December 13, 2007 at 7:01 am
Here is my problem:
I have a stored proc that needs to be compiled against a list of client databases. Rather than going in an compiling this stored proc against each database one at a time, does anyone know of a way I can write a script or an SSIS package that will look in a network directory for this stored proc, open the file, suck in the code and compile it against each of the client databases?
Any ideas would be appreciated.
Running SQL Server 2005.
Thank you.
December 13, 2007 at 7:49 am
Hello,
I think you can use the undocumented stored procedure which is available in SQL 2000 i.e., sp_msforeachdb
Look for the syntax and other options by google search
execute this statement from one of your SQL 2005 boxes which has less databases on it.
exec sp_msforeachdb "select * from information_schema.columns"
Thanks
Lucky
December 13, 2007 at 9:33 am
Thank you for the suggestion on how to loop through each database. I am still looking for a way to suck in a text file that has the "CREATE PROC ....etc..." code and compile it (exec it) against each database.
I know I cannot read the text file into a variable since there is a limit on the varchar data type.
December 13, 2007 at 9:56 am
My first question is, are you sure you want to do this? IF all of your databases are on the same server, you could place the stored procedure in one database and then easily call it from any of them by qualifying it with the database name. If the procedure is only designed to work against the current database and that is why you want to put it on all of them, you may be better served adding in a database variable and then qualifying out all references in the table then you would be giving each and every database its own copy of it. Having one central copy will reduce space usage and simplify code maintenance, not to mention you just might get greater execution plan reuse out of it.
If you do need it on every database on one server, I agree with Lucky's suggestion that ms_foreachdb is the way to go. You need to use a little care with the single quotes ', but aside from that it should work well for you.
Aside from those, you may want to look at a third party piece of software. I have never personally used it so cannot personally vouch for it, but I understand the program MultiScript will do something like that.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
December 14, 2007 at 9:53 am
Thank you for your response.
Yes, I do want to compile the proc in each database. I can appreciate your design suggestion, however it is not practical at this point. Based on our software infrustructure, it is required to have the proc compiled in each client's db.
Unfortuately, Lucky's suggestion to use ms_foreachdb will not work for me in this case. Other databases reside on the same server as my clients' databases. So I have written my own loop to hit the correct dbs. However, I am still not clear on how to write a script to loop over each of my clients' db, open a text file and compile the text file against the db.
SET CLIENT_LIST = 'CLIENT A, CLIENT B, CLIENT C...ETC'
SET MY_STORED_PROC = '\\[NETWORK DRIVE]\MY PROC.sql' <---- HOW DO I DO THIS????
* My Proc can be longer than 8k characters so reading the file into a variable is not feasible.
*
WHILE (NOT AT THE END OF THE LIST)
BEGIN
SET MY_CURRENT_DB = [pointer in list]
EXEC ('USE ' + MY_CURRENT_DB + ' GO ' + MY_STORED_PROC)
END
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply