Unable to update MySQL table from linked server on SQL Server 2014 I'm using SQL Server 2014, with a linked server to a MySQL database on AWS. I need to update rows on that linked server, using data that exists on another linked server. And if that wasn't complicated enough, I need the server and the database names to by parameterized, so I can easily configure my SQL Server Agent job to run against test or prod... so everything is dynamic-SQL.

I tried this:

exec (@remoteSql);


where @remoteSql contains a would-be-valid openquery update statement. This blows up with this error:


  The OLE DB provider "MSDASQL" for linked server "AWS" could not UPDATE table "[MSDASQL]". The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.


So I tried this:

    declare @remoteSql nvarchar(max);
    declare c cursor for select RemoteSql from @updates;
    open c;
    fetch next from c into @remoteSql;
    while @@fetch_status = 0
    begin
        exec (@remoteSql) at AWS;
        fetch next from c into @remoteSql;
    end;
    deallocate c;


where @updates contains a handful of records and @remoteSql contains a perfectly valid and rather simple update statement (no joins, just 3 fields and a where clause) to run on MySQL. This blows up with this ever so helpful error message:


  Could not execute statement on remote server 'AWS'.


I get the same error if I take the statement, escape the double-quotes and go exec ('update ... where ...') at AWS;, which worked here.

I have the Return matched rows instead of affected rows checkbox checked on my ODBC data source, and quadruple-checked that my linked server has RPC enabled.

The same stored procedure has a rather complex dynamic-SQL update statement involving a join to a local table and a join to another remote table through openquery, and that one works perfectly - however that same approach fails for this table.

Why is it refusing to update that remote table (the login I'm using does have the necessary permissions), and what else can I try?
Configure the MSDASQL provider

I believe the following should fix it. Turn on the following for SQL Servers MSDASQL provider:

AllowInProcess
IndexAsAccessPath
LevelZeroOnly
DynamicParameters
NestedQueries


Other options that seemed to work was creating a primary key:

https://www.sqlservercentral.com/Forums/657910/OPENQUERY-UPDATE-problem-with-MySQL-as-linked-server