Problem with Heterogeneous Services - Inserts and Updates to SQL Server 2K

Hello,

We are trying to connect our Oracle 9i on Windows 2K to a SQL Server 2K box. I can get the connection to work and I can get a (SELECT * from) query to work.

The problem I am getting is when I try to do a simple insert or update on a table that resides in the SQL Server box from the Oracle box. Selects work, Inserts and Updates do not.

Does anyone know if I can do inserts or updates via HS to SQL Server and if so are there some tricks that I need to do?

Here is the makeup of my SQL Server table (Does the TableName need to be capitalized?):
CREATE TABLE [dbo].[qcaStarsStagingBOL] (

[BOL] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PRODUCTID] [int] NOT NULL ,
[SOURCECOMPANYID] [int] NOT NULL ,
[CUSTOMERID] [int] NOT NULL

) ON [PRIMARY] Here is the query I run from the Oracle Box

Insert into QCASTARSSTAGINGBOL_at_HO.WORLD (BOL, PRODUCTID, SOURCECOMPANYID, CUSTOMERID)
values ('xxoo',1,1,1);

Here is the result I get

Insert into QCASTARSSTAGINGBOL_at_HO.WORLD (BOL, PRODUCTID, SOURCECOMPANYID, CUSTOMERID)
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Generic Connectivity Using OLEDB_FS]DRV_ModifyRow: (IRowsetChange::InsertRow:rc=-2147467259):Extended = Empty row cannot be inserted. Row must have at
least one column value set.
ORA-02063: preceding 2 lines from HO.WORLD

There is already data in the table, there are no defaults or constraints or triggers, the query (minus the @Ho.world) works from SQL Server.

Any help would be greatly appreciated.

Jamie. Received on Wed May 21 2003 - 23:18:02 CDT