1

I'm trying to execute the following simple statement on my Linked Server (HANA Based) in SQL Server:

UPDATE HANASERVER.."SAP_WORKSHOP"."CUSTOMER"
 SET "CUSTOMER_ID"='3' 
WHERE "CUSTOMER_NAME"='John'

Unfortunately the server retrieve the following error message, denying the transaction:

 The OLE DB provider "MSDASQL" for linked server "HANASERVER" could not UPDATE table "[HANASERVER]..[SAP_WORKSHOP].[CUSTOMER]"

The SELECT * is doable, so the connection is tested and working.

Anyone knows a workaround for this issue?

Thank you, Luigi

EDIT : I forgot to tell that I'm using the server as SYSTEM User, so I literally have the permission for doing everything on it, but unfortunately still not working.

EDIT : I defined the Linked Server following the procedure below :

EXEC sp_addlinkedserver 
@server = 'HANASERVER', --description
@srvproduct = 'HANA_TEST',  --description
@provider = 'MSDASQL',  --Microsoft's OLE DB provider (FIXED NAME)
@datasrc = 'HANA_TEST'  --ODBC System DSN (OUR CONFIGURED SYSTEM DSN)

EXEC sp_addlinkedsrvlogin   
@useself= 'FALSE',  
@rmtsrvname = 'HANASERVER', --description
@locallogin = NULL, 
@rmtuser = 'SYSTEM',    --HANA User
@rmtpassword = 'XXXXXXXX'   --HANA Pswd

EDIT: I'm currently using SSMS on the remote server where the system and the linked server are on. But still can't resolve the issue. I'm trying to exec the query from the query window. :)

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
LuigiVe
  • 17
  • 1
  • 5
  • Why not providing database name? It is not shown in the example here? after "[HANASERVER].." – Kannan Kandasamy Oct 14 '16 at 12:29
  • Hi, I'm following the standard syntax for the query. But even adding the Database name [HANA_TEST] It's still not working. Thank you for the answer anyway. :) – LuigiVe Oct 14 '16 at 12:48
  • Does the user being used for the linked server definition have more that SELECT access to the table your trying to update? – Neo Oct 14 '16 at 13:00

1 Answers1

0

Try using this syntax if you verified that the user specified for the linked server definition has appropriate permissions to UPDATE the table:

update [linked-server].dbname.dbo.tablename
...
where
...

Also try updating as below, specifically the @provider option:

EXEC sp_addlinkedserver     
   @server=N'S1_instance1',   
   @srvproduct=N'',  
   @provider=N'SQLNCLI',   
   @datasrc=N'S1\instance1';

MSDN LINKED SERVER SPEC

UPDATED

Check for TCP/IP and Named Pipes protocols and port. Open SQL Server Configuration Manager and check the SQL Server Network Configuration protocols. You should enable Named Pipes and TCP/IP protocol.

I think I forgot the login piece:

EXEC master.dbo.sp_addlinkedserver @server = N'SQL1', @srvproduct=N'SQL Server'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SQL1',@useself=N'False',@locallogin=NULL,@rmtuser=N'linkeduser',@rmtpassword='########'
Neo
  • 3,309
  • 7
  • 35
  • 44
  • Hi, I did the suggested fix, but now the system won't recognize the schema at all, so it seems not working. But thank you very much for answering :) Greetings, Luigi – LuigiVe Oct 14 '16 at 13:13
  • upvote? Something is not right in regards to your linked server definition I believe. – Neo Oct 14 '16 at 13:14
  • I upvoted, but unfortunately it's not showing. By the way, i defined the Linked Server Following the procedure i posted in the Question (i Edited it),. Thank you for answering :) . Luigi – LuigiVe Oct 14 '16 at 13:19
  • If you use SSMS and log in as SYSTEM, can you update the table from a query window? – Neo Oct 14 '16 at 13:20
  • Well, actually this is exactly the problem. I'm currently using SSMS on the remote server where the system and the linked server are on. But still can't resolve the issue. I'm trying to exec the query from the query window. :) – LuigiVe Oct 14 '16 at 13:23
  • Do you suggest to delete the current LS and rebuild it using this procedure? And what the N stands for? – LuigiVe Oct 14 '16 at 13:30
  • Yes, try recreating the linked server as above. Verify first that your user being used in the definition can update the table. The link I provided in my answer has all the details on the definitions. – Neo Oct 14 '16 at 13:32
  • "Named Pipes Provider: Could not open a connection to SQL Server [53]. " New Error. – LuigiVe Oct 14 '16 at 13:36
  • Is the SQL Browser service is running – Neo Oct 14 '16 at 13:38
  • Yes, it's. Thank you very much for your help anyway, you are really make me feel Positive as well. I Also tryed to change the name of the productionsrv as showed here : [link](http://stackoverflow.com/questions/28995047/sql-server-error-named-pipes-provider-could-not-open-a-connection-to-sql-serve) But still the same issue. ;( – LuigiVe Oct 14 '16 at 13:44
  • see my latest edit, once you add the new linked server, then add the login – Neo Oct 14 '16 at 13:45