October 28, 2013 at 12:10 pm
Hi,
Thanks for your help in advance. I am trying to run a script against a number of databases simply turning a trigger off in each one. However I am unable to set the USE database and the actual disable script within the same sp_executeSQL and therefore it is simply running against the same database as the first script.
If you can check my code and seeing where I am going wrong that would be really helpful.
Many Thanks,
Oliver
Declare @dataFiles Table (databaseName Varchar(256))
Declare @SQL Nvarchar(Max), @databaseName Varchar(256)
Insert into @dataFiles (databaseName)
VALUES ('ODM_ANZ'),('ODM_CAM')
Declare cur Cursor For
Select databaseName
From @dataFiles
Open cur
Fetch Next
From cur
Into @databaseName
While @@Fetch_Status = 0
Begin
Set @SQL = 'USE ' + @databasename
Exec sp_executeSQL @SQL
print @SQL
set @SQL = 'DISABLE TRIGGER DBO.Trig_Locations ON [dbo].[WELLS]'
Exec sp_executeSQL @SQL
Print @SQL
Fetch Next
From cur
Into @databaseName
End
Close cur
Deallocate cur
returns
(2 row(s) affected)
USE ODM_ANZ
DISABLE TRIGGER DBO.Trig_Locations ON [dbo].[WELLS]
USE ODM_CAM
DISABLE TRIGGER DBO.Trig_Locations ON [dbo].[WELLS]
but is only actually ever running on the first database????
October 28, 2013 at 12:18 pm
When you execute dynamic sql the scope is only for the current commands. The context of the executing code does not change. In other words, your statements are executing but they need to be run at the same time. Change your code to this and you should be pretty close.
Declare @dataFiles Table (databaseName Varchar(256))
Declare @SQL Nvarchar(Max), @databaseName Varchar(256)
Insert into @dataFiles (databaseName)
VALUES ('ODM_ANZ'),('ODM_CAM')
Declare cur Cursor For
Select databaseName
From @dataFiles
Open cur
Fetch Next
From cur
Into @databaseName
While @@Fetch_Status = 0
Begin
Set @SQL = 'USE ' + @databasename + '; DISABLE TRIGGER DBO.Trig_Locations ON [dbo].[WELLS]'
--Exec sp_executeSQL @SQL
Print @SQL
Fetch Next
From cur
Into @databaseName
End
Close cur
Deallocate cur
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 28, 2013 at 2:22 pm
Thanks so much Sean, really appreciate the help it's working like a gem now.
Thanks again,
Oliver
October 28, 2013 at 2:55 pm
oliver.morris (10/28/2013)
Thanks so much Sean, really appreciate the help it's working like a gem now.Thanks again,
Oliver
You're welcome. Glad that worked for you and thanks for letting me know.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply