One for the geeks out there ...
What's the best way to debug stored procedures in SQL Server 2005? I heard that it can be done in Visual Studio 2005, but I haven't quite been able to get it to work. I'm not sure if that's because I've got it set up the wrong way, but I couldn't insert any breakpoints or access any sort of Step Into function, which made it difficult.
What do other people use?
I was never able to get that to work either. I use print statements and select statements on variables to debug.
Marty
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Matthew Hodge Sent: Thursday, March 04, 2010 4:07 PM To: Martin A. Jones Subject: [Tessitura Technical Forum] Debugging Stored Procedures
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
Matt,
I’ve always used SQL Server Management Studio to debug. I reverse engineer the stored procedure and replace variables where needed. If there is an error it will show you on the messages tab in red when you run it. What problem are you having?
Naomi
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Matthew Hodge Sent: Thursday, March 04, 2010 4:07 PM To: Naomi Williams Subject: [Tessitura Technical Forum] Debugging Stored Procedures
Hi Matthew,
You can debug sp in SQL 2000 and SQL 2008, but not in SQL 2005.
----code need to be checked:declare @myCustomer_no intset @myCustomer_no=12345select * from t_customer where customer_no=@myCustomer_no
return ---if you want to stop the sp in the middle of sp.
---debug codeprint'@myCustomer_no is 'print @myCustomer_noselect * into a# t_customer from t_customer where customer_no=@myCustomer_no
---check out ---check the Messages &select * from a# t_customer
I attached impresario in SQL 2008 STD. but I never feel I have to use it debug sp. So I just leave it there.
have fun
Ben
Thanks guys. I'll keep the print command as a good cheat.
In the end, it turned out that the problem I was having was that the procedure in question hadn't been run since late 2009, but for a variety of reasons, I thought it had something wrong with it. However, it turns out that there was nothing wrong with it whatsoever and I can now move on to something else...
Nice photo change, by the way, Naomi.
SSMS for SQL Server 2008 has a very nice toolset for debugging that operates very similar to VS debugging in other languages, as long as the database being used is also SQL 2008.
-Ryan
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Matthew Hodge Sent: Thursday, March 04, 2010 10:42 PM To: Ryan Creps Subject: Re: [Tessitura Technical Forum] Debugging Stored Procedures
From: Ben Gu <bounce-bengu4278@tessituranetwork.com> Sent: 3/4/2010 9:22:53 PM
----code need to be checked: declare @myCustomer_no int set @myCustomer_no=12345 select * from t_customer where customer_no=@myCustomer_no
---debug code print'@myCustomer_no is ' print @myCustomer_no select * into a# t_customer from t_customer where customer_no=@myCustomer_no
---check out ---check the Messages & select * from a# t_customer
I couldn't get this work either in Visual Studio and I believe you need a special edition of VS to get this to work..
http://msdn.microsoft.com/en-us/library/s4sszxst%28VS.80%29.aspx
I used to debug SPs with DBArtisan but that's been years ago, but it might be an option.
John