Results 1 to 3 of 3

Thread: Error 7405: Heterogeneous queries require ANSI_NULLS...

  1. #1
    Bruce Wexler Guest

    Error 7405: Heterogeneous queries require ANSI_NULLS...

    I have a Stored Procedure I am trying to run that joins to a remote database. I am able to see everything in the QA just fine with this (courtesy of Anatha):

    SELECT DISTINCT a.*
    FROM LOCATION a,
    LinkServer.MC_Card.webuser.LOCATION b
    WHERE a.location_number = b.location_number

    But I am trying to run this query in Stored Procedure(notice the 4-part name callout to the LinkedServer tables) which returns the error message:

    Error 7405: Heterogeneous queries require 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.

    Here is the Stored Procedure:
    /****** Object: Stored Procedure dbo.spELRMCcardXtionByDate
    Script Date: 4/24/2001 11:51:27 AM ******/

    CREATE PROCEDURE dbo.spELRMCcardXtionByDate @dcid nvarchar(255), @startDate datetime, @endDate datetime
    AS
    -- declare @dcid nvarchar(255)
    -- set @dcid = '1032'
    SELECT STORE.[Str#], STORE.[Dcid#], E.card_number, E.program_number
    , E.start_date, E.end_date, E.card_number, E.event_number
    , E.status, E.budget, E.scheduled_date, P.tx_time, P.purchase_amount
    , L.merchant_name
    FROM (STORE INNER JOIN LinkServer.MC_Card.webuser.EVENT E ON STORE.[DemoID#] = E.event_number)
    LEFT JOIN (LinkServer.MC_Card.webuser.LOCATION L RIGHT JOIN LinkServer.MC_Card.webuser.POS_TX P ON L.location_number = P.location_number)
    ON E.event_number = P.event_number
    WHERE (((STORE.[Dcid#])= @dcid)) AND E.scheduled_date BETWEEN @startDate AND @endDate
    ORDER BY STORE.[Str#]
    -- and E.card_number IS NOT NULL
    GO

    Any help greatly appreciated.

    Thanks,
    Bruce

  2. #2
    Raj Guest

    Error 7405: Heterogeneous queries require ANSI_NULLS... (reply)

    Hi ! Add these two T-SQL
    GO
    SET ANSI_WARNINGS OFF
    GO
    SET ANSI_NULLS ON
    GO
    in your stored procedure & let me know if u still get the error
    Best of Luck


    ------------
    Bruce Wexler at 5/8/01 6:15:07 PM

    I have a Stored Procedure I am trying to run that joins to a remote database. I am able to see everything in the QA just fine with this (courtesy of Anatha):

    SELECT DISTINCT a.*
    FROM LOCATION a,
    LinkServer.MC_Card.webuser.LOCATION b
    WHERE a.location_number = b.location_number

    But I am trying to run this query in Stored Procedure(notice the 4-part name callout to the LinkedServer tables) which returns the error message:

    Error 7405: Heterogeneous queries require 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.

    Here is the Stored Procedure:
    /****** Object: Stored Procedure dbo.spELRMCcardXtionByDate
    Script Date: 4/24/2001 11:51:27 AM ******/

    CREATE PROCEDURE dbo.spELRMCcardXtionByDate @dcid nvarchar(255), @startDate datetime, @endDate datetime
    AS
    -- declare @dcid nvarchar(255)
    -- set @dcid = '1032'
    SELECT STORE.[Str#], STORE.[Dcid#], E.card_number, E.program_number
    , E.start_date, E.end_date, E.card_number, E.event_number
    , E.status, E.budget, E.scheduled_date, P.tx_time, P.purchase_amount
    , L.merchant_name
    FROM (STORE INNER JOIN LinkServer.MC_Card.webuser.EVENT E ON STORE.[DemoID#] = E.event_number)
    LEFT JOIN (LinkServer.MC_Card.webuser.LOCATION L RIGHT JOIN LinkServer.MC_Card.webuser.POS_TX P ON L.location_number = P.location_number)
    ON E.event_number = P.event_number
    WHERE (((STORE.[Dcid#])= @dcid)) AND E.scheduled_date BETWEEN @startDate AND @endDate
    ORDER BY STORE.[Str#]
    -- and E.card_number IS NOT NULL
    GO

    Any help greatly appreciated.

    Thanks,
    Bruce

  3. #3
    Bruce Wexler Guest

    Error 7405: Heterogeneous queries require ANSI_NULLS... (reply)

    Thanks Raj. You guys are all great to help me so much! I love this group!


    ------------
    Raj at 5/8/01 6:23:16 PM

    Hi ! Add these two T-SQL
    GO
    SET ANSI_WARNINGS OFF
    GO
    SET ANSI_NULLS ON
    GO
    in your stored procedure & let me know if u still get the error
    Best of Luck


    ------------
    Bruce Wexler at 5/8/01 6:15:07 PM

    I have a Stored Procedure I am trying to run that joins to a remote database. I am able to see everything in the QA just fine with this (courtesy of Anatha):

    SELECT DISTINCT a.*
    FROM LOCATION a,
    LinkServer.MC_Card.webuser.LOCATION b
    WHERE a.location_number = b.location_number

    But I am trying to run this query in Stored Procedure(notice the 4-part name callout to the LinkedServer tables) which returns the error message:

    Error 7405: Heterogeneous queries require 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.

    Here is the Stored Procedure:
    /****** Object: Stored Procedure dbo.spELRMCcardXtionByDate
    Script Date: 4/24/2001 11:51:27 AM ******/

    CREATE PROCEDURE dbo.spELRMCcardXtionByDate @dcid nvarchar(255), @startDate datetime, @endDate datetime
    AS
    -- declare @dcid nvarchar(255)
    -- set @dcid = '1032'
    SELECT STORE.[Str#], STORE.[Dcid#], E.card_number, E.program_number
    , E.start_date, E.end_date, E.card_number, E.event_number
    , E.status, E.budget, E.scheduled_date, P.tx_time, P.purchase_amount
    , L.merchant_name
    FROM (STORE INNER JOIN LinkServer.MC_Card.webuser.EVENT E ON STORE.[DemoID#] = E.event_number)
    LEFT JOIN (LinkServer.MC_Card.webuser.LOCATION L RIGHT JOIN LinkServer.MC_Card.webuser.POS_TX P ON L.location_number = P.location_number)
    ON E.event_number = P.event_number
    WHERE (((STORE.[Dcid#])= @dcid)) AND E.scheduled_date BETWEEN @startDate AND @endDate
    ORDER BY STORE.[Str#]
    -- and E.card_number IS NOT NULL
    GO

    Any help greatly appreciated.

    Thanks,
    Bruce

Posting Permissions

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