Pipeline Cannot Transfer Data with Identify Field

[Tilden Lau]

I tried to transfer data from one Sybase 11.5 server (Server A) to another
other Sybase 11.5 server (Server B) using PB7 pipeline. Whenever there is
an identity field, I cannot successfully transfer the table. If I specify
the Initial Value as NONE or '0', I will receive the following error message
"Explicit value specified for identity field in table 'amend_master' when
IDENTITY_INSERT is set to OFF". If I specify the Initial Value as EXCLUDE,
I will receive no error message. However the original idenity value will
not be carried forward to Server B and Server B will assign a new identity
now to the field!

Please advise if there is any method for me to transfer data from Server A
to Server B with the identity value unchange and the table in Server B still
carrys the field with identity characteristics.

[Alberto Serrano]

I've just faced this same problem, but with MS SQL Server 6.5.
In order to set explicit values in an identity column, you have to use before:
SET IDENTITY_INSERT table_name ON
INSERT INTO table_name....
UPDATE table_name....
...
SET IDENTITY_INSERT table_name OFF

I don't know how Sybase SQL Server works, but in MS SQL Server you can only have
IDENTITY_INSERT set on only one table at the same time.
If you want to use pipelines, you can't (AFAIK) execute the "SET IDENTITY..."
statement in the database administrator, because it must use the same session
(or transaction, not sure) that the pipeline uses, i.e., you can't go to db
administrator, execute "set identity...", switch back to the pipeline painter,
and then execute the pipeline, it doesn't work. I had to code a little program
that basically does this inside a loop:

pipeline lp_pipe
transaction ltr_source, ltr_dest
...
/* Instantiates objects, connects transactions, etc... */

EXECUTE INMEDIATE "SET IDENTITY_INSERT my_table ON" USING ltr_dest;
lp_pipe.Start(ltr_source, ltr_dest, dw_error)
EXECUTE INMEDIATE "SET IDENTITY_INSERT my_table OFF" USING ltr_dest;

where lp_pipe has assigned a pipeline dataobject that inserts or updates rows in
my_table.

Hope this helps,
Alberto.

[Tilden Lau]

Thanks a lot, Alberto!

Tilden

Alberto Serrano <apse...@bbvnet.com> wrote in message
news:37D53F2F...@bbvnet.com...