sql server extract - corrupt data when using an odbc stage

[Rob4732]

Hello Everyone,

We are connecting to a sql server database with datastage for the first time. The odbc configuration is complete and we are communicating with the database from parallel jobs. When extracting data from a sql server table(via an odbc stage), we noticed that some of the data in nvarchar columns is corrupted in the job(extra garbage after the data). Looks fine in the database, but not in the job. Codeset difference could be to blame. We tried setting datastage codeset to unicode, but still getting bad data.

We are extracting an integer and an nvarchar(see resulting data below):

Data in database:

ID LAST_NM
345 Kraska

Extracting same data in datastage and dumping to sequential file:

ID LAST_NM
345[null][null][null][sub][esc] Kraska[null]T_NM[null][null]

It appears as if part of the column name(T_NM) is in the data. When extracting multiple records, bad data appears to bleed down multiple recs.

DRS stage seems to work fine.

Any ideas appreciated.

Thx

Robert

[ray.wurlod]

Can you read the data successfully if you change the data type to VarChar - possibly with maximum length set up to four times the NVarChar column length specification?

[Rob4732]

I changed the LAST_NAME column from nvarchar 50 to varchar 100 and re ran. Same bad data.

When I change my database stage from ODBC to DRS and run same extract(using nvarchar 50) my data looks good.

ID LAST_NM
345Kraska

[kumar_s]

But what happend to the space inbetween the Number and Name?
Is that a special character avaialble in the Database itself?

[Rob4732]

Sorry, that was my faux pas. I originally typed in the results adding a space to make it clearer.

thx