Copy SQL Stored Procedures between DataSources/Schemas?

Question

Hi Friends, I've created SQL stored procedures in our development schema and its working fine. Now we are in the task of moving our development codes to production one. How do I copy those stored procedures from one schema to another schema? Do I need to re-compile whole procedures on targeted schema? Or Is there any way register those procedures on targeted schema? Could any one suggest me a better way to copy stored procedures?

I've created procedures through iSeries Navigator for Windows Version 5 Release 4.

Thanks in Advance, Suresh

Answer 1

Please read the link ,This might be helpful. http://weblogs.asp.net/steveschofield/archive/2005/12/31/change-schema-name-on-tables-and-stored-procedures-in-sql-server-2005.aspx

Answer 2

The SQL to create your stored procedures should be in source files as part of a project in some source control system (CVS, SVN, etc.).

To "deploy" your project to a new server (Dev, QA or Production) should simply require executing those "source SQL" files on the new server / database.

Indeed, any changes to the database (table columns altered, new tables added) should be in SQL files in source control, and applied the same way.

Although the database development tools don't always make it seem like it - SQL is source code too. :-)

Ron

Answer 3

The best way (so far) that I've found was working with SQL Management Studio, and using the Script Procedure As ... menu options. This will produce the SQL source for your sprocs (CREATE/ALTER depending on which menu option you use)

If you need to bulk-copy all the sprocs/user defined functions, use the Tasks->Generate Scripts menu option. In the resulting series of dialogs, you can choose which sprocs/functions to generate scripts for, then run those scripts on your new db.