How can I make a stored procedure commit immediately?

Question

EDIT This questions is no longer valid as the issue was something else. Please see my explanation below in my answer.

I'm not sure of the etiquette so i'l leave this question in its' current state

I have a stored procedure that writes some data to a table.

I'm using Microsoft Practices Enterprise library for making my stored procedure call. I invoke the stored procedure using a call to ExecuteNonQuery.

After ExecuteNonQuery returns i invoke a 3rd party library. It calls back to me on a separate thread in about 100 ms.

I then invoke another stored procedure to pull the data I had just written. In about 99% of cases the data is returned. Once in a while it returns no rows( ie it can't find the data). If I put a conditional break point to detect this condition in the debugger and manually rerun the stored procedure it always returns my data.

This makes me believe the writing stored procedure is working just not committing when its called.

I'm fairly novice when it comes to sql, so its entirely possible that I'm doing something wrong. I would have thought that the writing stored procedure would block until its contents were committed to the db.

Writing Stored Procedure

 ALTER PROCEDURE [dbo].[spWrite] 
    @guid varchar(50),
        @data varchar(50)
    AS
    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

-- see if this guid has already been added to the table
DECLARE @foundGuid varchar(50);
SELECT @foundGuid = [guid] from [dbo].[Details] where [guid] = @guid; 
    IF @foundGuid IS NULL
    -- first time we've seen this guid
    INSERT INTO [dbo].[Details] ( [guid], data ) VALUES (@guid, @data)
ELSE
    -- updaeting or verifying order
    UPDATE [dbo].[Details]  SET data =@data WHERE [guid] = @guid
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Reading Stored Procedure

ALTER PROCEDURE [dbo].[spRead] 
@guid varchar(50)   
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

SELECT * from [dbo].[Details] where [guid] = @guid; 
END 

Answer 1

To actually block other transactions and manually commit, maybe adding

BEGIN TRANSACTION
--place your
--transactions you wish to do here

--if everything was okay
COMMIT TRANSACTION
--or
--ROLLBACK TRANSACTION if something went wrong

could help you?

Answer 2

I’m not familiar with the data access tools you mention, but from your description I would guess that either the process does not wait for the stored procedure to complete execution before proceeding to the next steps, or ye olde “something else” is messing with the data in between your write and read calls.

One way to tell what’s going on is to use SQL Profiler. Fire it up, monitor all possible query execution events on the database (including stored procedure and stored procedures line start/stop events), watch the Text and Started/Ended columns, correlate this with the times you are seeing while tracing the application, and that should help you figure out what’s going on there. (SQL Profiler can be complex to use, but there are many sources on the web that explain it, and it is well worth learning how to use it.)