CODEWITHSUNDEEP
mysqlsql-server-2008odbc
bvandrunen
bvandrunen

Reputation: 443

Error when trying to update via linked server (mssql -> mysql) - Select works

I am having an issue when trying to do an update via a linked server. Error is the following: 

OLE DB provider "MSDASQL" for linked server "**LINKED_SERVER_NAME" returned message "Data provider or other service returned an E_FAIL status.".
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "LINKED_SERVER_NAME".

I have no problem selecting data but any time I try to do an update it fails. My update code:

Update [LINKED_SERVER_NAME]...[Table_Name]
SET post_content = 'alert'
where ID = 5061

This is my select statement which DOES work:

select top 100 * from [LINKED_SERVER_NAME]...[Table_Name] where ID = 5061

I am using:

UPDATE I have tried to use "OPENQUERY" -> this does not work either

Upvotes: 0

Views: 2751

Answers (1)

Russell Hart
Russell Hart

Reputation: 1852

OPENQUERY is not new but it is far more reliable when dealing with non-microsoft linked servers. 

UPDATE OPENQUERY (LINKED_SERVER_NAME, 'SELECT post_content FROM Table_Name WHERE ID = 5061') 
SET post_content = 'alert';
  • Do not use [ ] square brackets in mysql queries; you can use apostrophe instead.
  • Check that you have update permissions on the table.
  • Try running the update directly on a mysql connection. This may be an error not related to linked servers.
  • Is the mysql table a view? It may not be configured correctly as an updatable view. MySQL CREATE VIEW Syntax

If this still doesn't solve it, paste your actual openquery code into the question.

Upvotes: 1

Related Questions