Getting debugging information in SQL Server Management Studio

Question

I am creating a large script to do a bunch of inserts and updates. When I run it, I get some errors, but the error messages do not allow me to identify the problem - the line numbers since the last "GO", so I can not find the correct line.

I want to add calls to my script to a function in T-SQL that will simply be written to the results window, so I would better understand where the error occurs.

Answer 1

You can simply use PRINT in cases that you suspect might cause problems.

eg.

print 'Step 1' insert into tableA -- some code here ... print 'Step 2' etc 

You can also wrap your code inside the TRY CATCH statement and throw custom errors or print error messages if something goes wrong.

Answer 2

The PRINT operators suggested by @kristof will do what you want.

However, you can run SQL Profiler side by side while executing the script, catching all classes in the Errors and Warnings section and all SQL:StmtStarting events SQL:StmtStarting - this means that you do not have to edit your script.