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:
@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;
@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?