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

Question

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:

Microsoft SQL Server Management Studio 2008
Trying to connect from MS SQL -> MySQL via ODBC Connector 5.2 (5.1 has same issue)

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

From memory the syntax you are looking for is: UPDATE OPENQUERY(LINKED_SERVER_NAME, 'SELECT post_content FROM Table_Name WHERE ID = 4061') SET post_content = 'alert' - see msdn.microsoft.com/en-us/library/ms188427.aspx — dash
– dash, Commented Oct 3, 2012 at 20:54
Is openquery a new thing. I was successfully able to do this about 2 years ago. I just tried recently which is where I ran into problems. — bvandrunen
– bvandrunen, Commented Oct 3, 2012 at 20:55
I've always had to do it when the linked server was a non-SQL Server instance. Otherwise if the Linked Server is another SQL Server instance, standard ANSI SQL seems (mostly) okay. One other thing to check, I guess, is if the account details the Linked Server have been set up with have UPDATE permissions on the underlying TABLE_NAME. — dash
– dash, Commented Oct 3, 2012 at 20:57

Russell Hart · Accepted Answer · 2012-10-11 22:56:26Z

1

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.

answered Oct 11, 2012 at 22:56

Russell Hart

1,85113 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related