Getting debug output in SQL Server Managment Studio

Question

I'm generating a large script to do a bunch of inserts and updates. When I run it I get some errors, but the error messages don't let me pinpoint the problem - the line numbers are since the last "GO", so I can't find the right line.

I'd like to add calls to my script to a function in T-SQL that will just write to the results window, so I'd have a better idea where the error occurs.

Answer 1

You can simply use PRINT in the pleces that you suspect can cause problems

e.g.

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

You can also wrap your code into block of TRY CATCH statements and throw custom errors or print error messages if something goes wrong

Answer 2

PRINT statements as suggested by @kristof would do what you want.

However you could run SQL Profiler side-by-side when you execute the script, catching all classes in the Errors and Warnings section and all SQL:StmtStarting events -- this would mean you wouldn't have to edit your script.