| Author |
Topic |
|
MatrixOne
Starting Member
11 Posts |
 Posted - 2004-04-06 : 04:37:14
|
| 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 tot he 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'Anyone 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.Any help appriciated. |
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2004-04-06 : 04:42:50
|
| Any chance that one of the source tables composite pk's already exists on the destination table before the transformation takes place?Duane. |
 |
|
|
MatrixOne
Starting Member
11 Posts |
Posted - 2004-04-06 : 05:10:31
|
quote:
Originally posted by ditch
Any chance that one of the source tables composite pk's already exists on the destination table before the transformation takes place?Duane.
Yes 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 |
|
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2004-04-06 : 05:20:34
|
| I think that might be the reason why.If there is a constraint on the destination table then this problem will exist.The transform task is probably doing the equivalent of an insert.Duane. |
|
|
|
MatrixOne
Starting Member
11 Posts |
Posted - 2004-04-06 : 05:57:53
|
quote:
Originally posted by ditch
I think that might be the reason why.If there is a constraint on the destination table then this problem will exist.The transform task is probably doing the equivalent of an insert.Duane.
Ok - thanks for that but you just described the exact problem I have.  Why why why does it 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.  Can I just say Microsoft really had a laugh when they introduced collation  |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-04-06 : 08:36:09
|
| I think you misunderstand ditch....he is asking you if you have data in your destination table already, which could, when combined with the data you are atempting to insert, cause a primary key violation. |
|
|
|
MatrixOne
Starting Member
11 Posts |
Posted - 2004-04-06 : 09:17:50
|
quote:
Originally posted by crazyjoe
I think you misunderstand ditch....he is asking you if you have data in your destination table already, which could, when combined with the data you are atempting to insert, cause a primary key violation.
Ahh, no the temple is completely empty |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-04-06 : 19:23:03
|
| You probably have one collation that's case sensitive and one that's not. This can very well cause this problem. You have mOtor and motor in one table, which is unique. That's an impossibility in the other table.The brains that be in my current position decided that Binary was a great idea. I wail and weep with gnashing of teeth every time I think about it.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
MatrixOne
Starting Member
11 Posts |
Posted - 2004-04-07 : 06:09:52
|
Checked, both are Case insensativeThey are both SQL_latin blah blah blah just one is Cana insensative and one is not.Given that the fields used are either DateTime or int's I do not see how this *should* affect the DTS...  |
|
|
|
MatrixOne
Starting Member
11 Posts |
Posted - 2004-04-07 : 08:24:19
|
| I think the answer maybe due to the date field.I run a Find Duplicate Query on the Source table, and it returned 0 rows.I run the same query on the Destination table (which now does not have a PK) and it returns 468 rows.Looking at each row, the only difference between each record is the Time in the Date Time field.e.g.CustomerID,DomainID,TimeP,Server---------------------------------------------21109,53503,2004-03-26 18:00:00.000,TOWER999 21109,53503,2004-03-26 19:00:00.000,TOWER999 21109,53503,2004-03-29 09:00:00.000,TOWER014 21109,53503,2004-03-29 10:00:00.000,TOWER014 Any Ideas how to resolve this as Im baffeled as to why it is not seeing the difference between the times?????? |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-04-07 : 09:11:42
|
| You got me. Do you have a lot of Japanese in your database????On the Kana sensitive database, do an insert into and copy the table to another table. Then run ALTER TABLE and change the collation on the column. Try the insert. Who uses Kana sensitive? :)MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
DTS PK Failiure