Posted by Chris on 08/16/06 06:16

In SQL 2005 I have a stored procedure as below:

@sub_no smallint OUTPUT

BEGIN

BEGIN TRANSACTION

INSERT...INTO

SET @user_no = (SELECT ...... FROM ....WHERE sub_no = @sub_no)

INSERT...INTO

EXE another_stored_procedure (it includes also BEGIN...COMMIT)

EXE another_stored_procedure (it includes also BEGIN...COMMIT)

SET @sub_no = .......

COMMIT TRANSACTION

When Visual Studio (ASP.NET 2005) is open and I run the program,
procedure is executed once without any problem. If I publish the
project and put files on another server (or even use the published
files from my machine) I have an error because stored procedure is
executed twice. @sub_no is used as input/output parameter.

I followed/trace the steps in procedure and it seems that procedure is
executed once with correct value of @sub_no. The second time procedure
is executed, the value that it was assigned before COMMIT is used,
which gives an error because the INSERT values have NULL values.

In ASP.NET I call the store procedure once.

What could be the reason ?

Thanks a lot for any help.

[Back to original message]