October 9, 2003 at 2:15 pm
Hi,
Does someone know how to move a lot of store procedures to another SQL Server 2000? This new server has 90% of databases and tables from the old server.
Thanks.
October 9, 2003 at 2:28 pm
Or script all necessary SPs from old server and recreate them in the new server?
October 9, 2003 at 2:34 pm
Well, I didn't count exactly how many stored procedures in the old server, but it's more than 100 SPs, and we want only 50% of them. I am thinking to use DTS to move all the SPs to the new one, then delete the ones that I don't want. Will this way cost me the shortest time to do it or there's a better way?
Thank you.
October 10, 2003 at 2:35 am
Use Enterprise Manager. Select the SPs you wish to transfer, right click and choose Generate SQL Script. Select any additional funcitons or views that you wish to script and set you options (I'd recommend scripting object permissions here) and save the script to disk. Now open Query Analyzer, connect to your other server, select the database, open and run the script.
October 13, 2003 at 10:08 am
You can also use EM Import/Export or the DTS copy sql server objects task. There is a point at which you can select the objects to copy - rather than all objects. Just make sure that the option to include dependent objects is not checked - it is by default. You don't want to drop a table with data by mistake! Also, don't accept the default options. Do not select to copy logins, users, or groups unless you really want to - what a mess it can make. The copy object permissions can be used if you already have all the source roles and users - with specific object permissions of objects being moved - in the destination.
Randy
Helpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply