Freigeben über

Error 1934 returned when you run Update Statistics from a job against table that has index on computed column

  • Artikel

When you choose to update statistics in your Maintenance Plan, and you have index on "computed column" in that database, the maintenance plan will fail with the following error: [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE STATISTICS failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'

 

Root Cause: Statistics can be created or updated on tables with computed columns only if the conditions (the following SET options) are such that an index can be created on these columns. All connections using indexes on computed columns or indexed views must have the same settings for these seven options. These six SET options must be set to ON: ANSI_NULLS ANSI_PADDING ANSI_WARNINGS ARITHABORT CONCAT_NULL_YIELDS_NULL QUOTED_IDENTIFIER The NUMERIC_ROUNDABORT option must be set to OFF. There is no way to set this option via the Maintenance plan which uses a separate connection and this is by design. This also happens if you create a simple SQL Agent job and run Update statistics. This is because, when you run a SQL Agent job, by default the SQL Server Agent does not set QUOTED_IDENTIFIER and this is required to be ON if you have an index on a computed column.

To Resolve this issue

1. In SQL Enterprise Manager (SEM), right-click on Jobs and select New Job

2. Name the job, and specify the job owner

3. Click on the Steps tab, and then choose a New step

4. Title the step, and then include the following TSQL commands in the command section:

SET ANSI_NULLS ON SET ANSI_PADDING ON

SET ANSI_WARNINGS ON SET ARITHABORT ON

SET CONCAT_NULL_YIELDS_NULL ON SET QUOTED_IDENTIFIER ON

SET NUMERIC_ROUNDABORT OFF

GO

EXEC sp_MSForeachtable "UPDATE STATISTICS ? WITH FULLSCAN"

5. Click OK to save the steps.

6. Click on the Schedules tab and schedule the job.

7. Save the job by clicking OK 8. Right-click the job and choose Start Now to test it. Related KB article on a similar

problem: Q301292 PRB: SET OPTION Considerations When Running DBCC with Indexes On Computed Columns https://support.microsoft.com/default.aspx?scid=kb;en-us;Q301292

 

Regards

Karthick PK

Comments