DTS fails on some tasks
-
Hi,
What can I do about DTS failing to complete the transfer of objects and data from my local SQL Server 2000 box to an online production SQL Server 2000 box.
If, for example, I need to move several tables, views and stored procedures along with all associated data (but not associated tables, views etc), DTS will usually fail if it comes across a table or view name that it needs to reference (when creating a particular table or view) that doesn't yet exist.
It would be a view or table that is set to be copied across in the same DTS package, but it hasn't yet been copied and therefore it doesn't exist and the package fails.
Whats a sensible solution to this as I currently have to copy individual (or small groups of 2 or 3) tables and/or views at a time to avoid issues like this?
Anyone else have this problem?
How is it resolved?
Much appreciated.
Cheers!
-
DTS doesn't always do a good job of figuring out which objects need to be moved in what order based on dependencies. If this is going to be a repeating process you would probably do well to develop a single DTS package with the appropriate flow controls in it that force DTS to create objects and populate them in the correct order.
You can also just script out the tables that need to be moved along with the INSERT INTO...SELECT FROM statements to populate them, and wait until the end of the whole process to create your views, procedures and foreign keys.
HTH
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Thanks for the post.
I'll probably go down the route of scripting them at some point then. It's a shame DTS didn't have a checking engine built into it to see which order stuff needs to happen in!
On a related note, I sometimes have issues with database objects not copying over due to the object having had a name change at some point in it's life. Whats the general method for successfully renaming any object in the database to avoid future errors? Is it simply a case of making sure the primary keys and indexes are rebuilt each time, or is there something else?
Cheers!
-
I can't think of a reason an object wouldn't copy if it had been renamed... Dependent objects like views, triggers, and procs will have problems if the objects they reference have been renamed. In that case there is no easy way to take care of that situation except that you should be aware of any dependencies before you rename an object and make the appropriate changes to all dependent objects.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Rob,
You didn't say which task you're using to tranfer objects. If you use the Copy SQL Server Objects task, check 'Include all dependent objects' on the 'Copy' tab. It hasn't failed yet to copy a needed table before copying a view.
As for your second question, do you mean that you refer to objects in a DTS package that may be renamed at some point? If that's the case, you'll just have to change the object name in the package. DTS can't know that an object has been renamed.
Hope this helps.
Greg
Greg
-
Ok, I guess it must be an anomily in this database!
No problem for now.
Many thanks for your quick replies!
Cheers!
-
Hi Greg,
I use Copy SQL Server Objects and Data option in DTS.
I never tick Include all dependent objects because some of the dependant objects don't want to be copied across (users tables and such) as the online version is the most up to date copy - i.e. users could be removed if I copied this table from local to remote!
I guess thats the root cause of my issue really. If I could somehow have DTS copy dependent objects without copying _certain_ ones, such as the users table, then that would be the solution.
I think, for now, I'm going to have to write my own packages to get around this issue.
Thanks for the post though!
Cheers!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply