# Database Discussions > MS SQL Server 7/MS SQL Server 2000 >  Error 7405: Heterogeneous queries require ANSI_NULLS...

## Bruce Wexler

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 = &#39;1032&#39;
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

----------


## Raj

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 = &#39;1032&#39;
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

----------


## Bruce Wexler

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


------------
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 = &#39;1032&#39;
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

----------

