Home » Visual StudioRSS

How to: SET QUOTED_IDENTIFIER ON and ANSI_NULLS on for a Primary Key file under Keys in a 2008 DB pr

GO
PRINT N'Creating [dbo].[SalesRevenueMonthlyBookings]...';
GO
SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
GO

GO
CREATETABLE [dbo].[SalesRevenueMonthlyBookings] (
  [EntryDate]        SMALLDATETIMENOTNULL,
  [PlacementID]       INTNOTNULL,  
  [TranType]        INTNOTNULL,
  [ConfirmedBy]       INTNULL,
  [ConfirmedDate]      DATETIMENULL,  
  [RID]           INTIDENTITY (1, 1) NOTNULL,
  [SubTranType]       AS      (CASEWHEN [TranType] = (0)
                            OR [TranType] = (1) THEN [TranType] ELSE [RID] END) PERSISTED NOTNULL,
  [TrafficMonth]      SMALLDATETIMENULL
);


GO
SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;


GO
PRINT N'Creating PK_SalesRevenueMonthlyBookings_EntryDate_PlacementID...';


GO
ALTERTABLE [dbo].[SalesRevenueMonthlyBookings]
  ADDCONSTRAINT [PK_SalesRevenueMonthlyBookings_EntryDate_PlacementID] PRIMARYKEYCLUSTERED 
  ([EntryDate] ASC, [PlacementID] ASC, [TranType] ASC, [SubTranType] ASC) 
  WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO

I am trying to deploy a SQL 2008 DB project in VSTE 2010. The above is the code snippet that the deploy utility generates...I get the following error when the SQL executes...I tried going under the DB project->Schema Objects->Tables->Keys->PK_SalesRevenueDailyForecastByBooking_EntryDate_PlacementID.pkey.sql AND changed both the properties ANSI_NULLS to ON and QUOTED_IDENTIFIER to ON. The script that is generated by clicking deploy still ignores those hints...

Is there a way to force these settings when the deploy utility generates the alter table statement based on the primary key in the project?

 

 I get the following error when the deploy script runs:

Msg 1934, Level 16, State 1, Line 1

ALTER TABLE failed because the following SET options have incorrect settings: 'ANSI_NULLS, QUOTED_IDENTIFIER'. 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.

Msg 1750, Level 16, State 0, Line 1

Could not create constraint. See previous errors.

 

10 Answers Found

 

Answer 1

Hello,

Those settings can only be applied on the project level. You can set  them through project properties.

The set statement in the script will be ignored and be overwritten by project properties. Hope it helps.

 

Answer 2

The sample script IS what TFS generates. I'll try to explain it a little better. We imported an existing database into a DB project in VSTE. One of the tables had a computed column, which was also part of the index. The table, if scripted in SSMS, is like this:

SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
GO
CREATE TABLE [dbo].[SalesRevenueMonthlyBookings] (
  [EntryDate]        SMALLDATETIME NOT NULL,
  [PlacementID]       INT      NOT NULL, 
  [TranType]        INT      NOT NULL,
  [ConfirmedBy]       INT      NULL,
  [ConfirmedDate]      DATETIME   NULL, 
  [RID]           INT      IDENTITY (1, 1) NOT NULL,
  [SubTranType]       AS      (CASE WHEN [TranType] = (0)
                            OR [TranType] = (1) THEN [TranType] ELSE [RID] END) PERSISTED NOT NULL,
  [TrafficMonth]      SMALLDATETIME NULL,
CONSTRAINT [PK_SalesRevenueMonthlyBookings_EntryDate_PlacementID] PRIMARY KEY CLUSTERED
(
[EntryDate] ASC,
[PlacementID] ASC,
[TranType] ASC,
[SubTranType] 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


When the table is imported into VSTE DB project, the Table stored in the table section, while the PK is available under DB project->Schema Objects->Tables->Keys. When the project is deployed, the script generated by the deploy utility is like this:

GO
PRINT N'Creating [dbo].[SalesRevenueMonthlyBookings]...';
GO
SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
GO

GO
CREATE TABLE [dbo].[SalesRevenueMonthlyBookings] (
  [EntryDate]        SMALLDATETIME NOT NULL,
  [PlacementID]       INT      NOT NULL, 
  [TranType]        INT      NOT NULL,
  [ConfirmedBy]       INT      NULL,
  [ConfirmedDate]      DATETIME   NULL, 
  [RID]           INT      IDENTITY (1, 1) NOT NULL,
  [SubTranType]       AS      (CASE WHEN [TranType] = (0)
                            OR [TranType] = (1) THEN [TranType] ELSE [RID] END) PERSISTED NOT NULL,
  [TrafficMonth]      SMALLDATETIME NULL
);

GO
SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
GO
PRINT N'Creating PK_SalesRevenueMonthlyBookings_EntryDate_PlacementID...';
GO
ALTER TABLE [dbo].[SalesRevenueMonthlyBookings]
  ADD CONSTRAINT [PK_SalesRevenueMonthlyBookings_EntryDate_PlacementID] PRIMARY KEY CLUSTERED
  ([EntryDate] ASC, [PlacementID] ASC, [TranType] ASC, [SubTranType] ASC)
  WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO

As a result, the script fails because the ANSI null is set  to OFF before the alter table command.

 

 

I need a way to force the ANSI NULL setting to be ON at the Keys level of granularity after the table is imported into the project. Apparently, there is a property on the object under Keys that I have flipped to allow ANSI NULLS ON, but it seems to be ignored when the deploy script is generated. Even turning ANSI NULLS ON at the project level also does not help as the deploy script still has ANSI NULLS OFF right before it has the Alter table command....

Am I missing something?

 

 

Answer 3

Let me ask a couple of quick questions:

1) In Solution Explorer, right-click the file  that corresponds to the primary  key and then click Properties. What values appear in the Properties window for ANSI Nulls and Quoted Identifiers properties? By default, these end up set  to "Project Default", I believe.

2) If you change them to your desired setting and then re-deploy, do you get what you expect?

The following help topic talks about this - http://msdn.microsoft.com/en-us/library/aa980441.aspx 

The topic doesn't see much use, so I'm wondering if the name of it isn't clear. If you can suggest a better title, let me know :)

 

 

Answer 4

Steven,

Thanks for your response. I have already tried the step that you mention without any luck. In the primary  key file  properties both ANSI nulls and Quoted Identifers are set  to ON in my solution.

When I deploy my project, this is the error I get

C:\TFS\Vibrant Databases\VMDBs-ImportSetup\LiveData_Stats2\sql\debug\LiveData_Stats2.sql(162,0): Error SQL01268: .Net SqlClient Data Provider: Msg 1934, Level 16, State 1, Line 1 ALTER TABLE failed because the following SET options have incorrect settings: 'ANSI_NULLS, QUOTED_IDENTIFIER'. 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.

It seems like those settings at the object level are ignored. Problem is we do not want to Turn ON ANSI_NULLS at the project level for the entire database.

This is the part of the script that is generated as the deploy action:

PRINT N'Creating PK_SalesRevenueMonthlyBookings_EntryDate_PlacementID...';

GO
ALTER TABLE [dbo].[SalesRevenueMonthlyBookings]
    ADD CONSTRAINT [PK_SalesRevenueMonthlyBookings_EntryDate_PlacementID] PRIMARY key  CLUSTERED ([EntryDate] ASC, [PlacementID] ASC, [TranType] ASC, [SubTranType] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);

GO 

Any thoughts?

 

Answer 5

Hmmn, very strange. Let me see if I can reproduce the problem and if so, I'll yell at the product team ;)
 

Answer 6

Okay, I think that I've identified the problem. The deploy operation is just returning an error from SQL Server because of a requirement when you apply an index to a computer column (in your case, SubTransType).

