importing store procedures, How-to

Hi,
Can anyone tell me how I can import/export stored procedures in MS SQL 2005
from a different SQL server?  Used to use the right click menu on a database
(tasks, import/export) to do the job using DTS, and update all the SP and
UDF at once.  Now I cannot seem to see a way of doing it using IS
(Integration Services) in 2005, only able to import/export tables and views.

Can anyone please advise, there's got to be an easy way, but I've had no
luck looking on the web or books online.

Thanks
Pritchie

If someone knows how I can export the procedures then re-import them, it
would be appreciated.

Thanks

"Pritchie" <info2005@remove-this-including-dot.bigbunker.com> wrote in
message news:zXzWf.38338$u31.6894@newsfe2-win.ntli.net...
> Hi,
> Can anyone tell me how I can import/export stored procedures in MS SQL
> 2005
> from a different SQL server?  Used to use the right click menu on a
> database
> (tasks, import/export) to do the job using DTS, and update all the SP and
> UDF at once.  Now I cannot seem to see a way of doing it using IS
> (Integration Services) in 2005, only able to import/export tables and
> views.
>
> Can anyone please advise, there's got to be an easy way, but I've had no
> luck looking on the web or books online.
>
> Thanks
> Pritchie
>
>
>

Why not generate the scripts and then load into the new database?  If 2000,
right click DB & choose "Generate SQL Script...", then choose stored
procedures, and click preview.  From there, you can grab the scripts.  Then
go over to your new database and copy the script into the query window and
load.  If in 2005, right click DB & choose Tasks, then Generate Scripts, the
n
choose what you need, etc.

"Shayaan Siddiqui" wrote:

> If someone knows how I can export the procedures then re-import them, it
> would be appreciated.
>
> Thanks
>
> "Pritchie" <info2005@remove-this-including-dot.bigbunker.com> wrote in
> message news:zXzWf.38338$u31.6894@newsfe2-win.ntli.net... 
>
>
>

Hi CB,

Yes, this can be done, however it has a far bigger admin overhead than just
using the import/export task to transfer them.  Is this now the only way to
do it?  In my mind its 2 steps back after 3 steps forward... :o(

Thanks for your help CB..

Grubble to MS staff...
What happened to all the HCI/user friendly icons too in Enterprise
Manager!!!  now all collections (table, views, SP, UDF) all have the same
yellow folder icon, again 2 steps back after 3 steps forward (forward as in
separating the system and user tables).  Such a shame that little things
like this are missed.

Regards
Steve


"CB" <CB@discussions.microsoft.com> wrote in message
news:762C0394-2DDC-496A-96DD- 0CB9C36E4F01@microso
ft.com...
> Why not generate the scripts and then load into the new database?  If
 2000,

> right click DB & choose "Generate SQL Script...", then choose stored
> procedures, and click preview.  From there, you can grab the scripts.
Then
> go over to your new database and copy the script into the query window and
> load.  If in 2005, right click DB & choose Tasks, then Generate Scripts,
then
> choose what you need, etc.
>
> "Shayaan Siddiqui" wrote:
> 
and 
no 

Unless you're looking to automate this process every day or something, I
guess I would disagree about the admin overhead, but that's ok - everybody
has their preferred way of doing things.

If you'd like to use SSIS, you can still transfer objects.  When you open
Vis. Studio & choose Integration Services Project, drag over the "Transfer
SQL Server Objects Task".  Double-click, then choose your source &
destination connections & databases.  Scroll down to the "Objects to Copy"
property & expand it.  Then click the  StoredProceduresList
 collection (and
UDF, etc.) and choose the SPs you want to transfer.  Up at the top you can
choose whether you want to drop the destination objects first, etc. (and a
whole lot of other options).  Then save & choose Debug, Start Debugging if
you want to run in design mode.  Or save the package off to the server &
stick in a job.  Good Luck!

"Pritchie" wrote:

> Hi CB,
>
> Yes, this can be done, however it has a far bigger admin overhead than jus
t
> using the import/export task to transfer them.  Is this now the only way t
o
> do it?  In my mind its 2 steps back after 3 steps forward... :o(
>
> Thanks for your help CB..
>
> Grubble to MS staff...
> What happened to all the HCI/user friendly icons too in Enterprise
> Manager!!!  now all collections (table, views, SP, UDF) all have the same
> yellow folder icon, again 2 steps back after 3 steps forward (forward as i
n
> separating the system and user tables).  Such a shame that little things
> like this are missed.
>
> Regards
> Steve
>
>
> "CB" <CB@discussions.microsoft.com> wrote in message
> news:762C0394-2DDC-496A-96DD- 0CB9C36E4F01@microso
ft.com... 
> 2000, 
> Then 
> then 
> and 
> no 
>
>
>