• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 548
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

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