Truncating last char in insert to varchar field in MS SQL

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
cbres00
Participant
Posts: 34
Joined: Tue Sep 21, 2004 9:20 am

Truncating last char in insert to varchar field in MS SQL

Post by cbres00 »

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
Participant
Posts: 5208
Joined: Wed Jan 15, 2003 8:56 am
Location: Lutz, FL
Contact:

Post by kcbland »

What are your exact stages in the design?
Kenneth Bland

Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
cbres00
Participant
Posts: 34
Joined: Tue Sep 21, 2004 9:20 am

Post by cbres00 »

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

cbres00
kcbland
Participant
Posts: 5208
Joined: Wed Jan 15, 2003 8:56 am
Location: Lutz, FL
Contact:

Post by kcbland »

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.
Kenneth Bland

Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
cbres00
Participant
Posts: 34
Joined: Tue Sep 21, 2004 9:20 am

Post by cbres00 »

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
Participant
Posts: 5208
Joined: Wed Jan 15, 2003 8:56 am
Location: Lutz, FL
Contact:

Post by kcbland »

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.
Kenneth Bland

Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
cbres00
Participant
Posts: 34
Joined: Tue Sep 21, 2004 9:20 am

Post by cbres00 »

Indeed!!
I'll test and report back.

Hope all is well with you in FLA.

cbres00
kcbland
Participant
Posts: 5208
Joined: Wed Jan 15, 2003 8:56 am
Location: Lutz, FL
Contact:

Post by kcbland »

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. 8)
Kenneth Bland

Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
cbres00
Participant
Posts: 34
Joined: Tue Sep 21, 2004 9:20 am

Post by cbres00 »

If that's your only problem..... then you are problem-free indeed!
ameyvaidya
Charter Member
Charter Member
Posts: 166
Joined: Wed Mar 16, 2005 6:52 am
Location: Mumbai, India

Post by ameyvaidya »

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
Amey Vaidya<i>
I am rarely happier than when spending an entire day programming my computer to perform automatically a task that it would otherwise take me a good ten seconds to do by hand.</i>
<i>- Douglas Adams</i>
kcbland
Participant
Posts: 5208
Joined: Wed Jan 15, 2003 8:56 am
Location: Lutz, FL
Contact:

Post by kcbland »

A patch is always good. :lol:
Kenneth Bland

Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
cbres00
Participant
Posts: 34
Joined: Tue Sep 21, 2004 9:20 am

Post by cbres00 »

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
Charter Member
Charter Member
Posts: 166
Joined: Wed Mar 16, 2005 6:52 am
Location: Mumbai, India

Post by ameyvaidya »

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).
Amey Vaidya<i>
I am rarely happier than when spending an entire day programming my computer to perform automatically a task that it would otherwise take me a good ten seconds to do by hand.</i>
<i>- Douglas Adams</i>
Post Reply