December 5, 2005 at 11:38 am
I can use sp_configure to find out the server properties of a SQL instance, but what I can't find out by using this is the connection settings as displayed in the server properties dialogue on the connections tab.
Now when I did a trace I see that on ticking an option on the tab results in an entry into sysconfigures system table. The problem I have with this is that only the last value changed is stored in this table, the comment field that relates to this entry has 'user options' in it.
Does anybody know how I can get this information without having to bring up the sql server properties dialogue. What I want to do is be able to get the info and compare it against an earlier entry and fire an alert if there has been a change.
Thanks in advance.
December 5, 2005 at 11:58 am
Although not recommended, u can create a trigger on sysconfigures. User options are based on connection.
try looking into the documentation for
DBCC USEROPTIONS
Use the user options option to specify global defaults for all users. A list of default query processing options is established for the duration of a user's work session. user options allows you to change the default values of the SET options (if the server's default settings are not appropriate). A user can override these defaults by using the SET statement. You can configure user options dynamically for new logins. After you change the setting of user options, new logins use the new setting; current logins are not affected.
| Value | Configuration | Description |
|---|---|---|
| 1 | DISABLE_DEF_CNST_CHK | Controls interim or deferred constraint checking. |
| 2 | IMPLICIT_TRANSACTIONS | Controls whether a transaction is started implicitly when a statement is executed. |
| 4 | CURSOR_CLOSE_ON_COMMIT | Controls behavior of cursors after a commit operation has been performed. |
| 8 | ANSI_WARNINGS | Controls truncation and NULL in aggregate warnings. |
| 16 | ANSI_PADDING | Controls padding of fixed-length variables. |
| 32 | ANSI_NULLS | Controls NULL handling when using equality operators. |
| 64 | ARITHABORT | Terminates a query when an overflow or divide-by-zero error occurs during query execution. |
| 128 | ARITHIGNORE | Returns NULL when an overflow or divide-by-zero error occurs during a query. |
| 256 | QUOTED_IDENTIFIER | Differentiates between single and double quotation marks when evaluating an expression. |
| 512 | NOCOUNT | Turns off the message returned at the end of each statement that states how many rows were affected. |
| 1024 | ANSI_NULL_DFLT_ON | Alters the session's behavior to use ANSI compatibility for nullability. New columns defined without explicit nullability are defined to allow nulls. |
| 2048 | ANSI_NULL_DFLT_OFF | Alters the session's behavior not to use ANSI compatibility for nullability. New columns defined without explicit nullability are defined not to allow nulls. |
| 4096 | CONCAT_NULL_YIELDS_NULL | Returns NULL when concatenating a NULL value with a string. |
| 8192 | NUMERIC_ROUNDABORT | Generates an error when a loss of precision occurs in an expression. |
| 16384 | XACT_ABORT | Rolls back a transaction if a Transact- SQL statement raises a run-time error. |
Select @@OPTIONS
The bit positions in user options are identical to those in @@OPTIONS. BUT Each connection has its own @@OPTIONS function, which represents the configuration environment. When logging in to Microsoft® SQL Server™, a user receives a default environment that assigns the current user options value to the @@OPTIONS. Executing SET statements for user options affects the corresponding value in the session's @@OPTIONS.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply