[SOLVED] How can I make a stored procedure commit immediately?

How can I make a stored procedure commit immediately?

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

Solution

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?