how to debug a long script in management studio?

Question

I have a very long T-sql script, it is a single batch, something like

DECLARE variables

select into temptable...

cursor...

insert...

update..

as I run the script I have

(1 row(s) affected)

(5 row(s) affected)

[...]

ERROR

(2 row(s) affected)

etc...

I would like to run step by step the script to identify which is the command that gives error, is it possible in Management Studio without using Visual Studio?

Which other trick can you suggest me? I cannot run a block of code at a time because int hat case I will lose temp variables.

Note: Of course it is better not to write scripts in this way, but I found this and I would like to fix the problem.

Answer 1

Try to use SQL Server Profiler will help you for this issue.

Answer 2

In Sql Server Management Studio 2008 you can debug in a very similar manner to Visual Studio. Just set a break point on the line you want to debug and then click the debug button (alt-F5). This will start debugging from the first line. click the debug (play) button again and it will run to the line of code where you set your break point. Hope this helps.

Answer 3

Which version of SQL server are you running?

Depending on the severity of the error, another nifty trick you could try to catch the errors would be to wrap everything that is suspect in TRY / CATCH blocks.

Firstly create a table like so...

CREATE TABLE [dbo].[ERROR_LOGGING](
            [ErrorNumber] [int] NULL,
            [ErrorSeverity] [int] NULL,
            [ErrorState] [int] NULL,
            [ErrorProcedure] [nvarchar](128) NULL,
            [ErrorLine] [int] NULL,
            [ErrorMessage] [nvarchar](4000) NULL,
            [UpdateStamp] [datetime] NULL DEFAULT ( getdate() )
)  ON [PRIMARY]

Then put your code in a try / catch block.

BEGIN TRY

--Code---

END TRY


BEGIN CATCH

INSERT INTO ERROR_LOGGING (
                                            errornumber, 
                                            errorseverity, 
                                            errorstate, 
                                            errorprocedure, 
                                            errorline, 
                                            errormessage,
                                            updatestamp)
                            VALUES ( 
                                            ERROR_NUMBER(), 
                                            ERROR_SEVERITY(), 
                                            ERROR_STATE(), 
                                            ERROR_PROCEDURE(), 
                                            ERROR_LINE(), 
                                            ERROR_MESSAGE(),
                                            getdate()) 

END CATCH;

This will catch any errors above a severity level of 16 and insert them into the table created in the first step.

Caveat of this is that try / catch blocks do not catch low level errors.

I've seen it a few times that data in a source or destination table is different to what is expected, i.e. strange characters, text instead of integers etc. If you still can't crack it run a sql server profiler trace against the databse while you run the procedure.

It shouldn't take long to find the problem. I won't go into how to use SQL server profiler as you only need to do a quick web search to find hundreds of detailed guides.