January 12, 2007 at 7:44 am
Iām looking for some help debugging a fairly complex stored procedure.
How have you handled this in the past?
I am thinking about using PRINT statements to write out various pieces of information that I want to watch. I would like to write this information directly to a text file so that I can work with the output very easily.
Has anyone done this sort of thing before? Could you provide an example?
If not, what have you done in the past to debug your stored procedures?
Thanks for any insight.
R.
January 12, 2007 at 9:51 am
With Visual Studio 2005 you can step through stored procedure execution. I've not done this, but I know you can.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgAugust 25, 2008 at 3:54 am
Whoa! did you just say you can do some debugging in SQL? That would be great... I wish I knew how to do that...
Well, when I check my code for erros, I use either select or print
If there are no errors found but your database or sproc does something wrong like rollback the whole transaction without knowing what went wrong, Its good to use select.
Ex.
SELECT 'HERE'
this way, when you get back the resulting tables you'll see where the error occured.
when you get an error, this is the time when you need to use PRINT...
change the results from gridview to text view or whatever you call it and then you can track down where the error occured.
(By the way you can also use print like how yu use select when your results are not in gridview and are in text format)
man, its hard to explain but you can really track the error... although it takes time
another way is to use print or select to return the current values which you usually do in viewing values in debug mode
_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!
August 25, 2008 at 6:07 am
I would definitely advise using the inbuilt SQL Debugger in Visual Studio 2005. There is an article on MSDN which explains how to set it up (very straightforward). Using this you can step through code in the same way you would any other piece of code. It's invaluable for debugging. In my current project, I have a number of complicated stored procedures, with numerous loops / nested loops.
With this approach, I can step through it, and see exactly where either my loops aren't working, or I'm not getting back the value I'm expecting at a certain part of the code. Yes, you can do the same with PRINT or SELECT statements, but it's a lot more work, and you have to remember to take them out afterwards...
August 27, 2008 at 7:49 pm
Wow, thanks... uhmm... but I don't know how to use it LOL! Uhmmmmmmmmm... does it work for SQL Server 2005 EXPRESS?
_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!
August 28, 2008 at 9:16 am
Not having migrated from SS2K to SS2K5 yet,
The old way of doing things is to add the PRINT satetments (I prefer using SELECT).
Call your stored proc in the Query Analyzer. QA menu ("File", "Edit", "Query"...)
Use the "Query" and select the option "Results to file".
You could also create a table to store debug info, such as
[font="Courier New"]IF @@ERROR <> 0
INSERT INTO myDebugTable (ln) VALUES ('Step xxx failed')[/font]
You could use the line number of the stored proc but any change made will turn a specific line number into an approximate location.
To see if a particular step actually does affect at least one record, you could also do something like this:
[font="Courier New"]SET NOCOUNT OFF
IF @@ROWCOUNT < 1 BEGIN
INSERT INTO
END
SET NOCOUNT ON[/font]
A statement which never does not affect any record whatsoever might be the cause of the problem.
I AM REALLY NOT GIVING PROPER ADVICE HERE. INSTEAD OF CLINGING TO OUTDATED METHODS, YOU SHOULD REALLY STRIVE TO LEARN AND USE THE MORE POWERFUL FEATURES OFFERED BY 22K5....
Regards
August 28, 2008 at 12:37 pm
I am able to use Visual Studio IDE for debugging stored procs. You can debug your local SQL Server instance with out any efforts. But for others try this. http://aspnet.4guysfromrolla.com/articles/051607-1.aspx
August 28, 2008 at 1:23 pm
Never discount a "Newbie". They can teach making faces to old monkeys (OK, sounds strange, but this is a translation from French).
venkatesulu pointed me to an article on debugging that itself points to an article on debugging stored procs using SQL Server 2000 debugger. (I had always gotten along without it, never wondered if there was a better way of doing this ... Just like visiting Niagara Falls: tourists from other countries make it a point to see it but since for me it is only 600 km away, there is no sense of urgency to see it, I can go anytime I wish).
(I am still on 2000, now hesitating between migrating first to SSA2K5 then to SS2K8 or just migrate to SS2K8 directly.)
Thanks venkatesulu
August 28, 2008 at 2:26 pm
venkatesulu
Like J thanks for that link - wondered how to debug SQL 2005 and that link leading to other was just what the doctor ordered
August 28, 2008 at 3:18 pm
While being able to step through the code in Visual Studio is very helpful, don't discount using print and select statements as well. I will often have a parameter for my SP that is used for a debugging level. This is defaulted to not print out the debug statements. As I develop the SP I will rely almost totally on the use the of the Print/Select statements. Once I run into a section that is really causing problems I will move onto a combination of stepping through and the select statements. If it is still causing problems and I have a temp table in the SP, I will change that to a fixed table so that I can put a break point on the SP, run it in the debugger to the break point, and then switch over to a query window and run select statements against the data while using WITH(NOLOCK) on the tables so I can see the dirty data. This is often the ONLY way you will find out what is actually happening to the data as you work on the SP. Especially when you have datasets as large as I have to deal with. š
Gary Johnson
Sr Database Engineer
August 28, 2008 at 3:58 pm
Mr. Johnson,
Thanks for your remark about PRINT/SELECT statements still being relevant in some cases.
Regards
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply