Executing sequential stored procedures; works in query analyzer, doesn't in my .NET application

Question

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):

            int openStatus, closeStatus = 0;
            openStatus = Convert.ToInt32(SPs.LogAccess(userId, "OPENED"));
            closeStatus = Convert.ToInt32(SPs.LogAccess(userId, "CLOSED"));

This is simplified, but this is what LogAccess calls:

 ALTER procedure [dbo].[LogAccess]
      @UserID           uniqueid,
      @Action           varchar(10),          
      @Status           integer output

 as

 DECLARE @mStatus INT

 EXEC [MyOtherDb].[dbo].[LogAccess]
      @UserID = @UserID,
      @Action = @Action,        
      @Status = @mStatus OUTPUT


 select @mStatus

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.

This works great if I run them independently of one another, or even if I paste them into query analyzer. However when they execute from 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.

Answer 1

In your 1st stored proc, try having the EXEC statement wait for a return value from the 2nd stored proc. My suspicion is that your first SP is firing off the 2nd stored proc and then immediately returning control to your .NET code, which is leading to the above commenter's concurrency issue. (That is to say, the 2nd SP hasn't finished running yet by the time your next DB call is made!)

SP1: EXEC @retval = SP2 ....