LongVarChar length

RodBarnes · Post by **RodBarnes** » Fri Sep 05, 2008 1:30 pm

For text columns from SQL Server, we have been using the LongVarChar data type within the OLEDB stage. This has worked fine with a length of 128K. But recently some of the users have decided to write complete novels in the text field.

A SQL Server text field can be up to 2GB (2^31) but the DS help is not very forthcoming on how long a LongVarChar can be. At one point, I was unable to increase the field length beyond 944,745. Attempts to use a larger field length (944,746 or more) immediately produced this abort:

Code: Select all

Program "DSP.Open": Line 122, Exception raised in GCI subroutine:
Access violation.
Attempting to Cleanup after ABORT raised in stage jobExportCaseNotes..vision_LoadCase
DataStage Phantom Aborting with @ABORT.CODE = 3

Experimenting, I found that the maximum length is actually a factor of the total length of the record buffer (makes sense). I changed one column from an Integer(1) to a VarChar(20) and had to reduce the length of the LongVarChar to 944,721 before it would run. Increasing the "Buffer Size" on the job's "Performance" tab from 128KB to 1024KB didn't seem to have any affect on what I could do with the length. However, dropping the array size did allow me to increase the length. For example, dropping the array size from 500 to 200 let me increase the length to 2,100,000.

Anyone have a working alternative for handling text columns of great length?

ray.wurlod · Post by **ray.wurlod** » Fri Sep 05, 2008 5:41 pm

Assassinate the novelists?

RodBarnes · Post by **RodBarnes** » Fri Sep 05, 2008 6:20 pm

ray.wurlod wrote:Assassinate the novelists?

Too funny!!

jdmiceli · Post by **jdmiceli** » Fri Dec 19, 2008 10:47 am

Hey Rod!

Did you ever come to a resolution to this problem? I am having similar issues with regards to a straight pull involving novelists as well. Right now I am using SQL Server 2000 and DTS to move the data, but we keep getting hit by a bug that affects binary data being passed over a linked server.

I would like to have DS handle the transfer since there is no translating or anything being done. I am using the RDBMS stage and I can read the information just fine from source, but inserting into the target gets me the following errors:

Code: Select all

First message:
CLM_TEXT_Transfer..tgt_CLM_TEXT: [DataDirect][ODBC SQL Server Driver]Invalid SQL data type

Second message:
CLM_TEXT_Transfer..tgt_CLM_TEXT: SQLBindParameter: Failed to bind a parameter.

I have tried many different combinations, but seem to have no luck getting the LONGVARCHAR to bind to the TEXT datatype on SQL Server's side. Have you had this error too or have you been able to fully move your data? If you were able to do so, I would be greatly interested in the mapping path for the one text datatype field so I can try and get mine to work.

I think I may be hosed because I cannot use the OLEDB stage with AIX based DataStage (please correct me if I am wrong!)

RodBarnes · Post by **RodBarnes** » Mon Dec 22, 2008 10:02 am

Well, sort of. I didn't get any additional info from anyone. I just ended up doing what I described. I set the length to 2,100,000 and the array size to 200. This worked for my situation and I got no more aborts.

jdmiceli · Post by **jdmiceli** » Mon Dec 22, 2008 1:20 pm

Bummer...

I tried your solution and it won't work for me, primarily because I don't have the OLEDB Stage available as we are running on AIX Unix. If I am wrong, please feel free to set me straight.

Thanks!

ray.wurlod · Post by **ray.wurlod** » Mon Dec 22, 2008 2:06 pm

Alas for you John, OLEDB is only for Windows.

jdmiceli · Post by **jdmiceli** » Mon Dec 22, 2008 4:07 pm

I am experimenting with the RDBMS Stage docs that supposedly teach you to handle it. I will post my results.