November 11, 2005 at 2:14 pm
I have the following scenario where I need to move stored procedures and functions only from a Test Environment to Production:
Dev and Test environments are located on the same server.
I created a DTS package with a "Copy SQL Server Objects Task" in it and specified the Test Database as Source and Production Database as the Destination, selected objects (only functions and stored procedures) from the "Copy" tab.
The problem I am having is that even though I specifically do not select any table objects, the data in my tables in my Production Environment is being overwritten with data from my Test Envirnment ( I even tried deselecting the "copy data" check box and "include all dependent objects"..and get the same result.
Anyone know how to solve this problem ? I eventually just ended up scripting out my functions and stored procedures and executing the script on my Production Environment.
Also as a side question..Is there any way to programatically select the objects I want to transfer using the "Copy SQL Server Objects Task" task ?
TIA
Mathew J Kulangara
sqladventures.blogspot.com
November 11, 2005 at 5:52 pm
If you are frequently having to perform this task, you may want to look into a utility designed specifically for this task. I can speak for Red-Gate's SQL Compare as an ideal utility to yield these results.
Another option would be to write a quick VBScript that accesses the the objects via SQLDMO. You could drop all the required objects on your destination db, read from source and add the objects tot destination.
-Mike Gercevich
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply