Dynamic EXEC error handling

When doing a basic EXEC statement, is there any straight-forward way of
dealing with error handling about the statement inside the exec' Meaning,
if you test @.@.error after the EXEC, all you get back is that the EXEC ran,
not that the code WITHIN the EXEC ran ok. I threw this example below, that
does an sp_grantdbaccess inside an EXEC. Saving the @.@.error from that, to a
#table works, but this seems a bit much to code for. Any other ideas to
shorten the code but be able to do error handling within the EXEC' THanks,
Bruce
set nocount on
drop table #err
create table #err (error_no int)
declare @.cmd varchar(255), @.errno int
select @.errno = 0
select @.cmd = 'declare @.errno_2 int exec @.errno_2 = sp_grantdbaccess ' +
char(39) + 'mroXXXir' + char(39)
+ ', ' + char(39) + 'mroXXXir' + char(39) + ' insert into #err
(error_no) select @.errno_2'
select @.cmd
exec (@.cmd)
select @.errno = @.@.error
select @.errno as 'Error Code from the EXEC'
select error_no as 'Error Code from the SP_GRANTDBACCESS' from #err
set nocount offIf you get back a retunrn code you could try:
DECLARE @.RC int
EXEC @.RC = (''Do someting with ou db')
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Bruce de Freitas" <Bruce de Freitas@.discussions.microsoft.com> schrieb im
Newsbeitrag news:74E237AB-3225-45C4-84A5-81EB264807ED@.microsoft.com...
> When doing a basic EXEC statement, is there any straight-forward way of
> dealing with error handling about the statement inside the exec'
> Meaning,
> if you test @.@.error after the EXEC, all you get back is that the EXEC ran,
> not that the code WITHIN the EXEC ran ok. I threw this example below,
> that
> does an sp_grantdbaccess inside an EXEC. Saving the @.@.error from that, to
> a
> #table works, but this seems a bit much to code for. Any other ideas to
> shorten the code but be able to do error handling within the EXEC'
> THanks,
> Bruce
> set nocount on
> drop table #err
> create table #err (error_no int)
> declare @.cmd varchar(255), @.errno int
> select @.errno = 0
> select @.cmd = 'declare @.errno_2 int exec @.errno_2 = sp_grantdbaccess ' +
> char(39) + 'mroXXXir' + char(39)
> + ', ' + char(39) + 'mroXXXir' + char(39) + ' insert into #err
> (error_no) select @.errno_2'
> select @.cmd
> exec (@.cmd)
> select @.errno = @.@.error
> select @.errno as 'Error Code from the EXEC'
> select error_no as 'Error Code from the SP_GRANTDBACCESS' from #err
> set nocount off
>|||Thanks Jens, I'm not seeing that you can ceck the @.RC that way when doing an
EXEC of SQL, only an EXEC of a proc. I tried it like this below, but get th
e
following error. Bruce
set nocount on
declare @.cmd varchar(255), @.errno int, @.errno_2 int
select @.errno = 0
select @.cmd = 'exec sp_grantdbaccess ' + char(39) + 'mroXXXir' + char(39)
+ ', ' + char(39) + 'mroXXXir' + char(39)
select @.cmd
exec @.errno_2 = (@.cmd)
select @.errno = @.@.error
select @.errno as 'Error Code from the EXEC'
select @.errno_2 as 'Error Code from the SP_GRANTDBACCESS'
set nocount off
Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near '('.
"Jens Sü?meyer" wrote:

> If you get back a retunrn code you could try:
> DECLARE @.RC int
> EXEC @.RC = (''Do someting with ou db')
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Bruce de Freitas" <Bruce de Freitas@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:74E237AB-3225-45C4-84A5-81EB264807ED@.microsoft.com...
>
>|||Use sp_executesql instead, because you can use output parameters.
Example:
use northwind
go
declare @.sql nvarchar(4000)
declare @.error int
set @.sql = N'declare @.rv int exec @.rv = sp_grantdbaccess ''pepe'' set @.error
= coalesce(nullif(@.rv, 0), @.@.error)'
exec sp_executesql @.sql, N'@.error int output', @.error output
print @.error
go
AMB
"Bruce de Freitas" wrote:

> When doing a basic EXEC statement, is there any straight-forward way of
> dealing with error handling about the statement inside the exec' Meaning
,
> if you test @.@.error after the EXEC, all you get back is that the EXEC ran,
> not that the code WITHIN the EXEC ran ok. I threw this example below, tha
t
> does an sp_grantdbaccess inside an EXEC. Saving the @.@.error from that, to
a
> #table works, but this seems a bit much to code for. Any other ideas to
> shorten the code but be able to do error handling within the EXEC' THank
s,
> Bruce
> set nocount on
> drop table #err
> create table #err (error_no int)
> declare @.cmd varchar(255), @.errno int
> select @.errno = 0
> select @.cmd = 'declare @.errno_2 int exec @.errno_2 = sp_grantdbaccess ' +
> char(39) + 'mroXXXir' + char(39)
> + ', ' + char(39) + 'mroXXXir' + char(39) + ' insert into #err
> (error_no) select @.errno_2'
> select @.cmd
> exec (@.cmd)
> select @.errno = @.@.error
> select @.errno as 'Error Code from the EXEC'
> select error_no as 'Error Code from the SP_GRANTDBACCESS' from #err
> set nocount off
>|||Thanks much Alejandro. Yes, that worked good. I didn't realize you could
use variables like that using the sp_executesql instead of the EXEC. Very
cool. Bruce
set nocount on
declare @.cmd nvarchar(4000), @.errno int
select @.errno = 0
select @.cmd = 'declare @.errno_2 int exec @.errno_2 = sp_grantdbaccess ' +
char(39) + 'mroXXXir' + char(39)
+ ', ' + char(39) + 'mroXXXir' + char(39) + ' set @.errno =
coalesce(nullif(@.errno_2, 0), @.@.error)'
select @.cmd
exec sp_executesql @.cmd, N'@.errno int output', @.errno output
select @.@.error as 'Error Code of the sp_executesql'
select @.errno as 'Error Code from the sp_grantdbaccess'
set nocount off
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Use sp_executesql instead, because you can use output parameters.
> Example:
> use northwind
> go
> declare @.sql nvarchar(4000)
> declare @.error int
> set @.sql = N'declare @.rv int exec @.rv = sp_grantdbaccess ''pepe'' set @.err
or
> = coalesce(nullif(@.rv, 0), @.@.error)'
> exec sp_executesql @.sql, N'@.error int output', @.error output
> print @.error
> go
>
> AMB
> "Bruce de Freitas" wrote:
>