I have a procedure that pushes data from my local SQL Server 2005 database into a table in an Azure DB which is set up as a linked server. The basic data flow is create a #staging table, fill the #staging table with the new data, update existing rows in the Azure table, insert new rows in the Azure table. The error that is returned is "Cursor plan failed because it is not possible to force the plan for a cursor of type other than FAST_FORWARD or STATIC with a USE PLAN hint. Consider removing USE PLAN hint and updating statistics or using different hints to influence query plan choice." however I am not using a cursor in the procedure. I've narrowed the section of code down to the UPDATE portion below.
UPDATE T1
SET T1.Col5 = T2.Col5
FROM
Azure.database.dbo.tablename T1 INNER JOIN #staging T2
ON T1.[col1] = T2.[col1] COLLATE Latin1_General_CI_AS
AND T1.[col2] = T2.[col2] COLLATE Latin1_General_CI_AS
AND T1.[col3] = T2.[col3] COLLATE Latin1_General_CI_AS
AND T1.[col4] = T2.[col4]
WHERE T1.[col5] <> T2.[col5]
I'm using the same workflow to maintain a few other tables without issue so I'm not sure what the problem could be. I tried rebuilding statistics on the destination table and recompiling the procedure with no success. Any help would be appreciated.
I tried dropping the table on the Azure side and re-creating it in the hopes that anything cached would be cleared out. Ran the procedure and succeeded but only Inserts were required since the new table was empty. Ran a second time and it failed on the Update section with the same error.
Re-wrote the procedure and replaced Update section with Delete from Azure where key fields found in #staging then Insert from #staging. Working but certainly not ideal.
