Sql server string executing

Question

Post reply

Sql server string executing

humbar-a

SSC Eights!

Points: 970
More actions
March 22, 2010 at 11:31 am

#220184

Hi i have a syntax problem when i try to make this quey :
DECLARE @STRING VARCHAR(500)
declare @db varchar(100)
DECLARE @Flag INT
SET @Flag = 0
WHILE @Flag<2
BEGIN
select @db=name from sysdatabases where name like '%outbound%'
SET @string ='use '+@db+' DISABLE TRIGGER TRIGGeR_NAME on'+ @db
EXEC (@string)
set @flag=@flag+1
end
can anyone help me ?
thanks in advance.

Viewing 10 posts - 1 through 9 (of 9 total)

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

What error are you getting?

Just off the top of my head, you need to change your sysdatabases to sys.databases.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

humbar-a SSC Eights! Points: 970 More actions · Answer 2

Actually sysdatabeses does not cause a problem.

i get the error from this part as a syntax error :

SET @string ='use '+@db+' DISABLE TRIGGER tr_ListeGroups_in_Archivage_ListeGroups on'+ @db

Msg 102, Niveau 15, État 1, Ligne 1

Syntaxe incorrecte vers 'DISABLE'.

Msg 102, Niveau 15, État 1, Ligne 1

Syntaxe incorrecte vers 'DISABLE'.

thanks in advance.

vidya_pande SSCertifiable Points: 5275 More actions · Answer 3

GO can not be used in dynamic string statement.

Disable syntax is correct-

use something like - execute two statements differetnly

SET @string ='use '+'testdb'

exec (@string)

SET @string =' DISABLE TRIGGER TRIGGeR_NAME on'+ ' testdb'

exec (@string)

humbar-a SSC Eights! Points: 970 More actions · Answer 4

Thanks you two. It is better now but i still get an error which is different.

DECLARE @STRING1 VARCHAR(500)

DECLARE @STRING2 VARCHAR(500)

declare @db varchar(100)

DECLARE @Flag INT

SET @Flag = 0

WHILE @Flag<2

BEGIN

select @db=name from sysdatabases where name like '%outbound%' (Here Databasename is "xx_outbound")

SET @string1 ='use '+@db

SET @string2 ='DISABLE TRIGGER Trigger_Name on [SERVERNAME].'+@db

exec (@string1)

exec (@string2)

set @flag=@flag+1

end

ERROR :

The object [ServerName].xx_Outbound can't be found because it doesn't exists or you do not have authorization.

(Msg 1088, Niveau 16, État 21, Ligne 1

L'objet "[ServerName].xx_Outbound" est introuvable, car il n'existe pas ou vous ne disposez pas des autorisations requises.)

Actually it finds the object(xx_Outbound ) and when i make this query it works fine :

SET @string1 ='use '+@db

exec (@string1)

But for the second string i don't know what's wrong?

thanks.

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 5

When your first dynamic SQL finishes its run, the session returns to work with the original database that it was set to work with before the dynamic SQL ran. You need to use only one dynamic SQL that starts with the USE databaseName and then runs your script. Here is a small demo that shows it:

use tempdb

declare @st varchar(100)

--Should show master db

set @st = 'use master; select db_name()'

exec (@st)

--Should show tempdb again

set @st = 'select db_name()'

exec (@st)

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Stefan_G SSCertifiable Points: 6609 More actions · Answer 6

nailosuper (3/22/2010)
SET @string ='use '+@db+' DISABLE TRIGGER TRIGGeR_NAME on'+ @db

You are missing a space after on.

It should be

SET @string ='use '+@db+' DISABLE TRIGGER TRIGGeR_NAME on '+ @db

Paul White SSC Guru Points: 150468 More actions · Answer 7

It looks as if the intention here is to disable a database trigger.

Aside from the other syntactical errors, the syntax is 'DISABLE TRIGGER {name} ON DATABASE'.

You use the word 'DATABASE' there, not the actual name of the database.

I believe the following fixes all the errors:

SET @string =

'USE ' + QUOTENAME(@db) + ';' + SPACE(1) +

'DISABLE TRIGGER tr_ListeGroups_in_Archivage_ListeGroups' + SPACE(1) +

'ON DATABASE;';

Note the semicolon to delimit the USE statement, and the explicit spaces.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

humbar-a SSC Eights! Points: 970 More actions · Answer 8

At the end i had the success. Here's the result :

DECLARE @STRING1 VARCHAR(500)

declare @db varchar(100)

DECLARE @Flag INT

SET @Flag = 0

WHILE @Flag<22

BEGIN

select @db=name from sysdatabases where name like '%outbound%'

SET @string1 ='use '+@db+';'+' DISABLE TRIGGER Trigger_Name on '+@db+'.dbo.Table_Name'

exec (@string1)

set @flag=@flag+1

end

Thanks a lot to all.

Paul White SSC Guru Points: 150468 More actions · Answer 9

😀 So it was a table trigger after all! 😀

Glad the semi-colon thing helped.

Please look up the QUOTENAME function - it allows your code to cope with database names that contain spaces, for example.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi