Blog Zone

Database error, incorrect settings for 'ANSI_NULLS, QUOTED_IDENTIFIER'

Jul 7

Written by: Tim Wheeler
7/7/2008

Recently I encountered a slight issue with the Import Scripts function on the Visual Studio 2008 database project.

Situation:
I was receiving the following error when executing one of my stored procedures in SQL Server 2005.

INSERT 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 query notifications and/or xml data type methods.

Reason:
The stored procedeure has TSQL syntax that required:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Cause:
This was only occuring after I did a Deploy from the database project in visual studio. When I built the SP, and when I re ran my original SP script manually everything worked fine. It seemed that the database project was altering these settings. What I found is that the ScriptsIgnoredOnImport.sql contained the two SET options that my SP required, and when the database project deployed, it obviously didn't use those SET options.

Fix:
This probably sounds really obvious but here it is:
In the database project select your stored procedure in the solution explorer, bring up the properties windw (F4), and set the ANSI nulls property to On and the Quoted Identifiers property to On. (Stop laughing now ok)

A bug? Well I'm sure MS would say this is a Feature, but it seems that the Import Script function ignores the SET options for the stored procedure imports.

Trackback Print

Location: Blogs

.Net Blog

Tags:

2 comments so far...

Re: Database error, incorrect settings for 'ANSI_NULLS, QUOTED_IDENTIFIER'

christian louboutin--louboutin--christian louboutin--louboutin shoes--christian louboutin boots--lv handbags--mbt shoes--christian louboutin--nike shoes--cheap nike shoes--discount nike shoes--nike shoes sale--Jimmy choo--Jimmy choo shoes--cheap Jimmy choo--discount Jimmy choo--Jimmy choo shoes sale--moncler women--cheap moncler women--discount moncler women--moncler women sale--Manolo Blahnik--Manolo Blahnik shoes--cheap Manolo Blahnik--discount Manolo Blahnik--Manolo Blahnik sale--moncler men--cheap moncler men--discount moncler men--moncler men sale--Balmain Shoes--cheap Balmain Shoes--discount Balmain Shoes--Balmain Shoes sale--moncler kids--cheap moncler kids--discount moncler kids--moncler kids sale--Yves saint Laurent--Yves saint Laurent shoes--cheap Yves saint Laurent--discount Yves saint Laurent--Yves saint Laurent sale--cheap mbt shoes--discount mbt shoes--mbt shoes sale--Ed Hardy--Ed Hardy shoes--cheap Ed Hardy--discount Ed Hardy--Ed Hardy sale--UGG boots--cheap UGG boots--discount UGG boots--UGG boots sale--Alexander McQueen--Alexander McQueen shoes--cheap Alexander McQueen--discount Alexander McQueen--Alexander McQueen sale--Chanel Shoes--cheap Chanel Shoes--discount Chanel Shoes--Chanel Shoes sale--Gucci Shoes--cheap Gucci Shoes--discount Gucci Shoes--Gucci Shoes sale--Tory Burch Shoes--Tory Burch--cheap Tory Burch Shoes--discount Tory Burch Shoes--Tory Burch Shoes sale--Lanvin Shoes--cheap Lanvin Shoes--Lanvin Shoes--Lanvin Shoes sale--Bottega Veneta--Bottega Veneta shoes--cheap Bottega Veneta--discount Bottega Veneta--Bottega Veneta sale--

By d on 4/8/2010

Re: Database error, incorrect settings for 'ANSI_NULLS, QUOTED_IDENTIFIER'

With the conspiracy wow gold world of warcraft goldand revengewow gold world of warcraft gold wow gold kaufen wow gold kaufen wow gold wow gold that unwindsbuy wow gold buy wow gold wow power leveling, the worldwow power leveling wow cd key wow cd key needs a little cheap wow gold cheap wow gold preaching on tolerance.

By dj01 on 8/10/2010

Your name:
Title:
Comment:

Security Code Enter the code shown above in the box below
Add Comment Cancel