Quoted identifier change in 3.0.3

[asills]

I haven't messed with it, but if you could guarantee no one would be using Vault ( or you could temporarily bring down IIS / Vault server ), you could edit your maintenance plan script to turn QUOTED_IDENTIFIER OFF, run the maintenance, and set QUOTED_IDENTIFIER back on.

In other words, Vault requires the QUOTED_IDENTIFIER to be ON, the DBCC commands require QUOTED_IDENTIFIER to be ON, and the Maintenance Plan is hard-coded to set QUOTED_IDENTIFIER OFF, therby producing the maintenance plan failure.

So your first comment is wrong and OFF should be ON and ON should be OFF. In that regard, would it then just be possible to open the job, find the T-SQL step and before calling the maintenance extended stored procedure and set the value to ON? Or does the maintenance extended stored proc establish a new connection and thus reset the connection options?

[jclausius]

So your first comment is wrong and OFF should be ON and ON should be OFF. In that regard, would it then just be possible to open the job, find the T-SQL step and before calling the maintenance extended stored procedure and set the value to ON?

No, the first comment is not wrong. I thought perhaps the maintenance plan was failing itself because QI was turned ON.

Further research indicates that this is not the case. The actual problem is within the maintenance plan's extended stored procedure itself. There is no setting you can use to convince the extended stored proc to use a different QI setting.

Or does the maintenance extended stored proc establish a new connection and thus reset the connection options?

Yes. However, I don't know if the maintenance plan turns the QI option off permanently, or if the changed option only lasts during the run of the plan.

[asills]

Okay, well FYI the second sql script here:
http://support.sourcegear.com/viewtopic.php?t=2924

Gets an error in query analyzer (the cursor has 2 fields in it but the FETCH only sets one variable).

[jclausius]

Thanks for the report.

I've updated the KB article.

[ggonzalez]

Note that in SQL Server 2000 SP4 there is a new flag that can be added to an existing maintenance plan "integrity checks" or "optimizations" job step that will eliminate this error (-SupportComputedColumn). We were having this same problem, and this fixed it without requiring a separate maint plan as described above.

http://support.microsoft.com/kb/902388/

Greg Gonzalez
sqlSentry