Solved

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

Posted on 2009-07-07
7
648 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

863 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

25 Experts available now in Live!

Get 1:1 Help Now