Unable to update MySQL table from linked server on SQL Server 2014

Question

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?

• Haris Khan: Can you set up a MySQL Router with logging enabled to see what's happening on the other end of this query? I know it's additional work, but It might give you information that you can't see otherwise. dev.mysql.com/doc/mysql-router/8.0/en/…
• Mathieu Guindon: @HarisKhan thanks, I'll ask the MySQL administrator to do this. I only own the SQL Server side of things... life was so simple when everything was only SQL Server!
• Haris Khan: At least they both use the same general syntax! I hope you don't have to throw DynamoDB in there... :)

Answer 1

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

• Mathieu Guindon: I already had this information. If the MSDASQL provider was misconfigured, then the other table wouldn't be updating correctly, would it? "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". The target table does have a primary key, and I'm getting this optimistic concurrency rowset error whether the PK is involved in the update of this table or not.
• Sting: With the linked server providers I've had queries work right out of the gate and others that needed the provider configured. Throughout the years, I've seen extremely little documentation on the linked server provider parameters and why they are essential. So as to your specific case, I'd have to have that exact setup and try it myself. If you're trouble-shooting, I'd at least try them to rule it out if setting them doesn't work.
• Mathieu Guindon: Thanks, the MSDASQL provider is already configured with these settings.
• Sting: Another thing to try, providing the tables aren't too large, is creating SQL Temp tables to bring the data over to the SQL side and doing all your data manipulation in SQL Server. In many cases, I've had a performance increase doing this.

Answer 2

Leaving this for posterity, because no one ever thinks of that.

If everything else looks right, verify that you're not updating a column that automatically gets updated by an update trigger.

Not sure how MySQL implements its triggers, but in my case this is what was making it trip onto itself, and issue a very misleading error message.