Debugging of SQL Procedures in SQL SERVER 2000 & 2005

Question

Debugging of SQL Procedures in SQL SERVER 2000 & 2005

John-1015474

SSC Veteran

Points: 247
More actions
March 11, 2009 at 10:35 am

#97309

Hi Friends
I always done debugging in sql server 2000 and 2005 using the print command by printing the values of the variables.
Is there any other way to debug step by step a large sql procedure/Function in SQL Server 2000,2005 without using any external tools?
And if we have to use the external tools for it then call u please tell me the free tools available for debugging the Sql procedures
Nothing Is Impossible

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

If I'm not mistaken, you can use Visual Studio to debug procs. I haven't done so, but I've heard it can be done. That might count as an external tool.

Otherwise, it's all variations on printing values. You can select values, you can use raiserror to get messages out of the script, you can insert values into a Debug table.

Of course, you can also use Try...Catch in SQL 2005, and there are things you can do with that to debug.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Steve Jones - SSC Editor SSC Guru Points: 734444 More actions · Answer 2

SQL Server 2000 has a debugger in Query Analyzer.

SQL Server 2005 has some debugging in Visual Studio, but nothing built in.

John-1015474 SSC Veteran Points: 247 More actions · Answer 3

Thanks friend

Can u please tell me how to use them in debugging the sql procedure

Thanks again

Vivek

Nothing Is Impossible

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 4

Hi

Try this link:

http://msdn.microsoft.com/en-us/library/s0fk6z6e(VS.80).aspx

Hope this helps!

Flo

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 5

hi

Check out the below link(which does the debug using VS)

http://support.microsoft.com/default.aspx/kb/316549

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

YSLGuru SSC-Insane Points: 21632 More actions · Answer 6

ANyone know why the "Start Debugging" menu item would NOT be enabled? I would like to debug an SP but all of the Debug items like Break Point insertion and Run To are all dimmed out. I read the recomended above link to Microsoft and as far as I can tell I've not NOT done some key step but I feel like there is some requirement or setup I;'m missing.

One thing I did not do and that's b/c the article implies this is not necessary for T-SQL code and that is to install the Remote Debugger tool/app. Is that still a requirement even when debugging T-SQL SP's only?

Thankks

Kindest Regards,

Just say No to Facebook!

YSLGuru SSC-Insane Points: 21632 More actions · Answer 7

I've managed to get this to work by installing the full blown Visual STudio 2008 Professional Edition.

Kindest Regards,

Just say No to Facebook!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

As a guess, you previously only had the Standard Edition loaded? Yes, you need the Profession Edition to debug stored procedures. I thought, however, they had brought back the ability to debug SQL Server 2008 stored procedures in SSMS for SQL Server 2008?

YSLGuru SSC-Insane Points: 21632 More actions · Answer 9

Lynn Pettis (8/3/2009)
As a guess, you previously only had the Standard Edition loaded? Yes, you need the Profession Edition to debug stored procedures. I thought, however, they had brought back the ability to debug SQL Server 2008 stored procedures in SSMS for SQL Server 2008?

They have brought this back in VS2008 and it's great except that it requires that the DB Server be version 2008 as well. You can't hook up SSMS 2008 to a 2005 SQL Server box and debug an SP on it 🙁

Our main App and it's DB barely supports SQL 2005 and even then only when the DB compatablity is set to 8.0 so it's gonna be a while before we are using the server side of 2008 that much. We have a few smaller Apps & DB's that run on the more recent versions of SQL Server but the bulk of what we work with is still set to Compatability Mode of 80 on SQL 2005.

This ordeal has been extremly frustrating as I have tried bascially every suggestion I can find and eprformed all the requirments like enabled/running the Remote Debugger service and nothing works including whne I am at the actual SQL Server box except for using the full blown Professional Version of Visual Studio.

I have to say that after everything I am very disappointed in Microsoft for making this move when introducing SQL Server 2005. It looks to me like this was clearly a question of finding a way to indirectly push their DB Clients into purchasing Visual Studio licenses by taking away from SQL Server a feature that is highly sought after feature and putting it into something you have to pay a seperate fee for. I guess dropping the Search Tool just wasn't enough.

Thanks for following up.

BTW - Do you know if Red-Gate has any Debbugging abilities? We have licenses for it and use it all the time.

Kindest Regards,

Just say No to Facebook!