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 the
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...
> > 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
> >
> >
>
>|||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, 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 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:
> 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, 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
> >
> >

Dynamic Columns & Dynamic Grouping ?

Hi,

We need to design more than 500 Reports for our ongoing project and we are dealing with MS SQL Server Reporting Services first time. We are currently confused in 2 things

1) How to manage Reports? I mean we should store entire report in database and load at runtime or simply store as a report file

2) Most Important is Dynamic Columns & Dynamic Grouping.

We need something like Microsoft Office Accounting 2007 Reports. Header part of form has some parameters for filter and right part has some parameters for showing or hiding and reordering columns and groups.

We are not getting clue for this dynamic stuff. Can any one post working sample or at least necessary basic code with some idea?

Reply on Urgent Basis

Thanks lot

Someone Please Help

If I have posted in wrong forum then suggest the correct one.