sp_executesql Trap Error

Hi,

I am building SQL statements in a loop in SQL 2000 then using sp_executesql to execute each statement, if an individual statement fails I want to log the error and move on to the next one, but I can't trap the error.  At the moment if an individual statement fails the loop just stops.  

Note:  I am using SQL 2000 NOT 2005 or 2008

Any ideas?

thanks
LVL 2
kintonAsked:
Who is Participating?
 
lomo74Connect With a Mentor Commented:
first of all
set xact_abort off
otherwise transaction will stop whenever an error occurs
then you can lookup error in sysmessages
look at very trivial example

set xact_abort off

declare @i int, @err int

set @i = 3

while @i > 0 begin
	exec sp_executesql @statement = N'select 1/0 -- divide by zero error'
	--remember error
	select @err = @@error
	--log error if necessary
	if @err <> 0 begin
/*		insert into yourlogfile ([time], [description])    */
		select getdate(), [description] from sysmessages where error = @err
	end
	--next loop
	set @i = @i - 1
end

Open in new window

0
 
rajvjaCommented:
Why does it fails? print the dynamic sql statement and try to correct the error
0
 
EvilPostItCommented:
What error is it failing with?
0
 
kintonAuthor Commented:
IT's not the error that I am struggling with, I want to be able to catch errors should they appear in the future as this job will run every night.  At the moment I am deliberately making one statement in the loop fail so I can catch the error.
0
 
EvilPostItCommented:
Within SQL there there are 3 types of error Information/Warning/Error. SQL Server can only continue running if Information or Warning messages appear.

In SQL 2005 on the other hand you would be able to use a TRY / CATCH block for this.

With SQL 2000 the most you will be able to do is look at the job history for what error occured.
0
All Courses

From novice to tech pro — start learning today.