Not able to update data using linked server

[vaidyanathanpc]

Hi,
I am working on the database DB1 and have created a linked server to a database DB2. I have the login and password to access the remote server. The user has access to insert, update and delete data on the remote server. When i connect to the linked server and try to update an existing row I get the following error. What could be the problem??

Server: Msg 7306, Level 16, State 2, Line 1
Could not open table '"PWALLET"."DBO"."PW_TBL_REDEEMPOINTS"' from OLE DB provider 'SQLOLEDB'. The provider could not support a row lookup position. The provider indicates that conflicts occurred with other properties or requirements.
[OLE/DB provider returned message: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.]

The SQL I'm trying to execute is

UPDATE DB2.remoteuser.DBO.tablename
SET column1 = column1 + 200,
UPDATED_DATE = GETDATE()
WHERE colume2 = 2181


Please help.
Regards,
P.C. Vaidyanathan

 

[Rosko]

I think you have have got your identifier mixed up.

Your identifier should be built like this

server.database.owner_name.object_name

I think your DB2 should be in second place as I gather that is the database name.


Good Luck,

Let us know

Rosko

 

[vaidyanathanpc]

Sorry!!! Please read the update stmt as

UPDATE remoteservername.DB2.DBO.tablename
SET column1 = column1 + 200,
UPDATED_DATE = GETDATE()
WHERE colume2 = 2181

Sorry for the confusion...
Also in the error msg please read "pwallet" as DB2 and "pw_tbl_redeempoints" as the remote tablename.

P.C. Vaidyanathan

 

[Rosko]

Perhaps you should watch out for case-sensitivity.

If that does'nt solve it let us know if they are both SQL servers.

Rosko

 

[vaidyanathanpc]

There is no issue with case sensitivity. Both the servers are SQl Server 7.0 versions.

 

[Rosko]

Is your remote server name by any change beginning with a number?

Show me the line in the sysservers table for this particular linked server, perhaps this can shed a light.


Rosko

 

[Rizla]

i have the same sort of problem trying to update a linked oracle server. i get the following error message:

Server: Msg 7357, Level 16, State 2, Line 1

Could not process object 'update wixjda.wrk_piv_sales_ctl set RECORD_COUNT = 1'. The OLE DB provider 'MSDAORA' indicates that the object has no columns.

Although, i can do updates if i link to the same table through MS Access, it asks for a unique record identifier when you select the table and then lets you update fine.

Please help...

 

[Rosko]

Ok
But give me the line from yor master..sysservers table

Maybe this can provide me with some info
Rosko