Executing sequential stored procedures; works in query analyzer, doesn't in my .NET application
- by evanmortland
Hello,
I have an audit record table that I am writing to. I am connecting to MyDb, which has a stored procedure called 'CreateAudit', which is a passthrough stored procedure to another database on the same machine called MyOther DB with a stored procedure called 'CreatedAudit' as well.
In other words in MyDB I have CreateAudit, which does the following EXEC dbo.MyOtherDB.CreateAudit.
I call the MyDb CreateAudit stored procedure from my application, using subsonic as the DAL. The first time I call it, I call it with the following (pseudocode):
Result = CreateAudit(recordId, "Opened")
One line after that, I call:
Result2 = CreateAudit(recordId, "Closed")
In my second stored procedure it is supposed to mark the record that was created by the CreateAudit(recordId, "Opened") with a status of closed.
It works great if I run them independently of one another, but when they run in sequence in the application, the record is not marked as "Closed".
When I run SQL profiler I see that both queries ran, and if I copy the queries out and run them from query analyzer the record gets marked as closed 100% of the time!
When I run it from the application, about once every 20 times or so, the record is successfully marked closed - the other 19 times nothing happens, but I do not get an error!
Is it possible for the .NET app to skip over the ouput from the first stored procedure and start executing the second stored procedure before the record in the first is created?
When I add a "WAITFOR DELAY '00:00:00:003'" to the top of my stored procedure, the record is also closed 100% of the time.
My head is spinning, any ideas why this is happening!
Thanks for any responses, very interested in hearing how this can happen.
