FK constraint when FK row is there! Always takes 10 mins to fail!

I am developing a SQLServer/JSP/Java (Jrun4) web app that intermittently has a FK constraint that always hoses up the transaction for exactly 10 minutes.

The user can save a record from a form that inserts a few rows in the db. The second insert is dependent on the FK of the first insert. All the inserts are done in an entity bean that calls stored procs.

We've had this intermittant error where the inserts take exactly 10 minutes (every time!), then it fails with a FK constraint, yet out logging shows that it's using an existing foreign key. (the stored procs returns the FK)

We have a dev server and a test server, and this has yet to occur on the dev server! I feel like there must be something set up incorrectly.

Any ideas?

thanks in advance!

-DanFlagged -

Are any of the tables really large? Enforcing a FK constraint without indexes on large tables could cause it to take a really long time. 10 minutes seems extreme, but...

And do you know what the differences are between your dev and test server? Do they have the same constraints? Do they have the same data?|||And the same indexes and statistics?|||There isn't much data: under 100 rows, 10 column.

The problem only happens intermittently. In fact it hasn't happed in the last several hundered inserts (we've been testing it)

Originally posted by strader
Flagged -

Are any of the tables really large? Enforcing a FK constraint without indexes on large tables could cause it to take a really long time. 10 minutes seems extreme, but...

And do you know what the differences are between your dev and test server? Do they have the same constraints? Do they have the same data?|||Both servers are set up the same. The more I think about it- based on the infrequency of the problem it may just be a coincidence that it only happed on the one server.

I'm investigating a possible answer to the problem- I noticed an unhandled exception in the bean and I think it's possible the 1st insert worked, then some unhandled exception occured and rolled back the transaction, then the bean continuted to attempt to insert the child row- just a theory since this is so infrequent.

Originally posted by Paul Young
And the same indexes and statistics?|||Here's another possibility - if you're running simultaneous transactions from different threads or processes against that table, and if your java code or your interface (jdbc?) is hanging, causing that transaction to sit there indefinitely, it could be causing blocking or deadlocks on that small table. If it puts an exclusive lock on the table, and you're holding that transaction open, then all the other waiting processes will fail or timeout.

It might be happening on only one server vs. the other because you're stressing one more than the other.

If it happens again, try using SET LOCK_TIMEOUT to change it to a shorter time for that connection. The more I think about it, the more likely it seems this is what's happening, since it's timing out after exactly ten minutes each time.|||I wanted to try one more thing before I tried your suggestion- and it looks like I fixed the problem, but I'm not 100% sure why the transaction was failing. Here's what the code was doing:

When the user saves form data, the entity bean inserts rows into the database, and in the same transaction (I think) proceeds to update those rows. My guess is that once in a while sqlserver locks those rows so that the records cannot be re-read. My fix was to prevent the extraneous update from occuring. It was happening because I didn't know enough about Java beans when I wrote the bean code. (I only used the store method in the bean for UPDATES and I handled inserts manually, but the store is called automatically for all bean calls so I ended up inserting, then updating).

Our beans are container managed, so that their transactions begin when the bean is called and end when the bean returns. I thought that one could manipulate an uncommited row inserted within a transaction but I guess that's not ALWAYS the case because 1 out of 100 times it times out and fails.

I'd say the problem's solved since it's been a week since my fix was in and no more problems!

thanks for the help.

Fixed number of Intervals

Hi friends,

We are using SQL Server 2005 Reporting Services for developing our Reports. In one of our Reports having chart can we show fixed number of Intervals on X axis or Y axis ?

-sandyee

I believe that you can. In the y axis and x axis properties it gives you a minimum value, maximum value, and interval property to set.

Firewall and port 1433

I read in a forum that port 1433 should never be opened to the internet. Is
this true?
We are developing Windows applications that will need access to our SQL
server from outside our firewall. Is it better to connect via a VPN first an
d
then run the Windows application?
Does anyone know how to open port 1433 on Netscreen 5gt? If it's ok to open
port 1433 to the internet.
We are using Netscreen 5GT as out main firewall and then we have Windows
fireall turned on."Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:44113285-9232-4CB1-96AD-116293A830CA@.microsoft.com...
> I read in a forum that port 1433 should never be opened to the internet.
Is
> this true?
Well, it depends. Do you want any random person knocking on that port and
waiting for the next Slammer type worm to come along and own your system?
If so, leave it open.

> We are developing Windows applications that will need access to our SQL
> server from outside our firewall. Is it better to connect via a VPN first
and
> then run the Windows application?
Yes.

> Does anyone know how to open port 1433 on Netscreen 5gt? If it's ok to
open
> port 1433 to the internet.
> We are using Netscreen 5GT as out main firewall and then we have Windows
> fireall turned on.
Also, if they are connecting from a specific range of IPs, narrow anything
you open to just that range for further protection.|||Hi
Have you considered creating a web based application? A half way house may
be to use a web service instead of direct database access.
John
"Curtis" wrote:

> I read in a forum that port 1433 should never be opened to the internet. I
s
> this true?
> We are developing Windows applications that will need access to our SQL
> server from outside our firewall. Is it better to connect via a VPN first
and
> then run the Windows application?
> Does anyone know how to open port 1433 on Netscreen 5gt? If it's ok to ope
n
> port 1433 to the internet.
> We are using Netscreen 5GT as out main firewall and then we have Windows
> fireall turned on.|||Thank you both for the sound advice.
We have considered web based application. I am not well versed in web
services, so I need to do some research.
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Have you considered creating a web based application? A half way house may
> be to use a web service instead of direct database access.
> John
> "Curtis" wrote:
>

