| Author |
Topic |
|
hiuhiu
Starting Member
18 Posts |
 Posted - 2004-01-08 : 23:30:04
|
| When I tried to export my database to a new database on the same SQL Server 2000 instance using the DTS Import/Export Wizard, I faced the problem in which some of the objects couldn't be transfered due to dependencies problem. This is what I think - View A depends on Table B and C, but the DTS wizard exported View A to the new DB before it starts to export Table B and C, so View A is an invalid object so the transfer failed.But I suppose the DTS Import/Export Wizard will have the intelligent to figure out the sequence of the objects that should be exported.I have already checked the following in the DTS Wizard:- Create Destination objects (tables, views, stored procedures, constraints, etc.)- Drop destination objects first- Include all dependent objects- Copy data- Replace existing data- Copy all objectsI did not check 'Use default options', inside Options, I checked these:- Copy object-level permissions- Copy indexes- Copy triggers- Copy PRIMARY and FOREIGN keys- Use quoted identifiers when copying objectsSo, what have I done wrong and how to resolve it?Thank you so much.HIU |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-01-09 : 12:10:43
|
| Why aren't you just doing a BACKUP/RESTORE or DETACH/ATTACH? That will solve your problem and will be much faster.Tara |
 |
|
|
hiuhiu
Starting Member
18 Posts |
Posted - 2004-01-10 : 19:26:57
|
quote:
Originally posted by tduggan
Why aren't you just doing a BACKUP/RESTORE or DETACH/ATTACH? That will solve your problem and will be much faster.Tara
Thanks for the suggestion. But since I am kind of new in SQL Server, I want to learn more and figure out what might go wrong with the way I use DTS. I suppose a feature is there so it's supposed to work if I don't mess up the procedure to do it.  By the way, if the ways you suggested are faster and easier, then in what situations will people choose to use DTS?HIU |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-01-10 : 19:32:19
|
| In order to restore a database the user would need to have some admin-level permissions that may not be appropriate for a casual user. DTS would allow them to transfer a database without needing those permissions. DTS is also a lot more flexible in transferring specific objects; a restore will give you everything unless the database is very specifically designed (filegroups, partitions, etc.) But if permissions aren't a problem and you want to move an entire DB around, backup/restore and detach/attach can't be beat. |
|
|
|
hiuhiu
Starting Member
18 Posts |
Posted - 2004-01-11 : 05:50:36
|
quote:
Originally posted by robvolk
In order to restore a database the user would need to have some admin-level permissions that may not be appropriate for a casual user. DTS would allow them to transfer a database without needing those permissions. DTS is also a lot more flexible in transferring specific objects; a restore will give you everything unless the database is very specifically designed (filegroups, partitions, etc.) But if permissions aren't a problem and you want to move an entire DB around, backup/restore and detach/attach can't be beat.
OK, now that I know when to use DTS, but I still don't know why I got an error when using it. Actually I just assume it is a dependency problem but I am not sure. All I received was an error message saying the transfer failed. And some of the objects were not transfered to the new database.I know I am bugging you guys, but can anyone tell me whether my assumption is correct about the dependency problem for using DTS? And is there anyway to resolve this issue? I know backup/restore is easier but just want to know what I have done wrong with DTS.Thank you very much.HIU |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-01-12 : 12:21:52
|
| A lot of people here have reported problems with the copy database wizard. I don't use it. I can guess what the problem is though. The problem that you are having is that the tables aren't being transferred in the correct order. The parent tables need to be transferred first, then the child tables. This is due to referential integrity. The wizard is probably not smart enough to do this for you. People don't use the wizard cuz it's a wizard. Most people with experience avoid wizards since they know the better way of doing things.Tara |
|
|
|
hiuhiu
Starting Member
18 Posts |
Posted - 2004-01-12 : 21:55:23
|
quote:
Originally posted by tduggan
A lot of people here have reported problems with the copy database wizard. I don't use it. I can guess what the problem is though. The problem that you are having is that the tables are being transferred in the correct order. The parent tables need to be transferred first, then the child tables. This is due to referential integrity. The wizard is probably not smart enough to do this for you. People don't use the wizard cuz it's a wizard. Most people with experience avoid wizards since they know the better way of doing things.Tara
Thanks for the answer! As I said, I am not experienced in SQL Server. I really appreciate all your help and patient. I have made the wrong assumption that the wizard should be at least smart enough to figure out the referential integrity.  Thanks again!HIU |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2004-01-14 : 03:37:20
|
| If you look at the DTS package you can modify the workflow that so that certain events only happen after others have succeeded.If you want more information on that I can tell you in two weeks after I've done the course :-)steveSteve no function beer well without |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-01-14 : 12:16:09
|
| He's using the wizard, so there is nothing to configure. You also would have to figure out the order which can be a real pain if you're child tables are several levels deep. BACKUP/RESTORE is so easy and it grabs everything.Tara |
|
|
|
|
Dependencies Error using DTS