Failing to delete record from linked server

Question

I've spent the past two days searching everywhere for a solution to my problem but without any luck.

I have this query that deletes record from a remote server:

delete from OPENROWSET('SQLNCLI', 'Server=AB01S\SQLEXPRESS;Database=ShopData;Trusted_Connection=yes', 'Select receipt_n,action_in, action_ty, action_field_name,action_field_type,action_field_data, terminal from tblData where receipt_n= 1 and terminal = 1');

and I am getting this error:

OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Msg 7202, Level 11, State 1

It also suggests to use sp_addlinkedserver to add a linked server.

Some notes:

1. This happens only on this specific computer + remote server. On 2 different stations (Computer+Server) it worked just fine.
2. Insert to OpenRowSet is working OK.
3. Select * from OpenRowSet is working OK. => Changing the delete to select * works OK.
4. Ad hok is enabled on the computer that queries
5. Remote connection is enabled on the computer being queried via openrowset
6. I can ping the server (AB01S)

So far I have tried adding linked setup via GUI and sp_addlinkserver.

Answer 1

I found the problem!!

The server name (for some reason) was incorrect. @@servername returned AB01S_88

The solution was: running the following code and then restarting the service:

exec sp_dropserver @@servername
exec sp_addserver 'AB01S', local
exec sp_serveroption 'AB01S', 'data access' , 'true'