Using ODBC stage accessing SQL Server Text field

NigeGriff · Post by **NigeGriff** » Mon Mar 07, 2005 9:40 am

Hi, I am currently trying to read a SQL Server table that contains XML in a field defined as text. The job is able to process some of the rows of data, but when reaching a certain row the job abends with 'Abnormal termination of stage ... detected'. I have imported the column metadata from the SQL Server database and the text fields are defined as longvarchar. I think it maybe data within the text field that is causing problems and I have tried to add transform rule that will convert any invalid characters, but with no success. I will be using an XML input stage further within the processing but initially I need to pull the data out for initial transformation into a sequential file stage. I am using Datastage 7.0 on Unix AIX, SQL Server db is SQL Server 2000. Has anyone experienced similar problems or is there anyway you can get some more meaningful error messages out, instead on just crashing your job with an Abnormal termination message.

Thanks, Nigel

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Mon Mar 07, 2005 9:54 am

Try processing the data without the XML column in the input. If this works, try to locate the exact record giving problem by running a specific number of rows from top.

NigeGriff · Post by **NigeGriff** » Mon Mar 07, 2005 10:01 am

That was a quick reply. Thanks for that. I have just run through without the xml field and it runs ok. Then when stepping through using debug, I get down to the 18th record, then crash. The xml data for the 18th and other records around it look ok, but I can't check all, there are 1000's of bytes in the xml field.

Thanks,
Nigel

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Mon Mar 07, 2005 10:10 am

You can look for something like a kill char in Unix like '@' or '^C'.

Try using substring in SQL server. That will tell you the exact location of error - same like the record count that pointed you to 18th record.

NigeGriff · Post by **NigeGriff** » Tue Mar 08, 2005 4:27 am

I have tried using substring and when using 8k (max length for substring)chunks the data runs through ok. The xml text is 68748 bytes long, and I have substring'd all of it and it comes through ok. The field is defined as longvarchar with a length of 2147483647. Is there a limit on the maximum field length of data coming through the ODBC stage that anyone is aware of. When removing all records with text over approx 64k it runs ok but not when they are over this amount. Could this be a known bug.

Thanks,
Nigel

ray.wurlod · Post by **ray.wurlod** » Tue Mar 08, 2005 2:00 pm

I think it's a known restriction in ODBC. Long VarChar is limited to 64KB.

roy · Post by **roy** » Tue Aug 30, 2005 9:39 am

Hi All,
I was wondering is there any solution or work around anyone has for this issue

gpatton · Post by **gpatton** » Tue Aug 30, 2005 12:20 pm

Do you know what the practical maximum size of the xml that you will be reading is ?

NigeGriff · Post by **NigeGriff** » Mon Sep 05, 2005 2:27 am

Hi, instead of using the odbc stage we have used the DRS (dynamic relational stage) which has the needed long character support within, this is a new stage in 7.5. We also had to play about with the column metadata of the longvarchar field, because allowing it to default to 2gb for each column and the way datastage allocates required memory this was causing memory allocation abends/errors - the solution to this was to dynamically allocate the length of the field after selecting the maximum length of the sql server column from the database and plugging this in via a job parameter using a job sequence. Not ideal but it works, a lot of this is documented on developernet in the hint and tips and best practices downloads.

Thanks, Nigel