Post reply

WHY heterogeneus queries require the ANSI_NULLS?

  • November 15, 2007 at 2:48 am

    #176841

    Hi All!

    I issue an sp with a select combined of two tables one from the current database and another from a linked server. When I exec the sp within the QA I get the results correctly but when a client tries to run the sp they got "heterogeneus queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection" error right away.

    I drop & create the sp with SET ANSI_NULLS ON and SET ANSI_WARNINGS ON but didn't work. I set the ANSI options on the database at the and of the linked server but didn't work too.

    Any help, or comment of "won't ever work" is appreciated 🙂

  • November 16, 2007 at 5:29 am

    #751785

    Those are settings that the tool the user is accessing the database from must set. They are controlled per connection.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • November 16, 2007 at 6:55 am

    #751818

    Having those settings in the sproc will help with a lot of things... but I think you need to also have those setting turned on for YOUR session when you drop and create the proc.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply