Timeout or other timing-type problem???

  • Thread starter Thread starter fuzz_ball
  • Start date Start date
F

fuzz_ball

So I'm experiencing an odd problem. I establish a connection and
enlist in transaction and then save my dataset changes to the
database. My dataset contains 20+ tables that get updated, and I have
my code setup to handle inserts/modifies/deletes in the correct
order.

I'm experiencing a really odd bug, that I suspect is timeout-related
given how it occurs; but I do not get any message indicating a timeout
back from ADO.NET.

Basically, when I step through my save code that handles the inserts,
I have a table where I insert one record at a time (I need to do
something inbetween each insert to a certain table so calling Update()
won't work in my situation) I will typically get a sql exception
thrown that points to a foreign key violation, here is the error it
gives me:

The INSERT statement conflicted with the FOREIGN KEY constraint
"test_foreign_key". The conflict occurred in database "test", table
"dbo.Project", column 'project_id'.

The INSERT in question is to a child table (Location) that contains a
foreign key reference to dbo.Project.project_id.

Here is the part that has me scratching my head, and thinking that the
exception ADO.NET returns is lying to me: If I do NOT step through my
save code, then it completes successfully!

I'm running the same *exact* scenario every time (this includes the
key values that are generated and creating the same number of rows
with the same data). How do I ensure it is exact? Well I blow away my
database inbetween runs, I use the same script to pre-populate it, and
when generating this scenario, I create the same exact new records for
the various tables, and then I save.

Does anyone have any clue what I might be doing wrong, or what ADO.NET
is not telling me? Is it something to do with the configuration of my
DB (I'm using SQL 2005 btw)?

All I know for certain is that my problem, contrary to the exception
ADO.NET is throwing, is NOT foreign key related.

Here is how I'm establishing my connection and setting up my
transaction:

CommittableTransaction trans = new CommittableTransaction();
SqlConnection connection = new
SqlConnection(ResiDataSource.GetConnectionString());

connection.Open();
connection.EnlistTransaction(trans);
success = SaveToDatabse(dataSet, connection);
if ( success )
trans.Commit();
else
trans.Rollback();
 
Hi there,

In your place I would trace the sql statements issued to sql server, either
log them from code or even better, log them using sql tools.
 
I have tried tracing using SQL Server Profiler. When I step through my
code and this issue occurs, all that is different in the trace is that
output just stops. When successfull, I have a couple dozen entries
that appear in my trace, when this dies, it typically dies after the
4th or 5th (hence why I *suspect* timing) insert attempt.

All my trace is showing me is the 4 or 5 items that it executed
inserts for, and then tracing stops as at that point some kind of
error is returned to my program (and the error is always the one
aforementioned "The INSERT statement conflicted with the FOREIGN KEY
constraint...").

Is there any other tool/log I can look at to see where/why SQL (or
maybe it's ADO.NET) is returning a bogus "FOREIGN KEY constraint"
error?
 
I tried posting a response earlier, but it seems to have been lost-in-
space.

Anyhow, I did trace and what I find is that I get RPC completed events
after each INSERT completes, and then tracing output just ends. In a
successful situation (one in which I just let the code run without
setting break points) I get a couple dozen entries (one for each
INSERT). When I set a breakpoint, and step through the code, I get the
exception after the 4th or 5th (seems to depend on how much time I
take stepping through, the more I pause, the more likely it dies after
the 4th INSERT). Nothing in the Trace points to what may be causing
the problem. ADO.NET (erroneously) reports a FOREIGN KEY violation and
the whole process stops.

I've tried looking in the Event viewer hoping that maybe SQL Server
was stuffing some error message there, but alas I see nothing. Anyone
have any ideas? Are there any other logs I can turn on/watch that may
help me figure out what is going wrong?

Thanks!
 
Back
Top