setting options to work with indexed view

  • Thread starter Thread starter Zeng
  • Start date Start date
Z

Zeng

Hello, I'm starting to use indexed view for the first time with Standard
Edition and I ran into failure of updating base tables that involved in the
indexed view. I found out what the problem is, basically all my update code
needs to have these options set as below, but I haven't found a way to force
adonet to have them set by default. --Set all these options to
ON:ANSI_NULLSANSI_PADDINGANSI_WARNINGSARITHABORTCONCAT_NULL_YIELDS_NULLQUOTE
D_IDENTIFIER --Set this to OFF:NUMERIC_ROUNDABORTSo my question is1) Is
possible to set them as database defaults and have adonet honors that? If
yes, how?2) If (1) is not possible, is there a way to set them for each
connection to db?Thank you so much!
 
With the exception of ARITHABORT, the default ADO.NET settings are
appropriate for indexed view maintenance. You can turn on ARITHABORT as the
database default with ALTER DATABASE:

ALTER DATABASE MyDatabase
SET ARITHABORT ON

Alternatively, you can specify ARITHABORT ON as the server default by
turning on bit 64 of 'user options' via sp_configure. See the Books Online
for more information.
 
This is what I found on Books Online under Create Procedure:
"SQL Server saves the settings of both SET QUOTED_IDENTIFIER and SET
ANSI_NULLS when a stored procedure is created or altered. These original
settings are used when the stored procedure is executed. Therefore, any
client session settings for SET QUOTED_IDENTIFIER and SET ANSI_NULLS are
ignored during stored procedure execution. SET QUOTED_IDENTIFIER and SET
ANSI_NULLS statements that occur within the stored procedure do not affect
the functionality of the stored procedure."

Is there a fast way (batch edit etc..) for me to turn ON all existing store
procedures for QUOTED_IDENTIFIER and ANSI_NULLS options?

thank you very much,
 
Is there a fast way (batch edit etc..) for me to turn ON all existing
store
procedures for QUOTED_IDENTIFIER and ANSI_NULLS options?
Click to expand...

One method is to script your procs using EM and then search/replace the
setting statements with the desired 'ON' value. Be mindful of these
'sticky' settings when you create or modify stored procedures.
 
Back
Top