Weird situation - Stored procedure executed twice

[Chris]

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.

[Winista]

Can you post the code from your ASP.Net app where this SP is being called?
If the implementation is in a method then check if the same method is being
called multiple times for one request. For example you may be calling that
methos in Page_Load and then again in some event handler.
"Chris" <CL*****@gmail.comwrote in message
news:11**********************@b28g2000cwb.googlegr oups.com...

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.

[Chris]

Thanks Winista for your message.

The weird thing was that SP was not called again. And problem was
appearing only when I was using program on runtime (after pubishing it
to a website). From Visual Studio it was ok.

I published the website again and now it's ok. It seems the problem was
with "DLL". That's what I thought because I haven't changed the program
or SP.