-
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
-
How to set server option using T-Sql ("user options") (reply)
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 need help
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
Last edited by sonaliak; 03-08-2004 at 11:36 AM.
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|