Why SQL Server replies differently (errors out) to .NET program compared to SSMS?

Just bumped into a statement that works in SSMS, but produces an error when executed from C#:

create table    dbo.tb_Role
(
    idRole  smallint not null identity( 1, 1 )
        constraint xp_Role primary key clustered,
    sRole   varchar( 16 ) not null,
    s_Role  as lower( sRole )        开发者_开发知识库   -- automatic lower-case
        constraint  xu_Role unique,     -- enforce name uniqueness
    ..
)

Earlier in a similar case I always used a unique index on sRole without an additional computed column. Recently I realized that this approach will allow both 'Admins' and 'admins'. Wanting to do things properly, I added s_Role. But my install engine (written in C#) surprisingly choked on that statement, showing a SqlException:

CREATE TABLE failed because the following SET options have incorrect settings: '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. Could not create constraint. See previous errors.

If I comment out s_Role column (and xu_Role constraint) install executes the script perfectly, so the error is triggered by that column definition.

I have two questions:

1) SSMS is also a .NET application - same as my install engine. Why is the behavior different? Can't use profiler since it's an Express Edition..

If the difference is about default connection properties (specifically, QUOTED_IDENTIFIER), SSMS has the default set to ON (confirmed in Tools|Options|QueryExecution|SQLServer|ANSI), and MSDN says this option is ON by default [for new connections].

I never modify any options in the SqlConnection objects that I use, so what gives?

2) There are no quoted identifiers in that column definition, so what's the complaint about? Yes, I see "..indexes on computed columns", but I just tried wrapping that entire CREATE TABLE in SET QUOTED_IDENTIFIER ON|OFF in SSMS and then flipped them - OFF|ON. Both cases execute in SSMS without any differences or errors! So, even if I explicitly turn if OFF, SSMS executes this CREATE successfully..

The next thing to try is add same wrap into the install script. I'll add results in a jiffy. My environment: VS2010, .NET4 (tho same code would run on 2.0), SQL 2008 Express (sure that same would occur on R2).

If anyone has an explanation, I will very much appreciate sharing!

You misunderstand SET QUOTED_IDENTIFIER ON

This (and other options) should be ON for indexes on computed columns. A unique constraint is an index. It's to do with ANSI standards and predictable behaviours

When you CREATE TABLE do you explicitly run SET QUOTED_IDENTIFIER OFF in SSMS or bvia the menus? You may be seeting it OFF for new connections only.

From .net, run Profiler or execute DBCC USEROPTIONS to see what SET statements are actually issued. You aren't using a DSN or some such are you?

the error message is the key:

CREATE TABLE failed because the following SET options have incorrect settings: '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. Could not create constraint. See previous errors.

when trying to index a computed column, SQL Server is picky about your connection settings.

you are connecting to SQL Server with different settings for SSMS and your .NET program. Their defaults must be different.

try using these settings for both:

SET ANSI_NULLS ON 
SET CURSOR_CLOSE_ON_COMMIT ON 
SET ANSI_NULL_DFLT_ON ON 
SET ANSI_PADDING ON 
SET QUOTED_IDENTIFIER ON 
SET ANSI_WARNINGS ON 
SET ARITHABORT ON 
SET CONCAT_NULL_YIELDS_NULL ON 
SET NUMERIC_ROUNDABORT OFF

Your syntax looks like it's missing a comma and the column name for the unique constraint:

create table    dbo.tb_Role
(
    idRole  smallint not null identity( 1, 1 ) PRIMARY KEY,
    sRole   varchar( 16 ) not null,
    s_Role  as lower( sRole ),           -- automatic lower-case
        constraint  xu_Role unique (s_Role),     -- enforce name uniqueness
    ..
)