none
How to know Default value of some connection option like XACT_ABORT

    Вопрос

  • Hello all,

    I have a little question.

    You can define de default connection option values like Xact_abort, implicit transaction , .... in the server properties\connection

    How can I know in transactSQL or programming the default value of those options???

    I need to add this kind of information in a external monitoring tool.

    Thanks for your help

    8 июня 2012 г. 13:12

Ответы

Все ответы

  • You can get the current settings for a session in sys.dm_exec_requests.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    8 июня 2012 г. 13:20
  • Thanks, but what I need to know is how is define those options in the server properties\default connection options.

    In the fact, I create a tool to know the setting of sql server before installing an application. I need the max information

    I've found that kind of things

    do you think it's the better way?

    DECLARE @options INT 
    SELECT @options = @@OPTIONS 
    PRINT @options
    IF ( (1 & @options) = 1 ) PRINT 'DISABLE_DEF_CNST_CHK' 
    IF ( (2 & @options) = 2 ) PRINT 'IMPLICIT_TRANSACTIONS' 
    IF ( (4 & @options) = 4 ) PRINT 'CURSOR_CLOSE_ON_COMMIT' 
    IF ( (8 & @options) = 8 ) PRINT 'ANSI_WARNINGS' 
    IF ( (16 & @options) = 16 ) PRINT 'ANSI_PADDING' 
    IF ( (32 & @options) = 32 ) PRINT 'ANSI_NULLS' 
    IF ( (64 & @options) = 64 ) PRINT 'ARITHABORT' 
    IF ( (128 & @options) = 128 ) PRINT 'ARITHIGNORE'
    IF ( (256 & @options) = 256 ) PRINT 'QUOTED_IDENTIFIER' 
    IF ( (512 & @options) = 512 ) PRINT 'NOCOUNT' 
    IF ( (1024 & @options) = 1024 ) PRINT 'ANSI_NULL_DFLT_ON' 
    IF ( (2048 & @options) = 2048 ) PRINT 'ANSI_NULL_DFLT_OFF' 
    IF ( (4096 & @options) = 4096 ) PRINT 'CONCAT_NULL_YIELDS_NULL' 
    IF ( (8192 & @options) = 8192 ) PRINT 'NUMERIC_ROUNDABORT' 
    IF ( (16384 & @options) = 16384 ) PRINT 'XACT_ABORT'
    thanks
    8 июня 2012 г. 13:31
  • select * from sys.databases 

    This is the easy way


    Chuck Pedretti | Magenic – North Region | magenic.com

    • Помечено в качестве ответа Iric WenModerator 18 июня 2012 г. 7:11
    8 июня 2012 г. 13:41
  • @@options gives you the current session options.

    You can set default for some option both on server level and database level. And in the connection string.

    I think a more interesting question is a background on why you need to know this.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Помечено в качестве ответа Iric WenModerator 18 июня 2012 г. 7:11
    8 июня 2012 г. 22:25