Alter stored procedures in multiple databases

[Noonoo]

Can anyone help please. Someone out there must already have a solution.

I regularly have to copy stored procedures from a source development database to many other databases. I do this by copying the CREATE PROCEDURE code and pasting it to all the other databases.

What I would like is a way updating or adding a stored procedure from one source database to a collection of other databases. The source database is unlikely to change and neither are the databases I'd like to update. I'd just like to be able to insert the stored procedure name in a piece of code that does the altering for me. All databases are under the same SQL Server Registration.

Hpe that makes sense. Thanks for your time.

 

[SgtJarrow]

I am by no means a SQL wiz, but this seems a bit counterintuitive to me as a VB programmer. The ideal solution here is to have ONE copy of the stored procedure and then have all the databases execute against this one instance - just the same as you would in VB. Then you only have to update the code one time and all databases get the changes immediately.

A quick skim through my SQL reference manuals (since our SQL wiz is out on maternity leave right now) shows the command for executing a stored procedure in another database is:

exec servername.dbname.owner.storedprocedure

So if you have a stroed procedure called sp_RunMe in a database called MySQLLibrary on a server called MySQLServer, which had been created and setup with the SQL admin settings...you would execute it from another SQL server with:

exec MySQLServer.MySQLLibrary.dbo.sp_RunMe

Again, just my two cents and I am NOT a SQL DB Admin or any such...Hope this helps.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[Noonoo]

Thanks for the prompt reply. If I was building from scratch would do what you suggest. I agree with your logic and understand what you're saying.

Unfortunately, I've inherited this system and, trust me, changing the way stored procedures are referenced is too big a task to take on right now. I am stuck with separate databases with their own sp's and have to make the best of it.

Any ideas anyone...

 

[mrdenny]

Unless you are going to go and write a bunch of VB code from scratch your best bet is to deploy the procedures manually to each database.

If you had some time on your hand you could make a nice little VB app that you stick the CREATE PROCEDURE code in, and check which databases need the procedure then have it to the deployment for you.

Many people create a database called Common (or something like that) which they put procedures that are needed in more than one database.

You can also put the databases in master but putting them in a user database is recommended.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[arrowhouse]

I have seen this done in the following manner:

Save script(s) you have created as .sql file(s) in a particular directory.

Then write a batch file to call each of the scripts sat in your selected directory for each of the servers/databases using osql. The output of each query was stored in a file so the could be examined for errors.