Problem with updating remote tables on linked server (Sybase)

Last Post 17 Mar 2005 03:21 AM by Viacheslav. 0 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
Viacheslav
New Member
New Member

--
17 Mar 2005 03:21 AM
While working on MsSQL 2000 I'm trying to update table on remote server (Sybase)

Here is the description of remote table

IF OBJECT_ID('adm.TransClient') IS NOT NULL
drop table adm.TransClient
go
CREATE TABLE adm.TransClient
(
CUST_ID numeric(10,0) NOT NULL,
NAME varchar(254) NOT NULL,
ADR1 varchar(254) NOT NULL,
ADR2 varchar(254) NOT NULL,
CREDIT numeric(17,9) NOT NULL,
SALES varchar(30) NOT NULL,
INN varchar(30) NULL,
TYPEC smallint NOT NULL,
NO_NDS smallint NOT NULL,
NUM_DOG varchar(20) NOT NULL,
LOGIN varchar(16) NOT NULL,
BusinessGroup numeric(10,0) DEFAULT 0 NOT NULL,
TransType varchar(20) DEFAULT "insert" NOT NULL,
TransDate datetime DEFAULT getdate() NOT NULL,
Transferred smallint DEFAULT 0 NOT NULL,
CONSTRAINT PK1
PRIMARY KEY NONCLUSTERED (CUST_ID)
)
LOCK datarows
go

1. Doing like this

update BILLING_ASE12.billing.adm.TransClient
set [Transferred]=1

MsSQL returns:
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: Query-based update failed because the row to update could not be found.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::SetData returned 0x80004005: ].

2. Second atempt

update BILLING_ASE12.billing.adm.TransClient
set [Transferred]=1 where CUST_ID=7

MsSQL returns:
1 row affected


3. Final shoot - I wrote simpliest cursor that goes throw rows and sets Transferred=1
Althogh MsSQL has returned:
-----
Remote tables are not updatable. Updatable keyset-driven cursors on remote tables require a transaction with the REPEATABLE_READ or SERIALIZABLE isolation level spanning the cursor.
----

After that
select count(*) from BILLING_ASE12.billing.adm.TransClient = 300
select count(*) from BILLING_ASE12.billing.adm.TransClient where Transferred=1 = 148 !!!!!!!
select count(*) from BILLING_ASE12.billing.adm.TransClient where Transferred=1 = 152 !!!!!!!!

No comments from my side - simply do not understand what is going on

Any answers are appreciated!


Acceptable Use Policy
---