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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-04-09 : 12:32:18
|
| Mark writes "I have come accross an interesting problem and was wondering if anyone else has come accross something similar.I have a DTS package which executes a data transfomation between 2 servers and needs to do a one of copy of 100 million rows.DTS Transform is probably not the best tool to use, and I probably will use bcp in future, but there I am still curious about this problem.On ServerA (Source) I have a table which has 7 columns and 100 million rows. 4 of the 7 Fields are a composite PK as followsName Datatype-----------------------------------CustomerId intDomainId intTimeP Date/TimeServerId intThe other fields are ints also.On ServerB (Destination) I have the same exact table, with the EXACT same composite PK.The problem is, on the final batch insert, the DTS fails due to a PK violation. Now this should be impossibleas the Source server cannot have a duplicate key in it to transform to the destination server.I use a query to select the data to copy which is as follows:-SELECT * FROM SERVERA WITH (NOLOCK)WHERE TIMEP >= '2004-01-01 00:00:00.000'AND TIMEP <= '2004-04-05 23:59:59.999'Would you know why this should occur? I have no transformations being performed, and selected the correct destination table etc. The only thing I have specified is the BatchInsertSize.The Comp Key is already existing before the insert. Is this incorrect? I have done this as I dont want to execute an ALTER TABLE on a table with 100 million rows after the insert as the Temp DB has limmited space available. I just do not understand why it would have a Primary Key violation inserting data in the Destination table when the source table has exactly the same Primary Key as the destination table???????? I am correct in assuming that the source table CANNOT hold any primary key duplicates in it - else it would break the rules of its PK and the data would not exist in the Source table in the first place.If that is true, why when transfering the SAME data from that table to another table on a different server, but with exactly the same PK specified, does the DTS throw up a PK violation error on insert.Is there somewhere in the DTS that it just "hmmm we have this data in the source, and we could just copy it straight accross, but no well change some of it first"?????????HmmmI have noticed that the servers have a differing collation... I wondering if this causing the problems? I have already experianced the Collation problem when running a distributed Transaction using a INSERT, EXECUTE from a remote Stored Proc, the process just hangs compeletely and the when eventually the process times out, Orphaned SPID's are left hogging resources on both servers.... which have to be killed by their workspace ID or reboot the damn server. Do you think this could be the root of all evil, or is it another problem?" |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-04-09 : 13:05:49
|
| Is the source server collation using mixed case?Brett8-) |
 |
|
|
|
|
|
|
|
DTS Import Fails due to duplicate PK Error