AAAaaaargh! I said "COPY"...
-
why does SQL2000 DTS seem to be truncating the trailing blanks from my source column when transferring the contents from one SQL DB to another, using a Transform Data Task (transformation type of "Copy Column")??? Doing so causes a duplicate entry in a unique index that was respecting the space as a difference. Yeah, sure this isn't a great idea and the data may be a bit whacked, but I don't have time to go scrubbing every row right now.
-
What is the structure of the tables you are copying?
Thanks
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
-
alrighty. seems I may have been a bit hasty in blaming DTS. It would appear that it does not have to do with the copy as much as the DB's blatant ignorance of the trailing blank when adding the new data to the Unique Index. I am confident that I can change that behavior, but am still looking for the proper place to do so...
-
I am less confident, and beginning to question my sanity...
Anybody know if this can be changed??? (DO NOT ignore trailing blanks on vachar fields when maintaining Unique Indexes)
Edited by - joshcsmith13 on 03/03/2003 4:29:41 PM
-
If the field is varchar then you won't maintain any trailing spaces.
If the field is defined as char, any input is padded out to the length of the field.
eg: ABC[space][space]
in varchar field will always be ABC
in char(5) field will be ABC[space][space]
Thanks
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface -
IMHO you should look at the ANSI_PADDING settings:
Quote:
'ANSI_PADDING
When set to ON, trailing blanks in character values inserted into varchar columns and trailing zeros in binary values inserted into varbinary columns are not trimmed. Values are not padded to the length of the column. When set to OFF, the trailing blanks (for varchar) and zeros (for varbinary) are trimmed. This setting affects only the definition of new columns. '
H.Lindgren
-
I must be screaming now. IT DOESN'T WORK! I have SET (stupid)ANSI_PADDING ON and it does not affect the behavior of my Unique Index. this example is exaclty (or close enough for hand-grenades) what I am trying to accomplish with the following 2 rows:
A B
--- ----
1, '613'
1, '613 '
create unique index UQ_test on Test (A, B).
The values are very obviously saved as indicated, one with the trailing blank, and one without. HOWEVER, I have had no success when attempting to add the Unique index. Another thread I started in the T-SQL forum suggested adding a column to my unique index that keeps the DATALENGTH of my varchar column, but this is much more overhead than I want to invest in this issue.
-
Is there any way we could see the DTS package? IS the data that your getting an append to your table or a truncate to the table? Why are there two unique keys?
Anyway if there was a way you could post the DTS either as a screen shot, or even some of the code.
-
hey folks, thank you for your various responses. As I had aluded to earlier, the problem here turned out to be with the difference in the way Sybase and SQL Server treat varchar fields when maintaining Unique indexes. Since SQL Server is bound and determined to treat 'A' and 'A ' as the same value when maintaining a Unique Index, irregardless of the ANSI_PADDING setting, it became necessary (as I had dreaded) to check for duplicates on every entry and modify the data where necessary. DTS was doing it's job just fine. I have it all working now.
Thanks again.
-
Since I am not a fan of significant trailing blanks, especially as uniqueness qualifier!!!, I'm on the side of Sql Server in this case. My question was going to be, "Is your data corrupt or the design?". Glad to see you worked out the solution by fixing the data!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply