'Incorrect SET Options' Error When Buildin

问题:

We are using Visual Studio and a database project to generate our database.

I just made a number of database changes (including adding a new table named Correspondence) imported those changes into the database project, and attempted to deploy (rebuild) the database.

When I do, I get the following error:

Creating [dbo].[Correspondence]... Msg 1934, Level 16, State 1, Server (Server Name), Line 1 CREATE TABLE failed because the following SET options have incorrect settings : 'ANSI_WARNINGS, ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

Can anyone explain this error to me, and help me resolve it? Here's the script the database project uses to create this table.

PRINT N'Creating [dbo].[Correspondence]...';
GO

SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
GO

CREATE TABLE [dbo].[Correspondence] (
    [Id]                INT              IDENTITY (1, 1) NOT NULL,
    [WorkbookId]        INT              NOT NULL,
    [ProviderId]        UNIQUEIDENTIFIER NOT NULL,
    [MessageThreadId]   INT              NOT NULL,
    [MessageThreadType] AS               ((1)) PERSISTED NOT NULL
);
GO

SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
GO

PRINT N'Creating PK_Correspondence...';
GO

ALTER TABLE [dbo].[Correspondence]
ADD CONSTRAINT [PK_Correspondence] PRIMARY KEY CLUSTERED ([Id] ASC)
    WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF,
    IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);
GO

回答1:

According to BOL:

Indexed views and indexes on computed columns store results in the database for later reference. The stored results are valid only if all connections referring to the indexed view or indexed computed column can generate the same result set as the connection that created the index.

In order to create a table with a persisted, computed column, the following connection settings must be enabled:

SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET NUMERIC_ROUNDABORT ON
SET QUOTED_IDENTIFIER ON

These values are set on the database level and can be viewed using:

SELECT 
    is_ansi_nulls_on,
    is_ansi_padding_on,
    is_ansi_warnings_on,
    is_arithabort_on,
    is_concat_null_yields_null_on,
    is_numeric_roundabort_on,
    is_quoted_identifier_on
FROM sys.databases

However, the SET options can also be set by the client application connecting to SQL Server.

A perfect example is SQL Server Management Studio which has the default values for SET ANSI_NULLS and SET QUOTED_IDENTIFIER both to ON. This is one of the reasons why I could not initially duplicate the error you posted.

Anyway, to duplicate the error, try this (this will override the SSMS default settings):

SET ANSI_NULLS ON
SET ANSI_PADDING OFF
SET ANSI_WARNINGS OFF
SET ARITHABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON 
SET NUMERIC_ROUNDABORT OFF
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE T1 (
    ID INT NOT NULL,
    TypeVal AS ((1)) PERSISTED NOT NULL
) 

You can fix the test case above by using:

SET ANSI_PADDING ON
SET ANSI_WARNINGS ON

I would recommend tweaking these two settings in your script before the creation of the table and related indexes.

回答2:

I found the solution for this problem:

1. Go to the Server Properties.
2. Select the Connections tab.
3. Check if the ansi_padding option is unchecked.

回答3:

In my case I was trying to create a table from one database to another on MS SQL Server 2012. Right-clicking on a table and selecting Script Table as > DROP And CREATE To > New Query Editor Window, following script was created:

USE [SAMPLECOMPANY]
GO

ALTER TABLE [dbo].[Employees] DROP CONSTRAINT [FK_Employees_Departments]
GO

/****** Object:  Table [dbo].[Employees]    Script Date: 8/24/2016 9:31:15 PM ******/
DROP TABLE [dbo].[Employees]
GO

/****** Object:  Table [dbo].[Employees]    Script Date: 8/24/2016 9:31:15 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Employees](
    [EmployeeId] [int] IDENTITY(1,1) NOT NULL,
    [DepartmentId] [int] NOT NULL,
    [FullName] [varchar](50) NOT NULL,
    [HireDate] [datetime] NULL
 CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED 
(
    [EmployeeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [FK_Employees_Departments] FOREIGN KEY([DepartmentId])
REFERENCES [dbo].[Departments] ([DepartmentID])
GO

ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [FK_Employees_Departments]
GO

However when executing above script it was returning the error:

SELECT failed because the following SET options have incorrect settings: 'ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

The Solution I've found: Enabling the settings on the Top of the script like this:

USE [SAMPLECOMPANY]
GO
/****** Object:  Table [dbo].[Employees]    Script Date: 8/24/2016 9:31:15 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

ALTER TABLE [dbo].[Employees] DROP CONSTRAINT [FK_Employees_Departments]
GO

/****** Object:  Table [dbo].[Employees]    Script Date: 8/24/2016 9:31:15 PM ******/
DROP TABLE [dbo].[Employees]
GO



CREATE TABLE [dbo].[Employees](
    [EmployeeId] [int] IDENTITY(1,1) NOT NULL,
    [DepartmentId] [int] NOT NULL,
    [FullName] [varchar](50) NOT NULL,
    [HireDate] [datetime] NULL
 CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED 
(
    [EmployeeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [FK_Employees_Departments] FOREIGN KEY([DepartmentId])
REFERENCES [dbo].[Departments] ([DepartmentID])
GO

ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [FK_Employees_Departments]
GO

SET ANSI_PADDING OFF
GO

Hope this help.

回答4:

For me, just setting the compatibility level to higher level works fine. To see C.Level :

select compatibility_level from sys.databases where name = [your_database]