Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
purisqlserver
Yak Posting Veteran
73 Posts |
 Posted - 2002-03-14 : 08:09:28
|
| While transferring five tables from server I to server II using DTS, a error was triggered for the second time. this package has to run on regular basis. The complete process is set as job.During DTS process, the parent table data on server II is first deleted and then inserted,the same happening with other tables. The error is triggered,since the parent table is deleted and the child constraints are fired. In order to prevent this, can the flow of data be controlled,means first the parent tables and then the child tables.Thanx........ |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-03-14 : 09:30:02
|
| purisqlserver,If I understand you correctly you're using the Copy SQL Server objects task in DTS, specifying tables which have foreign key relationships to each other. You're encountering an error because the task is dropping the primary key tables first.This is a bug with DTS; it does not script the table objects in an order sensitive to the FK relationships.I would suggest two options:(1) since you only have five tables, it is not too effort-intensive to place five Transform Data Tasks in the package and properly sequence them yourself, or(2) run a Execute SQL Task which removes the FK relationships before the tables are deleted/moved, and then another Execute SQL Task which restores the FK relationships after. |
 |
|
|
purisqlserver
Yak Posting Veteran
73 Posts |
Posted - 2002-03-15 : 07:32:20
|
| Thanx for help........But still the first option doesn't work.I tried out with the second option,it works fine. The number of queries required to drop the FK constraints are many,as other related tables are also present. How cann we drop all the FK's constraints related to a particular table at a shot. After the process also ,the FK's have to built,it takes statements to do it, is there a better solution.The complete process happening in detail:Five tables are transferred from ServerI to ServerII(temprorary database). On the temprorary database, the prices columns are updated using backend programs. Later on, these tables are scheduled for DTS from ServerII(temp.. DB) to the production database on ServerII. There are other related tables on all these database. |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-03-15 : 09:06:52
|
| If you really want to use the Copy SQL Server task in DTS, you could split it into two tasks that run serially - one for the parent table(s), and then one running after it for the children table(s). |
|
|
|
purisqlserver
Yak Posting Veteran
73 Posts |
Posted - 2002-03-17 : 23:44:13
|
| "If you really want to use the Copy SQL Server task in DTS, you could split it into two tasks that run serially - one for the parent table(s), and then one running after it for the children table(s)."Your statement tells me that, there is some other solution,which is that??????Well, I did try it out serially , having the parent tables as first and later the child tables in the same job instance. The error is still trigerred since the child tables are already present at the destination. |
|
|
|
|
|
|
|
|
DTS Error