Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2007
    Posts
    3

    Unanswered: Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for

    I have a SQL200 stored proc that gives me the error "Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query." when I try to execute it through the query analyzer.

    I was able to create the stored proc fine but when I try to execute it through the query analyzer it gives me the above error. I do have Link Server select inside the stored proc. I have to turn of warnings inside the stored proc in order for it to not crash my vb6 recordset by putting in the SET ANSI_WARNINGS OFF
    SET NOCOUNT OFF
    SET ANSI_NULLS OFF
    or else my vb6 recordset crashes.

    When I created the sproc, I did what every one was telling me to do in the forums by putting in the

    SET ANSI_WARNINGS ON
    Go
    SET NOCOUNT ON
    GO
    SET ANSI_NULLS ON
    GO

    CREATE Procedure usp_SprocName


    AS
    SET ANSI_WARNINGS OFF
    SET NOCOUNT OFF
    SET ANSI_NULLS OFF

    Can someone help me?

  2. #2
    Join Date
    May 2002
    Location
    General Deheza, Cba, Arg.
    Posts
    276
    this is a confirmed bug of sql2k. do u read this link?
    http://support.microsoft.com/kb/296769/en-us

  3. #3
    Join Date
    Nov 2007
    Posts
    3
    I am not using the Enterprise Manager like the artical says. I am using the Query Analyser. I don't have a problem creating the stored proc. I only get the error when I try to execute the stored proc.

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    I assume this to be the code that you are using to create the stored procedure.

    SET ANSI_WARNINGS ON
    Go
    SET NOCOUNT ON
    GO
    SET ANSI_NULLS ON
    GO

    CREATE Procedure usp_SprocName


    AS
    SET ANSI_WARNINGS OFF
    SET NOCOUNT OFF
    SET ANSI_NULLS OFF
    OK. But why haven't you done what was mentioned in the Microsoft Support Article? That would seem like the only logical step to me.

    The error occurs because, in order to execute this type of query, the stored procedure definition must contain the definitions mentioned in the article. Your stored procedure sets the relative option to 'ON' but then immediately afterwards sets it to OFF. Try reading your stored procedure definition again....

    Regards,
    Last edited by r123456; 11-18-07 at 04:18.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    Nov 2007
    Posts
    3
    The reason for turning the warnings off immediately is because the stored proc. is executed in VB6 to populate a record set object. If after the creation of sproc, NSI_WARNINGS, NOCOUNT, ANSI_NULLS are on, then the recordset object crashes. The article you sent me, talks about how to fix a problem creating a stored sproc. After the stored proc is created, the warnings, nocount and ANSI_NULLS can be turned off. I have other sprcos that work this way with different linked servers. For some reason this one does not.

Posting Permissions

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