ARRRGH! I wanna debug!

I've seen on a few searches (obviously not dBforums searches :( ) that sometimes other folks are running into this, but I haven't yet seen an answer to it anywhere...so, how 'bouts our brainy folks consider it for a brief moment?

I'd 'preciate it immensely...

Trouble is, I cannot get any of my stored procs to run in debug mode through the SQL Query Analyzer. I have done this on many occasions before, but am unable to debug ANY sp's today.

Interesting thing is, that I initially named all my stored procs as "sp_<procname>" but since I read that is a no-no (since sp_ procs are assumed to be system stored procs, and the master db will be searched first at execution time - slowing things down a tad), I today changed all my procs to <procname> simply getting rid of the sp_ on each proc name.

I did this by first renaming the proc in Enterprise manager, then going into the proc (also in enterprise manager) and changing the proc name in the CREATE PROC statement (and, of course, changed all the references to other stored procs fro sp_<procname> to <procname>). then saving the proc in Enterprise Manager.

After investigating the problem further, I tried deleting the proc in EManager, then went to SQL Server and recreating it (both by scripting the proc and running it in SQL Query Analyzer AND just deleting and re-adding (as "add new proc" in EManager.

No effect.

Still can't debug. When I go into the debugger, it allows me to set up my params as usual, but then when I go to execute, it doesn't stop even at the first line of the proc...it just commences to rip through the procs and runs to the end (runs successfully, I might add...but provides no way to view the progress!!!)

Any thoughts? I know it's gotta be setup or something...but just can't figger it out.I never use debug...from what I understand it's quirky, and I would imagine there all kinds of transaction types of problems...locking, ect..

AND did you look in BOL

There are some very specific things you have to do to set it up...

look up

"Troubleshooting the Transact-SQL Debugger"

In Search of BOL

And what do you need to debug?

The best debugging is here or SQLTeam anyway...|||well...so many questions, so few answers :)

The debugger WAS working a couple days ago...apparently the current failure is unrelated to the changes to procs I made yesterday, since I am unable to debug ANY procedures in ANY of the databases on the server. *shrug*

I looked in BOL (I always try to do that before dumping on y'all here ;) ) and didn't find much besides what you refer to. I'll obviously need to check all that to see what's up, but can't figure out why any of that would suddenly have changed...Guess I'll have to try to check logs so perhaps I can see if someone changed something.

I'm debugging my stored procedures...and as much knowledge as is present here, it can't replace a good line-by-line debugging session as far as giving detailed level visibility to what's going on in a proc. Of course there are exceptions, such as when I SEE what's going on, but can't UNDERSTAND it *LOL* but I've worked in both debugging and non-debugging environments (me-"what debugger do you use here", them'"ummm...'printf' is the one we use here" *rolling eyes*) It can, of course, be done...but if you learn and/or understand the quirky stuff that is present in EVERY debugger, then a line-debugger is just about the best type of x-ray glasses when working on a database.

And I'll arm-wrestle ya to the death (or, at least, to the "really sore arm") over this one.|||Can we start a poll?

Uses Debugger:1
Doesn't Use Debugger: 1|||Uh-oh, what have I stepped in? :D|||Update - tried to dump the SQL Server debugger in favor of the .NET Studio debugger, and that would be cool, 'cept now I'll have to learn a new debugger *scowl* Though I managed to step through a test proc, though cannot for the freakin' LIFE of me find the output pane in the Studio .NET debugger...whatever the "OUTPUT" window, is, it ain't where my proc's OUTPUT goes *scratching head*

Also, for better or worse, found Brett's post on SQLTeam and followed it and a few other search hits (wow, imagine if you will, a site with a search feature that WORKS!!! *LOL*) and discovered that I'm dealing with a decision by Uncle Bill that I cannot use my SQL Server debugger client as-is (http://support.microsoft.com/default.aspx?scid=kb;EN-US;328151 )

After runnint the LEGACY-ON thang, my debugger started working again. NOW I can go find all those pesky quirks that have been hiding from me so far ;)|||Is there some reason why you can't/won't apply sp3a to your client so you can debug?

-PatP|||Nope...just was stupid and thought this cute lil' SP3 disk that MSDN sent me was for decoration ;)

Just updated the client and everything is hunky dory again.

However, now that I realize I can develop on SQL server from Studio .NET, *sigh* I guess I'll have to start learning to use that anyway. (we're off to the .NET races later this summer anyway ;) )|||Yeah, they do have a nice esthetic effect, but I really prefer to use them to improve the state of my digital world instead of as mere decoration.

Actually, it takes a bit more to get the .NET debugger working properly (and much of that is tuning-to-preference work, so you're the only one that can do it), but it is WELL worth the effort!

-PatP