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?
