how to copy some stored procedure in SQL 2005

How to copy only some stored procedure from dev server into prod server in sql server 2005? Should I use import/export wizard or copy database wizard? Thanks in advance.

Right click on the database in the object explorer, goTasks->Generate Scripts. SetScript Object-Level Permission toTrue, click Next, check only theStored procedures, you will see a list of SPs after clicking Next. Pick the ones you like and go Next, then select preferred script mode.

|||

Thanks jack for your reply. Is there a way to differentiate between stor proc that is already in the prod server and which are not? Thanks.

|||

It's better to use 3rd party tool to handle the comparisons. Even if a SP exist in the production server, it does not mean it's has not been altered on your dev server.

Here's a good but expensive one:http://www.red-gate.com/products/SQL_Compare/index.htm

Visual Studio Team Edition for Database Professionals edition can do this too, but it's even more expensive:http://msdn2.microsoft.com/en-us/teamsystem/aa718807.aspx

|||

Thanks.

how to copy only the stored procedures to another server/database

Hi all,
I am just getting my feet wet with SQL server, and need
to only copy the stored procedures from one db to another. I tried the
export tool, but even though it allows me to select the objects I want, it
also tries to copy other objects (such as user names), and it fails because
they already exist in the other db.
Thanks in advance,
Carlos.Don=B4t know what you mean by "export tool", But either in the Script
generator of the Enterprise manager as well as in the DTS wizard you
can define if you want to script out / export the depended objects. If
you don=B4t want them just change the tab, deselct the options and
script out / export the objects to the other database.
HTH, jens Suessmeyer.
--
http://www.sqlserver2005.de
--