Firewall and port 1433

I read in a forum that port 1433 should never be opened to the internet. Is
this true?
We are developing Windows applications that will need access to our SQL
server from outside our firewall. Is it better to connect via a VPN first and
then run the Windows application?
Does anyone know how to open port 1433 on Netscreen 5gt? If it's ok to open
port 1433 to the internet.
We are using Netscreen 5GT as out main firewall and then we have Windows
fireall turned on."Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:44113285-9232-4CB1-96AD-116293A830CA@.microsoft.com...
> I read in a forum that port 1433 should never be opened to the internet.
Is
> this true?
Well, it depends. Do you want any random person knocking on that port and
waiting for the next Slammer type worm to come along and own your system?
If so, leave it open.
> We are developing Windows applications that will need access to our SQL
> server from outside our firewall. Is it better to connect via a VPN first
and
> then run the Windows application?
Yes.
> Does anyone know how to open port 1433 on Netscreen 5gt? If it's ok to
open
> port 1433 to the internet.
> We are using Netscreen 5GT as out main firewall and then we have Windows
> fireall turned on.
Also, if they are connecting from a specific range of IPs, narrow anything
you open to just that range for further protection.|||Hi
Have you considered creating a web based application? A half way house may
be to use a web service instead of direct database access.
John
"Curtis" wrote:
> I read in a forum that port 1433 should never be opened to the internet. Is
> this true?
> We are developing Windows applications that will need access to our SQL
> server from outside our firewall. Is it better to connect via a VPN first and
> then run the Windows application?
> Does anyone know how to open port 1433 on Netscreen 5gt? If it's ok to open
> port 1433 to the internet.
> We are using Netscreen 5GT as out main firewall and then we have Windows
> fireall turned on.|||Thank you both for the sound advice.
We have considered web based application. I am not well versed in web
services, so I need to do some research.
"John Bell" wrote:
> Hi
> Have you considered creating a web based application? A half way house may
> be to use a web service instead of direct database access.
> John
> "Curtis" wrote:
> > I read in a forum that port 1433 should never be opened to the internet. Is
> > this true?
> >
> > We are developing Windows applications that will need access to our SQL
> > server from outside our firewall. Is it better to connect via a VPN first and
> > then run the Windows application?
> >
> > Does anyone know how to open port 1433 on Netscreen 5gt? If it's ok to open
> > port 1433 to the internet.
> >
> > We are using Netscreen 5GT as out main firewall and then we have Windows
> > fireall turned on.

Firefox

I'm currently developing a ASP.NET v2 application in VS 2005. Historically,
I've used Crystal Reports and I'm very familiar with that system. However,
this application needs to be hosted by an ISP on the internet so I'm now
constrained by the services offered by the hosting service.
When I've investigated Crystal hosting, the price jumps up significantly and
I was told by one hosting company that if I did find a Crystal hosting
service, they are probably using a dodgy licensing model. This project is
also cost constrained...
However, companies like www.discountasp.net offer SQL Server 2005 with
reporting services for just $15 a month which is great. I'm just watching
the webcasts from Microsoft and reporting services looks ideal and pretty
neat.
But I'm little concerned about cross-browser compatibility... does anyone
have any comments on this? Firefox is probably the only other browser that I
must support.
Thanks, Rob.
PS.On Jan 16, 5:51 pm, "Rob" <rob_nicholson@.nospam_unforgettable.com>
wrote:
> I'm currently developing a ASP.NET v2 application in VS 2005. Historically,
> I've used Crystal Reports and I'm very familiar with that system. However,
> this application needs to be hosted by an ISP on the internet so I'm now
> constrained by the services offered by the hosting service.
> When I've investigated Crystal hosting, the price jumps up significantly and
> I was told by one hosting company that if I did find a Crystal hosting
> service, they are probably using a dodgy licensing model. This project is
> also cost constrained...
> However, companies likewww.discountasp.netoffer SQL Server 2005 with
> reporting services for just $15 a month which is great. I'm just watching
> the webcasts from Microsoft and reporting services looks ideal and pretty
> neat.
> But I'm little concerned about cross-browser compatibility... does anyone
> have any comments on this? Firefox is probably the only other browser that I
> must support.
> Thanks, Rob.
> PS
Generally speaking, the compatibility is pretty good with Firefox in
comparison with IE6 and IE7. There are a few drawbacks however. One is
that when viewing reports directly in the Report Manager, IE shows the
entire report page in the initial view; whereas, Firefox has a
scrunched down version about one-third the normal height of the report
page, which requires vertical scrolling to view the entire report. The
search control that is naturally a part of the toolbar in the Report
Manager for IE, does not exist in Firefox (though the user can select
Ctrl+F to do a search on the current page in Firefox; but, not quite
the same thing). Also, drill through report functionality does not
work as well in Firefox as it does in IE. Otherwise, the main
differences between Crystal Reports and SSRS are that: there are
slightly different export options, they have a different production
licensing scheme and that Crystal Reports expression syntax is
different than that of SSRS, which uses VB.NET syntax. Also, CR has
been around longer and has a few minor functionality options that SSRS
does not. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant