How to set server option using T-Sql ("user options")

One of the configuration options that can be set for the server is called "user options". User options contains sub-options such as ansi_warnings, ansi_padding, ansi_nulls, arithabort, etc..

Using T-SQL, how are these set?

sp_configure 'user options', @configvalue = 'value'
go
reconfigue with override
go

i.e. Are the individual option values summed. Say I wanted to set ansi_warnings (whose value is 8) and ansi_nulls (whose value is 32) on and all other user options off. Then is my @configvalue 40?

Sorry, but I don't feel BOL is clear on how to set these options.

TIA Gary

Gary,

You're right that BOL is not clear about it. Your assumption is also right. The question arises why are doing this using T-sql and not EM?



------------
Gary Andrews at 7/31/2002 2:09:59 PM

One of the configuration options that can be set for the server is called "user options". User options contains sub-options such as ansi_warnings, ansi_padding, ansi_nulls, arithabort, etc..

Using T-SQL, how are these set?

sp_configure 'user options', @configvalue = 'value'
go
reconfigue with override
go

i.e. Are the individual option values summed. Say I wanted to set ansi_warnings (whose value is 8) and ansi_nulls (whose value is 32) on and all other user options off. Then is my @configvalue 40?

Sorry, but I don't feel BOL is clear on how to set these options.

TIA Gary

I am creating indexed view for sql server 2000. I need to set these options to on for index view to work. I tried this

Alter Database v52devsq SET arithabort On
Alter Database v52devsq SET ANSI_NULLS On
Alter Database v52devsq SET CONCAT_NULL_YIELDS_NULL On
Alter Database v52devsq SET QUOTED_IDENTIFIER On

But I get errors all over in the application
SQL command executed: EXECUTE pr_ET_delExpense 837, 1177, 6667, 30, 16
DELETE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'. Error Code SQL or AFX: -1
Then I read that one needs to set these at the server level. But after reading BOL or MSDN I still cannot figure out how to do this. I need to use Transact SQL as I will need to put this as a script in the application build.
Any help
Thanks

Just run set commands in query analyzer, check sql books online for syntax. I think you have to set them when create db.

BOL is no help.. they do not have this specified.
I need to set these 4 settings on the server level for index view to work and need it in transact sql.. its not in BOL.
I figured out some part of it...
arithabort is 64
quoted identifier is 256
ansi nulls 32
concat null yields nills 4096
and ansi warnings is 8

this adds up to 4456
So I did this connecting as MASTER
sp_configure 'user options' , 4456
go
RECONFIGURE
go

I got rid of all other errors with arithabort and all EXCEPT

the one with quoted identifier
EXECUTE pr_ET_delExpense 837, 1177, 6667, 30, 16
go

Server: Msg 1934, Level 16, State 1, Procedure pr_et_delExpense, Line 40
DELETE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.

How do I fix this -
I did as above server level setting
I tried doing
Alter Database v52devsq SET QUOTED_IDENTIFIER On

this one
USE master
EXEC sp_dboption 'v52devsq', 'quoted identifier', 'TRUE'

also this one
set QUOTED_IDENTIFIER on
go

EXECUTE pr_ET_delExpense 837, 1177, 6667, 30, 16
go

Non of this is working I keep getting error with
quoted identifier. How do I fix it ?

I created a test indexed view like this -

/*Expenses both project Currency and Base Currency*/
drop index iv_expenses.iv_expenses_idx
go
DROP VIEW iv_expenses
GO
CREATE VIEW iv_expenses WITH SCHEMABINDING
AS
select work_id,Period_ID, sum(EDtl_ProjAmt) as expproj, sum(EDtl_BaseAmt) as expbase, count_big(*) as count
from dbo.ETExpDTL , dbo.mwebwork, dbo.ETGLCMPNY, dbo.ETActivity, dbo.mwebFiscalPeriod
where Work_ID= Act_Impt_ID and Act_ID=EDtl_Activity
and work_entity_type in (3, 4) and Work_GLCompany = GLCo_Company
and EDtl_TranDate between Period_Start and Period_Finish
and Period_Level = 3
GROUP BY work_id, Period_ID
GO

CREATE UNIQUE CLUSTERED INDEX iv_expenses_idx ON iv_expenses (Work_ID, period_id)
GO

Any help ?

You can set those options in enterprise manager as well.