Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 511
  • Last Modified:

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
0
kinton
Asked:
kinton
1 Solution
 
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
 
lomo74Commented:
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now