August 12, 2009 at 11:19 am
Guys,
Quick Questions...
Where are the values for SET OPTIONS stored in the database for a SP, Func, Trigger, etc? If they are different from the global settings?
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
I know the global settings for a database are stored in sys.databases view. But what about for each Stored Proc or other objects.
USE [SomeDB]
GO
SET ARITHABORT OFF
SET CONCAT_NULL_YIELDS_NULL OFF
SET QUOTED_IDENTIFIER OFF
GO
CREATE usp_SampleProc
AS
BEGIN
-- perform some action
END
thanks,
_UB
August 12, 2009 at 11:57 am
OBJECTPROPERTY has a couple...
SELECT OBJECTPROPERTY(OBJECT_ID('Procedure_Name'), 'ExecIsQuotedIdentOn')
SELECT OBJECTPROPERTY(OBJECT_ID('Procedure_Name'), 'ExecIsAnsiNullsOn')
where are the rest... are they even stored for each Stored Proc.....at all?
August 12, 2009 at 5:08 pm
From what I gathered, looks like the rest are not stored at all, but just inherited. Either from Global database settings or Session settings or inline settings.
hmmmm......
August 13, 2009 at 7:54 am
Most, if not all, of these settings are part of the connection. ADO, ADO.NET, and ODBC all have different defaults so sometimes it depends on the method used to connect to the database.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 13, 2009 at 8:44 am
Exactly, that's the problem I am trying to solve. Different client side have different settings (JDBC, ADO.NET, ODBC... OleDB), so my goal is to try to use the SQL Server required settings when running queries or Procedures.
http://msdn.microsoft.com/en-us/library/ms175088(SQL.90).aspx
The default settings of different client side settings is causing problems with Computed Columns.
thanks for the feedback,
_Ub
Most, if not all, of these settings are part of the connection. ADO, ADO.NET, and ODBC all have different defaults so sometimes it depends on the method used to connect to the database.
August 13, 2009 at 8:52 am
Ah, some of the stuff you can set as attributes in the connection string, otherwise you need add the SET statements to your applications before you run the other commands. I believe that using stored procedures for data access will help with this problem.
What are the problems you are having with Computed Columns? I'd be interested in doing some testing on it.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 13, 2009 at 9:10 am
Thanks. I'll try to give as much info as possible, if you need more, please let me know.
We have Sql Server 2005 server with a database running in 80 compatibility mode (...its a long story). A table like this:
CREATE TABLE dbo.Patient(
ID INT NOT NULL IDENTITY(1,1)
, Name VARCHAR(50) NOT NULL
, Business_ID AS AS 'ABC_' + CONVERT(VARCHAR, ID + 654698) PERSISTED NOT NULL
)
When data is inserted from our application (Java + JDBC driver), the 'Business_ID' cannot be populated. As by default JDBC driver has SET ARITHABORT OFF and SET ANSI_WARNINGS OFF. The required settings are ON and ON for both for the computed results to work.
From BoL: http://msdn.microsoft.com/en-us/library/ms175088(SQL.90).aspx
When the SET options are set incorrectly, one or more of the following can occur:
# The indexed view or computed column cannot be created.
August 13, 2009 at 10:37 am
Prehaps this information From Books On Line, will help you:
SET ARITHABORT OFF (also effect of ANSI_WARNINGS setting)
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/828b357b-2f36-49fe-9f31-8794512c02a3.htm
When a SET statement is executed in a stored procedure, the new setting is active only until the procedure is completed. When the procedure is completed, the connection's setting for that option will go back to what it was before the procedure was executed.
SET QUOTED_IDENTIFIER
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/10f66b71-9241-4a3a-9292-455ae7252565.htm
SET QUOTED_IDENTIFIER is set at parse time. Setting at parse time means that if the SET statement is present in the batch or stored procedure, it takes effect, regardless of whether code execution actually reaches that point; and the SET statement takes effect before any statements are executed.
August 13, 2009 at 2:33 pm
Thanks for the articles... I need to look into this further.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply