Results 1 to 2 of 2

Thread: ANSI Questions

  1. #1
    Join Date
    Oct 2004
    Posts
    30

    Question Unanswered: ANSI Questions

    I have a 6.5 and 2000 db that allows over flow of data to be entered into the
    fields, but I have another 2000 db that does not allow over flow of data to
    be entered. How can I check the ansi_defaults in the db's? I went through
    the book ol but it didn't explain how to see those values. Only how to set
    it using the SET option. Which was SET ANSI_DEFAULTS off go. Does it set it for the whole db or just my session? I need to
    change it for all logins to be consistant. Thanks

  2. #2
    Join Date
    Dec 2004
    Location
    California, USA
    Posts
    93
    LOOK UP @@Options in BOL - Here's a script that makes use of it.

    begin

    ---------------------------------------------------------------------------
    --- Declarations
    ---------------------------------------------------------------------------

    declare
    @Err Int,
    @RetVal Int,
    @ProcName sysname

    declare @Definitions table (
    BitMask int,
    OptionName nvarchar(25),
    Description nvarchar(500)
    )

    ---------------------------------------------------------------------------
    --- Initialization
    ---------------------------------------------------------------------------

    Set @Err = 0 /* 0 ==> OK */
    Set @ProcName = 'DisplaySetOptions Script'

    insert into @Definitions (
    BitMask, OptionName, Description
    )
    select 1, 'DISABLE_DEF_CNST_CHK', 'Controls interim or deferred constraint checking.'
    union all select 2, 'IMPLICIT_TRANSACTIONS', 'Controls whether a transaction is started implicitly when a statement is executed.'
    union all select 4, 'CURSOR_CLOSE_ON_COMMIT', 'Controls behavior of cursors after a commit operation has been performed.'
    union all select 8, 'ANSI_WARNINGS', 'Controls truncation and NULL in aggregate warnings.'
    union all select 16, 'ANSI_PADDING', 'Controls padding of fixed-length variables.'
    union all select 32, 'ANSI_NULLS', 'Controls NULL handling when using equality operators.'
    union all select 64, 'ARITHABORT', 'Terminates a query when an overflow or divide-by-zero error occurs during query execution.'
    union all select 128, 'ARITHIGNORE', 'Returns NULL when an overflow or divide-by-zero error occurs during a query.'
    union all select 256, 'QUOTED_IDENTIFIER', 'Differentiates between single and double quotation marks when evaluating an expression.'
    union all select 512, 'NOCOUNT', 'Turns off the message returned at the end of each statement that states how many rows were affected.'
    union all select 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.'
    union all select 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.'
    union all select 4096, 'CONCAT_NULL_YIELDS_NULL', 'Returns NULL when concatenating a NULL value with a string.'
    union all select 8192, 'NUMERIC_ROUNDABORT', 'Generates an error when a loss of precision occurs in an expression.'
    union all select 16384, 'XACT_ABORT', 'Rolls back a transaction if a Transact- SQL statement raises a run-time error.'

    ---------------------------------------------------------------------------
    --- Body of Procedure
    ---------------------------------------------------------------------------

    select OptionName,
    case when BitMask & @@Options = BitMask then 'ON' else '---' end Value,
    Description
    from @Definitions
    order by OptionName

    if @@error <> 0
    begin
    set @Err = -1 /* -1 ==> Error: ... */
    goto CommonExit
    end

    ---------------------------------------------------------------------------
    --- Clean-Up and Exit
    ---------------------------------------------------------------------------

    CommonExit:
    print '@Err = ' + cast(@Err as varchar(10))

    end
    go
    Whoever imagines himself a favorite with God holds others in contempt. - Robert Green Ingersoll, lawyer and orator (1833-1899)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •