Monday, March 26, 2012
Dynamic Query
my sp is used to eliminate duplicate data from the table
dynamic query inside the sp is
select @.sql = 'SELECT * into ' + @.i_errorDb + '.dbo.' + @.i_tableName + '
from '
+ @.i_oldDb + '..'+ @.i_tableName + ' as T where ' + @.i_PrimaryKey + '=
(select min('+@.i_PrimaryKey + ') from ' +
@.i_oldDb + '..'+ @.i_tableName + ' where ' + @.i_PrimaryKey + ' = T.' +
@.i_PrimaryKey + ' having count(*) > 1) order by ' + @.i_PrimaryKey
exec sp_executesql @.sql
it works fine if i have a single pk in the table. but in case of composite
pk, it fails.
pls help me to solve this
thanks
vanithait gives me the solution.
is there any alternative method?
select @.sql = 'delete FROM ' + @.i_oldDb + '..'+ @.i_tableName + ' WHERE ' +
@.i_PrimaryKey1 + '=(SELECT MIN('+
@.i_PrimaryKey1 + ') FROM ' + @.i_oldDb + '..'+ @.i_tableName + ' as T WHERE '
+ @.i_oldDb + '..'+ @.i_tableName + '.' + @.i_PrimaryKey1 + '= T.' +
@.i_PrimaryKey1
if @.i_PrimaryKey2 is not null
begin
select @.sql = @.sql + ' and ' + @.i_oldDb + '..'+ @.i_tableName + '.' +
@.i_PrimaryKey2 + ' = T.' + @.i_PrimaryKey2
end
if @.i_PrimaryKey3 is not null
begin
select @.sql = @.sql + ' and ' + @.i_oldDb + '..'+ @.i_tableName + '.' +
@.i_PrimaryKey3 + ' = T.' + @.i_PrimaryKey3
end
select @.sql = @.sql + ' having count(*) > 1)'
exec sp_executesql @.sql
"vanitha" wrote:
> hi friends,
> my sp is used to eliminate duplicate data from the table
> dynamic query inside the sp is
> select @.sql = 'SELECT * into ' + @.i_errorDb + '.dbo.' + @.i_tableName + '
> from '
> + @.i_oldDb + '..'+ @.i_tableName + ' as T where ' + @.i_PrimaryKey + '=
> (select min('+@.i_PrimaryKey + ') from ' +
> @.i_oldDb + '..'+ @.i_tableName + ' where ' + @.i_PrimaryKey + ' = T.' +
> @.i_PrimaryKey + ' having count(*) > 1) order by ' + @.i_PrimaryKey
> exec sp_executesql @.sql
> it works fine if i have a single pk in the table. but in case of composite
> pk, it fails.
> pls help me to solve this
> thanks
> vanitha
dynamic query
how far dynamic query built inside stored procedures is efficient than
normal queries inside stored procedures.
does dynamic query have advantage over ad hoc queries or not and in
what way.
kalai vananHi
http://www.sommarskog.se/dynamic_sql.html
"kalaivanan" <mail2kalai@.gmail.com> wrote in message
news:1164110912.592826.4610@.m73g2000cwd.googlegroups.com...
> hi,
> how far dynamic query built inside stored procedures is efficient than
> normal queries inside stored procedures.
> does dynamic query have advantage over ad hoc queries or not and in
> what way.
> kalai vanan
>
dynamic query
how far dynamic query built inside stored procedures is efficient than
normal queries inside stored procedures.
does dynamic query have advantage over ad hoc queries or not and in
what way.
kalai vanan
Hi
http://www.sommarskog.se/dynamic_sql.html
"kalaivanan" <mail2kalai@.gmail.com> wrote in message
news:1164110912.592826.4610@.m73g2000cwd.googlegrou ps.com...
> hi,
> how far dynamic query built inside stored procedures is efficient than
> normal queries inside stored procedures.
> does dynamic query have advantage over ad hoc queries or not and in
> what way.
> kalai vanan
>
sql
dynamic query
how far dynamic query built inside stored procedures is efficient than
normal queries inside stored procedures.
does dynamic query have advantage over ad hoc queries and in what way.
kalai vanankalaivanan wrote:
Quote:
Originally Posted by
how far dynamic query built inside stored procedures is efficient than
normal queries inside stored procedures.
The issue is caching query plans.
Quote:
Originally Posted by
does dynamic query have advantage over ad hoc queries and in what way.
They're more powerful - if you need it, and if you can afford the
disadvantages.
See http://www.sommarskog.se/dynamic_sql.html for more info.|||kalaivanan wrote:
Quote:
Originally Posted by
hi,
how far dynamic query built inside stored procedures is efficient than
normal queries inside stored procedures.
does dynamic query have advantage over ad hoc queries and in what way.
>
kalai vanan
If you pass object name as parameters, you have no choice other than
using dynamic sql
Make sure you read the suggested article fully
Madhivanan
dynamic query
how far dynamic query built inside stored procedures is efficient than
normal queries inside stored procedures.
does dynamic query have advantage over ad hoc queries or not and in
what way.
kalai vananHi
http://www.sommarskog.se/dynamic_sql.html
"kalaivanan" <mail2kalai@.gmail.com> wrote in message
news:1164110912.592826.4610@.m73g2000cwd.googlegroups.com...
> hi,
> how far dynamic query built inside stored procedures is efficient than
> normal queries inside stored procedures.
> does dynamic query have advantage over ad hoc queries or not and in
> what way.
> kalai vanan
>
Wednesday, March 21, 2012
Dynamic Login & User Creation
Hello. I'm trying to create a new login and username inside a trigger using variables.
This code works:
create login testUserName with password = 'testPassword'
The problem comes when I try to use variables for the username and password like this.
create login @.username with password = @.password
Does anybody know how to get around this problem?
BTW, the error message it gives is this, but I really doubt that semicolons have anything to do with it. If I literally type my data in the create login call it works fine, but when I use variables it doesn't.
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near '@.username'.
Msg 319, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
SQL Server doesn't allow you to specify variables in most DDL statements. So the only way is to form the CREATE LOGIN statement as a string and execute it using dynamic SQL. See the EXECUTE topic in Books Online for more details on how to execute SQL statements dynamically.|||
This doesn′t work unless you wrap it in dynamic SQL:
DECLARE @.SQLString VARCHAR(400)
SET @.SQLString = 'CREATE LOGIN ' + @.USERNAME + WITH PASSWORD ' + @.PASSWORD
EXEC(@.SQLSTRING)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
Thanks a lot! Just in case anybody else has this problem, here's some code that does what I wanted.
declare @.USERNAME varchar(50);
declare @.PASSWORD varchar(50);
set @.USERNAME = 'testUserName2';
set @.PASSWORD = 'testPassword';
DECLARE @.SQLString VARCHAR(400)
SET @.SQLString = 'CREATE LOGIN ' + @.USERNAME + ' WITH PASSWORD = ' + CHAR(39) + @.PASSWORD + CHAR(39)
EXEC(@.SQLSTRING)
|||You need to protect the dynamic SQL against SQL injection attacks. So you need to quote the login name which is an identifier otherwise potentially someone could provide a malicious login name which can be used to do attack the database. The password part is hard to protect since it is just a string. So you will have to validate it for certain characters in the front-end.
declare @.USERNAME varchar(50);
declare @.PASSWORD varchar(50);
set @.USERNAME = quotename('testUserName2'); -- Use quotename to form the identifier
set @.PASSWORD = 'testPassword';
DECLARE @.SQLString VARCHAR(400)
SET @.SQLString = 'CREATE LOGIN ' + @.USERNAME + ' WITH PASSWORD = ' + CHAR(39) + @.PASSWORD + CHAR(39)
EXEC(@.SQLSTRING)
|||Thanks for the tip. We're currently protecting against it on the front end but I recently heard there are steps I can take in the database for protection, so I'll have to look into that. Thanks.Friday, March 9, 2012
Dynamic EXEC error handling
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:
>
Dynamic EXEC error handling
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
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
>
|||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...
>
>
|||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:
[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 @.error
> = coalesce(nullif(@.rv, 0), @.@.error)'
> exec sp_executesql @.sql, N'@.error int output', @.error output
> print @.error
> go
>
> AMB
> "Bruce de Freitas" wrote:
Dynamic EXEC error handling
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
> >
> >