sp_execute and errors

Question

Post reply

sp_execute and errors

Adam Angelini

SSCrazy Eights

Points: 8548
More actions
February 15, 2006 at 10:41 am

#167108

if you are building dynamic sql in a stored procedure and you call sp_execute, but it errors out, is it possible to grab the error and continue execution of the original procedure - it just stops execution all together

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jo Pattyn SSC-Dedicated Points: 32348 More actions · Answer 1

Jo Pattyn

SSC-Dedicated

Points: 32348

February 15, 2006 at 10:58 am

#621126

what level of severity does the error have?

JeffB Hall of Fame Points: 3541 More actions · Answer 2

This is how I normally execute dynamic SQL and it does continue on an print out an error message.

declare @sql varchar(8000)

declare @ErrorNum int

select @sql = 'select * from foo'

exec(@SQL)

select @ErrorNum = @@ERROR

if @ErrorNum = 0 begin

print 'all ok'

end

else begin

print 'an error occurred'

end

Adam Angelini SSCrazy Eights Points: 8548 More actions · Answer 3

Well, this is what I am doing here, and it halts execution, and prints out error:

SET @sql = '

insert into master.dbo.db_size(svr_name,dbname,db_size,stat_time) SELECT ''' + @srvName + ''' AS SVR_Name,

''' + @dbname +''' AS DB_Name ,

cast(sum(size * 8 / 1024) as numeric(10,2)) AS DBSize, ''' + cast(@date as varchar(50)) + ''' Stat_Time FROM [' + @srvName + '].[' + @dbname + '].dbo.sysfiles'

EXECUTE (@sql)

SELECT @error = @@error

IF @error <> 0

BEGIN

PRINT 'Error ' + cast(@error as VARCHAR(50)) + ' occured on statement'

PRINT @sql

END

Server: Msg 7314, Level 16, State 1, Line 2

OLE DB provider 'SQL2' does not contain table '"attain"."dbo"."sysfiles"'. The table either does not exist or the current user does not have permissions on that table.

OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='SQL2', TableName='"attain"."dbo"."sysfiles"'].

Clearly it's a premissions error, but i'd like it to continue execution and collect the information for the databases it does have access to. Any help would be appreciated - Thanks

JeffB Hall of Fame Points: 3541 More actions · Answer 4

I don't know how to continue but since you are specifing a linked server for accessing each database, you could ensure the account used for the security context of the linked server is a 'db_datareader' for each database on each server that you are collecting the information.

Adam Angelini SSCrazy Eights Points: 8548 More actions · Answer 5

Well, that will get this thing going anyway, you would think there would be a way to trap the error and continue execution, but I guess my only option is try and make sure the error doesn't occur in the first place - Thanks All