Copying Stored Procedures
-
I recently tried to use DTS to copy all objects of one database to a new instance of SQL Server 2000 on another server. Everything seemed to move EXCEPT the stored procedures, especially the "user" stored procedures. I've tried using DTS just to copy one stored procedure by itself DTS says the object was copied successfully, but when I go look at the stored procedure list for the new DB, it isn't there.
Being a newbie, I'm not quite sure how to "script" the copying of these procedures from one machine to the other.
-
Richard - You can do this via Enterprise Mgr.
Right-click the db name---> All Tasks, Click Script, then choose 'Show All', then 'Stored Procedures'.
Script to a file name of your choosing. Then edit that file, copy and paste into a Q-A window and create the SPs into your new database.
Hope this helps. John
-
I did this. Just as you said. Sent all the SPs to a file, opened the file in notepad, highlighted every single line, Ctrl-C, opened a QA window in that instance and in that DB, ctrl-V, F5. It ran with a bunch of notations about unable to create some row. No new SPs in the new instance/DB.
-
Are you talking about sysdepends error/comment?
If so you can just ignore it, assuming you retest every proc to make sure it works fine
. -
Yep. Those are the ones. Thanx!
-
In Enterprise Manager, you always have to click "REFRESH" in order to see the current list of Stored Procedures, Tables etc ... Perhaps that's why you don't see the new ones ?
-
Okay, all of the ones that did not migrate are of type System. All the others are there, now. This apparently has some implication as to the migration/copy. Not being a DBA, I'm not sure what.
-
You can't create the system procs if they are already there... or maybe they are reserved in some way???
Anyways those system procs will recreate themselves when needed.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply