Results 1 to 2 of 2

Thread: Ansi Nulls

  1. #1
    Join Date
    Aug 2002
    Posts
    7

    Red face Unanswered: Ansi Nulls

    I am get the following erro message when accessing the linked server table.
    Any Suggetions ??

    "HETROGENOUS QUERIES REQUIRE THE ANSI NULLS ON AND ASI WARNINGS TO BE SET FOR THE CONNETION. THIS ENSURES CONSISTENT QUERY SEMANTICS. ENABLE THESE OPTIONS AND THEN USE THE YOUR QUERY"

    If Enable what go will go wrong because these are production servers..

    Raj

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    essentially you need to issue two statments:

    set ANSI_NULLS on
    set ANSI_WARNINGS on

    I doubt setting either of these to on will give you any problems with the possability of NULL comparisons, see below.

    as for what will go wrong, from Books Online:

    ANSI_NULLS

    When set to ON, all comparisons to a null value evaluate to NULL (unknown). When set to OFF, comparisons of non-Unicode values to a null value evaluate to TRUE if both values are NULL. By default, the ANSI_NULLS database option is OFF.

    Connection-level settings (set using the SET statement) override the default database setting for ANSI_NULLS. By default, ODBC and OLE DB clients issue a connection-level SET statement setting ANSI_NULLS to ON for the session when connecting to SQL Server. For more information, see SET ANSI_NULLS.

    SET ANSI_NULLS also must be set to ON when you create or manipulate indexes on computed columns or indexed views.

    The status of this option can be determined by examining the IsAnsiNullsEnabled property of the DATABASEPROPERTYEX function.

    and

    ANSI_WARNINGS

    When set to ON, errors or warnings are issued when conditions such as "divide by zero" occur or null values appear in aggregate functions. When set to OFF, no warnings are raised when null values appear in aggregate functions, and null values are returned when conditions such as "divide by zero" occur. By default, ANSI_WARNINGS is OFF.

    SET ANSI_WARNINGS must be set to ON when you create or manipulate indexes on computed columns or indexed views.

    Connection-level settings (set using the SET statement) override the default database setting for ANSI_WARNINGS. By default, ODBC and OLE DB clients issue a connection-level SET statement setting ANSI_WARNINGS to ON for the session when connecting to SQL Server. For more information, see SET ANSI_WARNINGS.

    The status of this option can be determined by examining the IsAnsiWarningsEnabled property of the DATABASEPROPERTYEX function.
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

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