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
This question is related to
sql-server
sql-server-2008
database-project
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
I found the solution for this problem:
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.
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.
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]
Source: Stackoverflow.com