Problems with MSSQL text data type

mmcclurg · Post by **mmcclurg** » Thu Nov 10, 2005 10:23 am

Couldn't find any info on this - I'm taking a SQL Server table and copying all the rows to an identical table on another SQL Server. I'm having problems with one field which is a text data type.

When I import the table definitions into Datastage (v 7.0.1) it calls the text field a LongVarChar of length 2147483647. Everything is setup OK - OLE/DB on one end, SQL Bulk Loader on the other with a transformer stage in between doing no transformations - I get "Array Has Invalid Rows" errors on each row in Director.

I know the job is OK, because I've defaulted the text in the destination to a short text string and it imports OK. I know my version of Datastage has some problems with SQL text fields, is there any way around this?

Thanks in advance!
Matt

ray.wurlod · Post by **ray.wurlod** » Thu Nov 10, 2005 2:04 pm

Change the data type of the text field to VarChar 4000 (Long VarChar may still work). Change the derivation of the text field to CAST(textfield AS VARCHAR(4000)).