January 11, 2006 at 3:55 am
Hi,
From the BOL, if set ansi_defaults is on, then SET IMPLICIT_TRANSACTIONS
is ON. However, it also mentioned that MS Ole DB provider for SQL server & ODBC will turn it off when connected.
Therefore, when I am using Query analyzer to run my SP, is SET IMPLICIIT_TRANSACTIONS ON ?
On the other hand, using 'DBCC useroptions' in QA, show that SET IMPLICIT_TRANSACTIONS is ON.
How about my web and windows application when connecting to SQL2k SP4 ?
It is because some of my SP show lot of tables locking in the enterprise manager -> management -> Currenty Activity -> blocking/object.
Initially, my SP running fine and fast like less than a minute. However,
sequence running again, my SP will take 2-3 minutes.
Is above command causing my SP slowness. Please advise and comment. thank you
January 16, 2006 at 8:00 am
This was removed by the editor as SPAM
January 16, 2006 at 10:18 am
At which keyword have you seen that implicit transaction is linked to
ANSI_NULL_DEFAULT ?
SQL options control ANSI compliance options.
ANSI_NULL_DEFAULT
Allows the user to control the database default nullability. When NULL or NOT NULL is not specified explicitly, a user-defined data type or a column definition uses the default setting for nullability. Nullability is determined by session and database settings. Microsoft SQL Server™2000 defaults to NOT NULL. For ANSI compatibility, setting the database option ANSI_NULL_DEFAULT to ON changes the database default to NULL.
When this option is set to ON, all user-defined data types or columns that are not explicitly defined as NOT NULL during a CREATE TABLE or ALTER TABLE statement default to allowing null values. Columns that are defined with constraints follow constraint rules regardless of this setting.
Connection-level settings (set using the SET statement) override the default database-level setting for ANSI_NULL_DEFAULT. By default, ODBC and OLE DB clients issue a connection-level SET statement setting ANSI_NULL_DEFAULT to ON for the session when connecting to SQL Server. For more information, see SET ANSI_NULL_DFLT_ON.
The status of this option can be determined by examining the IsAnsiNullDefault property of the DATABASEPROPERTYEX function.
This only affects creating objects
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply