Problem with update tables on linked Sybase server

While working on MsSQL 2000 I'm trying to update table on remote server
(Sybase)
Here is the description of remote table (Sybase)
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 = 202 !!!!!!!
select count(*) from BILLING_ASE12.billing.adm.TransClient where
Transferred=0 = 48 !!!!!!!!
No comments from my side - simply do not understand what is going on
Any answers are appreciated
Hi
0x80004005 = Access Denied. Check that you have permissions on the
destination server for what you are doing.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Viacheslav" <Viacheslav@.discussions.microsoft.com> wrote in message
news:2F06080B-EEC4-4D91-A133-AE0C3E3923B6@.microsoft.com...
> While working on MsSQL 2000 I'm trying to update table on remote server
> (Sybase)
> Here is the description of remote table (Sybase)
> 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 = 202 !!!!!!!
> select count(*) from BILLING_ASE12.billing.adm.TransClient where
> Transferred=0 = 48 !!!!!!!!
> No comments from my side - simply do not understand what is going on
> Any answers are appreciated
|||Surely I have access to this server
Otherwise I couldn't do
update no.2
(update BILLING_ASE12.billing.adm.TransClient
set [Transferred]=1 where CUST_ID=7)
that completed successfully
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> 0x80004005 = Access Denied. Check that you have permissions on the
> destination server for what you are doing.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Viacheslav" <Viacheslav@.discussions.microsoft.com> wrote in message
> news:2F06080B-EEC4-4D91-A133-AE0C3E3923B6@.microsoft.com...
> row
>
>