I'm currently testing a system that replicates data across a number of different databases. Once testing has been completed I use a stored procedure to reset the data on the master database so that additional tests can be run. I want to write a stored procedure that will reset the data on slave databases the testing has just been run on, but rather than have a stored procedure on each slave just have one on the master, as I could be testing across 1-n slaves.
I can access slave data from the master datbase with [slave1].[dbo].[target-table], and am looking at passing the slave name as a parameter to the stored procedure so the command would be [@.SlaveName].[dbo].[target-table], but any text inside the [ ] seems to be taken as literal string
Any one have any pointers?
If the count or names of your slave databases is/are likely to change then you'd have to do this using dynamic SQL. You could load a cursor with the contents of 'SELECT [name] FROM master.sys.databases WHERE <--insert criteria-->' then iterate through the cursor and create and execute a SQL string for each value that's returned.
For information only, there's an undocumented stored proc called sp_MSForEachDB that can be used to execute a SQL script in each database. However, be warned that undocumented stored procs could be dropped or significantly changed between SQL Server releases, or even service packs, so should not be used in production code.
Chris
|||Solution I cam up was in the stored procedure to have a
EXEC ('USE '+@.SlaveName +'<action to take>')
No comments:
Post a Comment