Posted by Winista on 08/16/06 16:29

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" <CLarkou@gmail.com> wrote in message
news:1155708968.441792.172330@b28g2000cwb.googlegroups.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.
>

[Back to original message]