Solved

Unable to execute Stored Procedure in Management studio but can execute in Visual studio Debug mode

Posted on 2009-07-07
7
645 Views
Last Modified: 2013-11-26
Hi,
  I just attached a stored procedure which I am trying to execute in management studio. I am using a cursor to update the table in sql server 2005.

exec EXEC [sp_SiteUpdate] @PaymentID = 123456.
I get the message 'Command(s) completed successfully.' though the table is not updated

But if I execute the same stored procedure in debug mode in visual studio, I am able to update the table and the message I get,

'(3 row(s) affected)
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[sp_SiteUpdate].
The thread 'db01 [98]' (0x13c4) has exited with code 0 (0x0).
The program '[1796] [SQL] db01: db01' has exited with code 0 (0x0).

Does anyone know what's wrong with the stored procedure?

Thanks,
HK

CREATE PROC [dbo].[sp_SiteUpdate](

@PaymentID nvarchar(50))
 

AS
 

--SET NOCOUNT ON
 

DECLARE  @LeanLoadID nvarchar(50)

DECLARE @InvoiceSite nvarchar(50)

DECLARE @GroupInfo nvarchar(50)

DECLARE @RowsAffected int
 

SET @RowsAffected = 0
 

DECLARE invoicesite_cursor CURSOR FOR 

SELECT leanloadID FROM PAYMENT LP,LOAD LL

WHERE LP.PAYMENTID = LL.PAYMENTID AND LP.PAYMENTID = @PaymentID

ORDER BY LL.LEANLOADID
 

OPEN invoicesite_cursor
 

WHILE @@FETCH_STATUS = 0

BEGIN
 

FETCH NEXT FROM invoicesite_cursor 

INTO @LeanLoadID
 

SELECT @InvoiceSite = S.LOCATIONREF,@GroupInfo = GROUPINFORMATION

FROM LEANWS_LOAD L

LEFT OUTER JOIN

(SELECT LOCATIONREF,LEANLOADID  FROM STOP WHERE LOCATIONREF IN('1','2','3') AND LEANLOADID = @LeanLoadID) AS S ON L.LEANLOADID = S.LEANLOADID

WHERE L.LEANLOADID = @LeanLoadID
 

SET @InvoiceSite = ISNULL(@InvoiceSite,0)
 

IF @InvoiceSite = '1'

   SET @InvoiceSite = 'TXHQ'

ELSE IF @InvoiceSite = '2'

   SET @InvoiceSite = 'KNOX'

ELSE IF @InvoiceSite = '3'

   SET @InvoiceSite = 'ARIZ'

ELSE

   SET @InvoiceSite = @GroupInfo 
 

UPDATE LOAD SET INVOICESITE = @InvoiceSite

WHERE LEANLOADID = @LeanLoadID

SET @RowsAffected = @@ROWCOUNT
 
 

END 

CLOSE invoicesite_cursor

DEALLOCATE invoicesite_cursor

Open in new window

0
Comment
Question by:computer12
  • 3
  • 3
7 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 200 total points
ID: 24795215
If you are running from SSMS, Have you committed the procedure after it is executed.
If you haven't committed, then it wont be reflected in that table.

Kindly check whether you have set your SET IMPLICIT_TRANSACTIONS set to ON accidentally.

Switch it OFF to autocommit statements.
0
 

Author Comment

by:computer12
ID: 24795323
I added SET IMPLICIT_TRANSACTIONS OFF to my stored procedure and it still had the same problem. Is this problem because of using cursor in stored procedure?
0
 

Author Comment

by:computer12
ID: 24795811
Any thoughts?
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 2

Assisted Solution

by:corptech
corptech earned 50 total points
ID: 24796762
Change your first test to:  EXEC sp_SiteUpdate '123456' and see if you get the correct results.  There isn't anything wrong with the stored procedure other than
the syntax of this call.  The second test is returning 0 exit code (successful code in SQL Server).
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 200 total points
ID: 24800890
>> I added SET IMPLICIT_TRANSACTIONS OFF to my stored procedure and it still had the same problem. Is this problem because of using cursor in stored procedure?

I mentioned not to include this one in your procedure but to execute that in SSMS query window.
Once set to OFF, execute your procedure and now it should work.

Hope this clarifies.
0
 

Author Comment

by:computer12
ID: 24814106
It did work the first time after I set it to OFF then executed the procedure.
1. But the next time, I did not set it to OFF and executed the procedure. This time the stored procedure didnot work.
2. I tried directly with out setting it to OFF to execute the procedure, but it didnot work.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24945205
Ok... Just try this one out in your Query Window

Hope this helps


SET IMPLICIT_TRANSACTIONS OFF;

GO
 

EXEC [sp_SiteUpdate] @PaymentID = 123456

Open in new window

0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Hangfire / asp.net sample 3 28
Create a form which is copy of a form in vb.net 2 17
Azure SQL DB? 3 15
Test a query 23 13
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now