Incorrect SET Options Error When Building Database Project

Question

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

User · Answer

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

User · Answer

In my case  I found that a computed column had been added to the  included columns  of an index   Later  when an item in that table was updated  the merge statement failed with that message  The merge was in a trigger  so this was hard to track down  Removing the computed column from the index fixed it

User · Answer

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

User · Answer

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

User · Answer

I had the same issue with the filtered index and my inserts and updates were failing  All I did was to change the stored procedure that had the insert and update statement to   create procedure abc    AS BEGIN SET NOCOUNT ON SET NUMERIC ROUNDABORT OFF SET CONCAT NULL YIELDS NULL ON  SET ANSI WARNINGS ON  SET ANSI PADDING ON  end

User · Answer

I found the solution for this problem    Go to the Server Properties  Select the Connections tab  Check if the ansi padding option is unchecked

[sql-server] 'Incorrect SET Options' Error When Building Database Project

Examples related to sql-server

Examples related to sql-server-2008

Examples related to database-project