Referring to the SQL Server docs (http://msdn.microsoft.com/en-us/library/ms189292.aspx), we see that SQL Server has specific requirements when you apply an index on a computed column. Here is the section on set  options:

SET Option Requirements

The ANSI_NULLS connection-level option must be set to ON when the CREATE TABLE or ALTER TABLE statement that defines the computed column is executed. The OBJECTPROPERTY function reports whether the option is on through the IsAnsiNullsOn property.

The connection on which the index is created, and all connections trying INSERT, UPDATE, or DELETE statements that will change values in the index, must have six SET options set to ON and one option set to OFF. The optimizer ignores an index on a computed column for any SELECT statement executed by a connection that does not have these same option settings.

The NUMERIC_ROUNDABORT option must be set to OFF, and the following options must be set to ON:

ANSI_NULLS

ANSI_PADDING

ANSI_WARNINGS

ARITHABORT

CONCAT_NULL_YIELDS_NULL

QUOTED_IDENTIFIER

Setting ANSI_WARNINGS to ON implicitly sets ARITHABORT to ON when the database compatibility level is set to 90. If the database compatibility level is set to 80 or earlier, the ARITHABORT option must explicitly be set to ON. For more information, see SET Options That Affect Results.

So, the tool is just telling you that you're trying to do something that isn't allowed if you want to apply an index to a computed column. You must have ANSI NULLs and QUOTED_IDENTIFIER on for the Index that you are creating.

Does that help?

 

 

Answer 7

Steven,

Thank you for identifying the problem. It does help me understand the reasons better now. I do realize that with the index on a computed column, I must have ANSI NULLs and  QUOTED_IDENTIFIER ON for the Index when I create one. The table was created properly in an existing DB. I imported that DB in my DB project and the project splits up the table and the key  in two separate sections in the solution explorer. For the PK file, I have already set  the object level property to have both those settings ON. It seems though that when Visual Studio creates the deploy script, it ignores the settings done at the Object level from the Primary Key file. The problem is that how do I force that operation to occur when I deploy my project. In other words, it is not creating a command like "SET ANSI_NULLS, QUOTED_IDENTIFIER ON;" from those Property settings before it creates the alter table command in the deploy script (for creating the Primary Key). The resulting deploy script ends up looking like this...

GO
PRINT N'Creating [dbo].[SalesRevenueMonthlyBookings]...';
GO
SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
GO
CREATE TABLE [dbo].[SalesRevenueMonthlyBookings] (
  [EntryDate]        SMALLDATETIME NOT NULL,
  [PlacementID]       INT      NOT NULL, 
  [TranType]        INT      NOT NULL,
  [ConfirmedBy]       INT      NULL,
  [ConfirmedDate]      DATETIME   NULL, 
  [RID]           INT      IDENTITY (1, 1) NOT NULL,
  [SubTranType]       AS      (CASE WHEN [TranType] = (0)
                            OR [TranType] = (1) THEN [TranType] ELSE [RID] END) PERSISTED NOT NULL,
  [TrafficMonth]      SMALLDATETIME NULL
);
GO
SET ANSI_NULLS, QUOTED_IDENTIFIER OFF;
GO
PRINT N'Creating PK_SalesRevenueMonthlyBookings_EntryDate_PlacementID...';
GO
ALTER TABLE [dbo].[SalesRevenueMonthlyBookings]
  ADD CONSTRAINT [PK_SalesRevenueMonthlyBookings_EntryDate_PlacementID] PRIMARY KEY CLUSTERED
  ([EntryDate] ASC, [PlacementID] ASC, [TranType] ASC, [SubTranType] ASC)
  WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);
GO

I hope I understood your response correctly. Am I missing something?

 

Answer 8

I think you understood me. Let me see what my resulting deployment scrpit looks like after I fix my settings locally. Hmmn, even when they are set  correctly, the error still occurs. This is looking like a product bug. Let me ping the product team and see what they have to say.

thanks for your patience in walking through the issue with me.

 

 

Answer 9

And I have confirmation that the issue is indeed a product bug.

The PM tells me: The issue is that these file  settings are not honored for all objects when defined on files.  Particularly indexes and constraints.

 You can work around the problem in one of two ways:

1.       Define Primary Key constraint inline on table and set  ANSI NULLs and Quoted Identifiers on the file for the table.

 

CREATE TABLE [dbo].[Table1]
(
  column_1 int NOT NULL, 
  column_2 int NOT NULL,
  column_3 AS column_1 + column_2 PERSISTED NOT NULL CONSTRAINT PKTable1_column3 PRIMARY KEY,
  column_4 int NOT NULL
)
GO

 

 

2.       Move to constraint to post deployment script

Because, in your scenario, you're dealing with a multi-column index that includes a computed column, I think you'll have to go with option #2. It's not an ideal solution, but it can work around the bug.

 

 

Answer 10

Thanks Steven for confirming that this is a bug. I was beginning to think that it was, but wanted to make sure I wasn't missing to set  some setting somewhere.

Post deployment script seems to be the most likely option we will have to go with.

Thanks again for all your help.

 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter