In article <7FFA5634-05E8-435A-87C5-CBCD7B4C0ACF (AT) microsoft (DOT) com>, Studen77 wrote:
So you want to take 1 procedure and apply it to multiple databases?

Will this be a different SP every time or the same one?


An easy way is to use the Transfer Objects task and simply specify your proc(s) in there.
You would need multiple instances of the task for each server (or you can use a loop)




--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
www.konesans.com - for all your consultancy needs

Then you can easily set this up in designer. If the stored procs are
the same and the destinations are the same you can simply add the tasks
to designer, choose to take over the stored procs only, choose to drop
the destination objects first and make sure that you haven't specified
anything like "Logins" as well.

No code, no object model.


Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
www.konesans.com - for all your consultancy needs

Thanks very much, Allan. In the case where one would like to transfer stored procedures from a single DB to thousands of other DB's, what would you consider to be the most efficient method in doing so?
(BTW, I hadn't used DTS designer up to this point (I'm a new developer) and found it powerful and intuitive.)

"Allan Mitchell" wrote:

In article <34AF9BA4-69BA-4882-9653-3CBACA793281 (AT) microsoft (DOT) com>, Studen77 wrote:
I am glad you find the designer intuitive. How about you look at the object model and see the property names for the
destination server. You can then create a loop over say a recordset and chage the destination for each loop.

Have a look at our looping example here

Looping, Importing and Archiving
(http://www.sqldts.com/Default.aspx?246)


Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
www.konesans.com - for all your consultancy needs