cant figure out how to write out to varchar(1024) in sybase

[james garner]

Hi all,

I have to read a SQL server 2000 table which has a varchar field 1024 bytes long and i need to write it to a sybase table which has the same varchar field length (and is in production so i cannot change the field type). what i'm getting is the error "Sybase Server message 5702 (severity 10): The SQL Server is terminating this process." i've notice that it only errors on records where that 1024 byte long field has data. now i can view the data via a DRS stage but it's not letting me export the data via an sybaseocpx stage. i've tried changing the field type ina transform to a longvarchar but it still errors though with a different message:

APT_CombinedOperatorController,0: Fatal Error: Fatal: SQL Server returned error status value -6 from executing 'INSERT INTO APP_WORK.dbo.CTI_CRMS_RULE_CONFIG (CRMS_RULEID,CRMS_RULETYPE,CRMS_RULENAME,RULE_ACTIVE,RULE_ORDER,RULE_RULENAME,RULE_SCRIPT,RULE_COL,RULE_BUILD_DT,RULE_EFF_DT,RULE_TERM_DT ) VALUES (?,?,?,?,?,?,"ParamMarkerKeyw",?,?,?,?)'.

if i limit the field length in the transform to 254 it works..

so the question is, how can i export all of the fields data
?

[ray.wurlod]

Can you lie and specify Long VarChar (1024) data type in your job?

[james garner]

I have done that but i get the 2nd error message. It's odd because i can view the data as varchar(1024) but then in the transform or straight to the export stage it chokes on the field length being greater than 254. when i import it as a longvarchar(1024) then it all works up to the export where i get the 2nd error.

Jim

[ray.wurlod]

The second error is being thrown by SQL Server, not by DataStage. Can you insert these long strings by any other means (for example manually)?

[james garner]

I am told that because of the limitations of the varchar field in sybase 12.5 the original sql developer was filling the field as follows:

sqlfield=sqlfield+"filling the varchar field with 255 chr chunks at a time"

he says that even though you can define a varchar over 255 chrs you can only fill it with 255 chrs at a time..

as a test i changed the target field from varchar(1024) to text() which allows 214meg chars, reimported the schema into DS and i am still getting an error:

APT_CombinedOperatorController,0: Fatal Error: Fatal: SQL Server returned error status value -6 from executing 'INSERT INTO APP_WORK.dbo.CTI_CRMS_RULE_CONFIG (CRMS_RULEID,CRMS_RULETYPE,CRMS_RULENAME,RULE_ACTIVE,RULE_ORDER,RULE_RULENAME,RULE_SCRIPT,RULE_COL,RULE_BUILD_DT,RULE_EFF_DT,RULE_TERM_DT ) VALUES (?,?,?,?,?,?,"ParamMarkerKeyw",?,?,?,?)'. Consult the Transact-SQL User's Guide or SQL Server Reference Manual for a listing of return status values.

i noticed that in the error it puts "ParamMarkerKeyw" in the field at question...

thanks for your time on this one..

Jim