How To Debug a lengthy Stored Proc in Sql Server 2000 ,that contains 5000 lines of code
-
anyway i don't want to use a error log , and track that log file, would like to know any other alternative way to debug a stored proc. make it more clear, can we debug that through the debugger window, through query analyzer. i found one debug option while right clicking the stored proc through the object browser , the dubug window comes but all the option like step into,step over are disabled may i know this reason and how to active the debugger.
-
This is a little open ended...
I would advise you to get a some Work Load data by running the profiler while the procedure is execuing and using that in the Database tuing wizard to get suggestions.
Before that though, I would look at the execution plans, and look for the slowest executing queries, Table scans etc. Also look at the actualy queries in the Sp and see if there are not alternative ways to write Subqueries and recursive function calls etc....
If you know what the SP is supposed to do maybe look at the actually logic employed and see if that can be improved on.......
-
Anoop
try running
GRANT EXECUTE ON [dbo].[sp_sdidebug] TO [public]
GO
in your master db.
HTH
Dave J
-
Thanks for your opinion, but I am not satisfied with the answer. see i wish use sql debugger, and would like to know is these a common way of debugging a lengthy stored Proc
-
And is there any other steps..
-
Anoop, from BOL
Starting the Debugger
T-SQL Debugger can only be run from within SQL Query Analyzer. Once started, the debugging interface occupies a window within that application.
Starting the debugger
There are two methods for starting the debugger. Each method requires that the target procedure be created and stored in a SQL Server database. The first method employs Object Browser. The second method relies on the Object Search tool.
To start the debugger from Object Browser
On the Tools menu, click Object Browser.
In Object Browser, click the Objects tab.
Double-click to open the database in which your procedure is stored.
Double-click to open the Stored Procedures folder.
Right-click the stored procedure to debug. A Context menu will appear.
Select Debug from the Context menu.
To start the debugger from Object Search
On the Tools menu, click Object Search.
In the Object Search window, enter the appropriate search parameters required to locate the procedure you are looking for. Click the Find Now button to display matches.
In the results pane of the Object Search window, right-click the name of the stored procedure you wish to debug. A Context Menu appears.
Select Debug from the Context Menu.
When the Transact-SQL Debugger starts, a dialog box appears, prompting you to set the values of input parameter variables. It is not mandatory that these values be set at this time. You will have the opportunity to make modifications once the debugger's interface appears. In the dialog box, click Execute to continue with your session.
Note SQL Query Analyzer does not support multiple instances of the debugger. Attempting to debug a second stored procedure will prompt you to cancel the currently active debugging session.
Due to connection constraints, it is not possible to create a new query while the debugger window is in the foreground. To create a new query, either bring an existing query window to the foreground or open a new connection to the database.
HTH
Dave J
-
I can never start the darn thing (lol) cause the Stored Procedure I want to debug doesn't have any parameters to pass in...
I couldn't find anything to tell me how to do it without Parameters being passed in...
-
john.steinbeck (11/3/2008)
I can never start the darn thing (lol) cause the Stored Procedure I want to debug doesn't have any parameters to pass in...I couldn't find anything to tell me how to do it without Parameters being passed in...
That's not what I see here. I created this
CREATE PROCEDURE dbo.myTestProc
AS
SELECT 1
RETURN 0
And it goes into the debugger fine. The initial window does pop up, but I just press Execute and it steps through as expected?
Dave J
-
Cool, Let me take my foot out of my mouth...lol.
I assume I could put variables in the debugger that are in my Stored Procedure and view their values?
John
-
There is no way to debug an SP with any kind of debugging tools.
Unless it's purely procedural code without any attempt to access data in database.
But in this case, I believe, there is no point to have this procedure.
In all other cases SP is dependent on DML statements (which are not part of SP) and actual data in tables.
There are heaps of examples (on this forum as well) when "procedure was working for 6 months (2 years, etc.) and suddenly started to fail today.
Stored procedures must e debugged on abstract level, with verifying of data definitions, keys, constraints, etc. Starting from CREATE TABLE statements.
And no debugging tool can provide you with such service.
_____________
Code for TallyGenerator -
Hello SqlServer's,
When i try to debug the stored proc, that time i am not able to interact with debugging window. situation is this, all the (step into,step over...etc) which state is enabled but when i try for stepinto action that window become not responding ,if i already supply some value as argument fine it will produce the result but i am not able to track the runtime value in each variable holds .
-Anoop
-
Wouldn't it be easier just to print the values you want to see during SP execution?
_____________
Code for TallyGenerator -
Don't want to print to see , just want to see all in the result pan
-
Then use SELECT instead of PRINT.
But it's more resource consuming.
_____________
Code for TallyGenerator -
The old-fashioned way of doing this.
Obviously, 5,000 lines of cocde mean that there a lot of consecutive, distinct steps in the processing.
Start with the first block, enter it in something like
[font="Courier New"]--
IF @@ERROR <> 0 SET @li_Return_Code = -100 ELSE SET @li_Return_Code = 0
IF @li_Return_Code <> 0 RETURN @li_Return_Code
-- OR --
IF @li_Return_Code <> 0 GOTO Finir
-- OR --
IF @li_Return_Code = 0 BEGIN
.
. IF @@ERROR <> 0 SET @li_Return_Code = -101
END[/font]
And when exsecuting the stored procedure in the Query Analyzer do the following
[font="Courier New"]DECLARE @r int
EXECUTE @r =
SELECT 'Results = ', @r[/font]
You could start at the middle of your stored procedure and check if it failed in the first or second half. Then when you know which half, insert a new statement in the middle of this offending half. And so on.
The code lines [font="Comic Sans MS"]IF @@ERROR <> 0 SET @li_Return_Code = -101 [/font]do not have to be removed when you are done.
In fact, when I create any procedure, even a short one, I make it a point to always return a status code. And if a step fails, there should really not be a point in keeping plodding on until the end, right ?
By checking for error at every step, you also get a first indication of where things went wrong.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply