debug a SP
-
I am trying to become familiar with the debugger in SQL server 2k. I open QA, select the SP I want, click debug. THen:
enter parameter values
click execute
WHen I do this i am expecting to be able to step through code. Instead, it executes the entire SP and shows on the results pane:
@return_value = 0
I already know that. The SP is supposed to be adding records to tables. I am trying to find out why no records are being added.
Really appreciate the help!!
Sam
-
When you choose "Debug", you need to wait a while for the debugger to load the session. It takes about 10-20 seconds for some reason on my system. When it finishes loading it places the current line pointer at the top of the stored procedure. Hitting F5 runs until it either finishes or it reaches the end of the stored procedure. So, if you don't wait for it to show the pointer and hit F5 thinking you are simply starting the debugger, you are actually cueing the command to run the whole thing and by the time it finishes loading all you see is that it finished.
As for why the row wasn't there, the debug environment defaults to checking the "Auto Rollback". As soon as the procedure finishes or you stop debugging it any data it created or altered is rolled back. The dialog box you are given when you first choose "Debug" has the Auto Rollback option in it that you can uncheck if you desire. Alternatively you can depress the button for it to the right of the debug step buttons once debug mode has been begun and prior to it finishing. You should also be able to query the dirty data (uncommitted read) prior to the debug procedure finishing if you step through it little by little.
I learned that if you get impatient and start clicking debug buttons while the debugger is still initializing you can lock up the connection. When killing it left it in KILLED/ROLLBACK status indefinately I had to stop/restart SQL Server and related services (although I didn't try individually) to recover the connection and free the locks it held.
-
I've found that if parameters are not entered in a specific way, the debugger exhibits the behavior you describe. In particular, values for data type parameters need to be entered in a particular format, although which one eludes me at the moment. I think I've had the best luck using ODBC Canonical.
BTW, my understanding of the debugger is incomplete. If anyone knows a little more about this, please chime in!
-mt
-
I had the same problem when i started using SQL debugger. Here is how i fixed it:
Run Exec sp_sdidebug 'legacy_on' to ensure that the Debug is turned on for that SQl server, This needs to be run every time the server is restarted as the Debug option is turned off by Default. Also Check whether you have execute permission on sp_sdidebug. Both these should be checked before you use the SQL Debugger.
Also you might want to note that the TSQL Debugger does not accept a value for a datetime parameter when the datetime value has any nonzero seconds. The debugger accepts 2003-07-29 11:33:00 but will not accept 2003-07-29 11:33:01. When the latter is specified you get an overflow error. This is because the input window handles datetime incorrectly. (It handles datetime parameters as smalldatetime.)
Kavitha
-
I really appreciate the input. I will let you know how it goes.
-
So do I....
Thanks to this discussion I think I just solved a problem that has been plauging me all week.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply