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
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:
However when executing above script it was returning the error:
The Solution I've found: Enabling the settings on the Top of the script like this:
Hope this help.
According to BOL:
In order to create a table with a persisted, computed column, the following connection settings must be enabled:
These values are set on the database level and can be viewed using:
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):
You can fix the test case above by using:
I would recommend tweaking these two settings in your script before the creation of the table and related indexes.
I found the solution for this problem:
For me, just setting the compatibility level to higher level works fine. To see C.Level :