By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,865 Members | 1,717 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,865 IT Pros & Developers. It's quick & easy.

Weird situation - Stored procedure executed twice

P: n/a
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.

Aug 16 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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.

Aug 16 '06 #2

P: n/a
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.

Aug 18 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.