# Database Discussions > MS SQL Server 7/MS SQL Server 2000 >  How to set server option using T-Sql ("user options")

## Gary Andrews

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

Using T-SQL, how are these set? 

sp_configure &#39;user options&#39;, @configvalue = &#39;value&#39; 
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&#39;t feel BOL is clear on how to set these options. 

TIA   Gary

----------


## Pan

Gary,

You&#39;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 &#34;user options&#34;. User options contains sub-options such as ansi_warnings, ansi_padding, ansi_nulls, arithabort, etc.. 

Using T-SQL, how are these set? 

sp_configure &#39;user options&#39;, @configvalue = &#39;value&#39; 
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&#39;t feel BOL is clear on how to set these options. 

TIA   Gary

----------


## sonaliak

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

----------


## rmiao

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

----------


## sonaliak

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 ?

----------


## rmiao

You can set those options in enterprise manager as well.

----------

