SQL Agent - Could not complete cursor operation because the set options have changed since the cursor was declared. [SQLSTATE 42000] (Error 16958).

Some how I've had this error before.  SQL Agent maintenance job with dynamic SQL in the job step fails with the above error. 

I've had this on sql 2005 and sql 2008; for the most part I thought I'd never see this error again, but alas it keeps coming back.  This time the solution was to just remove the cursor from the dynamic SQL and use a temporary table.  Solutions in the past have been SP4 for sql 2005, sp2 for sql 2008; working on getting the set options right, all these are valid solutions, but none of them worked.  So this is what I had to do.

In particular this script reindexes all user databases on a sql 2008 instance, where the compatability mode of the database is in 9.0.  We have a different step in the maintenance job for databases that are in 10.0 mode.

Fun.

StartReindex_90_compatability.txt (4.17 KB)