Copy/Move stored procedures to database

Hi,
Is there any way to copy or move the stored procedures from one
database to another database?
Regards, Martin
the conventional way to do it is to script them out and then run the script
in the database you wish to recreate them in.
here is a sqldmo script you can run to script the procs out.
set objServer = CreateObject("SQLDMO.SQLServer")
objServer.Connect "hilary2kp", "sa", "se1cure#"
set objDatabase=objServer.databases("pubs")
for each storedprocedure in objDatabase.StoredProcedures
wscript.echo storedprocedure.name
storedprocedure.script 294,"c:\storedprocs.sql"
next
set storedprocedure=nothing
set objdatabase=nothing
objServer.Disconnect
set SQLServer=nothing
Oh, btw, you can also use replication to replicate the procs. If there is a
publication in your database you can use sp_addscriptexec to distribute a
stored proc creation script to all you subscribers which use a unc (ie our
ftp subscribers won't get the script).
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Martin Heuckeroth" <nick@.nospam.invalid> wrote in message
news:tpakj0ps11l3cqn45d5jf87lq529dvlko1@.4ax.com...
>
> Hi,
> Is there any way to copy or move the stored procedures from one
> database to another database?
> Regards, Martin
|||This will copy an object
http://www.nigelrivett.net/SQL-DMOTransfer.html
It doesn't need all that code - I've left it in as a hint as to what's
available.
"Hilary Cotter" wrote:

> the conventional way to do it is to script them out and then run the script
> in the database you wish to recreate them in.
> here is a sqldmo script you can run to script the procs out.
> set objServer = CreateObject("SQLDMO.SQLServer")
> objServer.Connect "hilary2kp", "sa", "se1cure#"
> set objDatabase=objServer.databases("pubs")
> for each storedprocedure in objDatabase.StoredProcedures
> wscript.echo storedprocedure.name
> storedprocedure.script 294,"c:\storedprocs.sql"
> next
> set storedprocedure=nothing
> set objdatabase=nothing
> objServer.Disconnect
> set SQLServer=nothing
> Oh, btw, you can also use replication to replicate the procs. If there is a
> publication in your database you can use sp_addscriptexec to distribute a
> stored proc creation script to all you subscribers which use a unc (ie our
> ftp subscribers won't get the script).
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Martin Heuckeroth" <nick@.nospam.invalid> wrote in message
> news:tpakj0ps11l3cqn45d5jf87lq529dvlko1@.4ax.com...
>
>
|||Martin,
there is a Copy SQL Server Objects Task in DTS for this type of thing.
HTH,
Paul Ibison
(The ONLY sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)