Error inserting records into Sybase from SqlServer via Linked Server
-
Hi all,
We have an error ( see below ) when trying to insert into a Sybase Db from Sql Server (QA) via a linked server.;
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Sybase.ASEOLEDBProvider' reported an error.
[OLE/DB provider returned message: [SYBASE][OLE DB Sybase provider]Invalid character value. Error in parameter 4.]
OLE DB error trace [OLE/DB Provider 'Sybase.ASEOLEDBProvider' IRowsetChange::InsertRow returned 0x80004005: ].
The Sql statement is very simple & as follows;
insert into SERVER.database.owner.table_name (col_datetime, col_status, col_step )
values ( '2003-08-15 05:15:04.467', 'COMPLETED', 'STEP1' )
The Sybase table has an Identity column which is also the primary key, not listed in the insert as it's an ID (we have tried with it listed out of frustration, still no joy).
We have tried creating the link server using the 'Sybase ASE OLE DB Provider' & 'Microsoft OLE Provider for ODBC Driver', neither make a difference. We have also tried removing the primary key constraint but that didn't work, so it seems related to the identity column & it's seeding, as if we create the same table structure minus the ID column it works fine
Any suggestions on how to deal with cross Db insertions using tables with ID columns via Linked Servers ???
We are running Win2k sp3 with Sql2k sp3a & trying to insert into a Sybase ASE 12.0 Db.
Thks in advance.
j
-
This was removed by the editor as SPAM
-
Ok, solution found !,
We needed to call 'openquery' to do the insert, the following code is an example of how it was done;
insert openquery(SERVERNAME, 'select field_1,field_2, field_3 from tablename')
values (getdate(),'FINISHED', 'sql')
This appears to work ok, with Sybase generating the Identity value as per any normal insert into the Sybase Db, returning no errors to SqlServer.
Regards.
j
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply