Truncating last char in insert to varchar field in MS SQL

cbres00 · Post by **cbres00** » Tue Oct 25, 2005 2:26 pm

I'm doing a straight (no transform) extract from a Teradata table to a MS SQL table. Both the source and target cols are varchar(50).

When I look at the target table in MS SQL I see that the rightmost character in the varchar(50) field is truncated! I put a transform between the stages and stuck a peek on the transform. The data is extracted from Teradata intact.

I tried to modify the MS SQL insert statement via a user-generated sql:
INSERT into xyz VALUES ('?') and it didn't like the quote wrapper.

I also tinkered with the transform and wrapped the field with single quotes. Instead of getting the entire string inserted, I get the leading quote but no trailing quote.

Example
Source: ab011234
Target: ab01123

Using quote wrappers:
Source: ab011234
Target : 'ab01234

How can I get non-truncated data from Teradata to MS SQL without having to change tables?

Any help would be greatly appreciated!
Regards,
cbres00

kcbland · Post by **kcbland** » Tue Oct 25, 2005 2:35 pm

What are your exact stages in the design?

cbres00 · Post by **cbres00** » Tue Oct 25, 2005 2:43 pm

Teradata stage for the Teradata piece and DRS for the MS SQL piece.

cbres00

kcbland · Post by **kcbland** » Tue Oct 25, 2005 3:06 pm

Have you tried spooling to a sequential file as well to see if the results are the same? I suspect that the straight link between stages is introducing some issue with the metadata compatibilities. You see, if you import the metadata for the source and target separately, you're only allowed one set of metadata to work for both stages. Incompatibilities have to be adjusted using a transformer stage.

cbres00 · Post by **cbres00** » Tue Oct 25, 2005 3:09 pm

I did think about that. :D In fact, that's my backup strategy for any goofy things like this.

I've never used a Modify before. Got any tips?

Regards,
cbres00

kcbland · Post by **kcbland** » Tue Oct 25, 2005 3:11 pm

I mistyped, I meant the transformer. I went back and fixed it, but you must have read it too quick. Give it a shot and see if that fixes the problem. Datatype discrepancies between databases have to be adjusted, I suspect that not at VARCHARs are created equal.

cbres00 · Post by **cbres00** » Tue Oct 25, 2005 3:13 pm

Indeed!!
I'll test and report back.

Hope all is well with you in FLA.

cbres00

kcbland · Post by **kcbland** » Tue Oct 25, 2005 3:15 pm

Cold!!! The storm blew south of Tampa, sucking cold weather our way. It topped out at 70F today. I can't wait for it to get back to 85F.

cbres00 · Post by **cbres00** » Tue Oct 25, 2005 3:18 pm

If that's your only problem..... then you are problem-free indeed!

ameyvaidya · Post by **ameyvaidya** » Tue Oct 25, 2005 6:37 pm

Hi cbres00,

We'had the same problem with DS7.5 EE. Ascential support had confirmed this to be a bug with the DRS stage and had given us a patch.

U might want to have a word with them.

HTH

kcbland · Post by **kcbland** » Tue Oct 25, 2005 7:18 pm

A patch is always good.

cbres00 · Post by **cbres00** » Wed Oct 26, 2005 10:07 am

We upgraded to 7.5.1 a few months ago. I would have thought the patch would be contained in the cd they gave us.

cbres00

ameyvaidya · Post by **ameyvaidya** » Wed Oct 26, 2005 7:49 pm

If I remember correctly, This issue came up around May-Jun 2005 for us Just after the upgrade to 7.5.1. The upgrade broke the DRS Stage. Do not know if our patch came on the CD or was shipped seperately(I wasn't the DS admin). But the symptoms u described are the exact same. We too were inserting records IN MSSQL usig the DRS stage with the last character(s) getting truncated. When we switched to the ODBC stage (with everything else remaining exactly the same), the job worked flawlessly(:idea: Hey thats another thing u could check out).