troubleshooting Question

Hetergoenous query, linked servers & Excel pivot table

Avatar of wizard_340
wizard_340 asked on
Microsoft SQL ServerMicrosoft ExcelMicrosoft SQL Server 2005
5 Comments1 Solution305 ViewsLast Modified:
I'm trying to populate a pivot table in Excel using data from an ODBC connection.  The connection points to DB1, which is a SQL 2000 server and is set up with a linked server to DB2, a SQL 2005 server, in order to combine the necessary data.  

This all works in SQL, but I'm getting the dreaded "Heterogenous queries require ANSI_NULLS and ANSI_WARNINGS to be set" error when run in Excel.  I've rewritten the SQL portion 3 different ways so far with the same results: 1) originally a view built upon other views, 2) a sproc dependant on views, and 3) direct table references.  In all cases I've expressly set the options in the code.

I've double-checked the ODBC connection to verify ANSI options are enabled, and they are.  I'm at a loss what's actually tripping the ANSI issue at this point.
USE [Budgets]
GO
/****** Object:  StoredProcedure [dbo].[GetPriorYearBudget_sp]    Script Date: 10/21/2009 16:21:44 ******/
SET ANSI_WARNINGS ON
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetPriorYearBudget_sp] @NewPNO NVARCHAR(6), @BudgetStartDate DATETIME
AS 
BEGIN
--DECLARE @NewPNO NVARCHAR(6), @BudgetStartDate DATETIME
--SET @NewPNO = '1445'
--SET @BudgetStartDate = '20081216'
 
SELECT  ISNULL(r.PeriodDate, '20640401') AS PeriodDate
		,bm.SumAcct AS Sort
		,-SUM(ISNULL(r.Amt, 0)) AS Amt
		,bm.SumName
		,ISNULL(r.NEWPNO, '0000') AS NewPNO
FROM	(SELECT pm.NAME, t.UMONTH AS PeriodDate, t.SBUDGET AS Amt, 
			pm.NEWPNO, am.Seg3_Code AS Acct
		FROM db2.cabqleak_live.dbo.property AS p 
			INNER JOIN dbo.PropertyMaster_vw pm ON p.scode = pm.Yardi_PNO 
			INNER JOIN db2.cabqleak_live.dbo.total AS t ON p.HMY = t.HPPTY 
			INNER JOIN db2.cabqleak_live.dbo.acct AS a ON t.HACCT = a.HMY
			INNER JOIN dbo.YardiAccountMap_vw am ON a.scode = REPLACE(am.Yardi_Account, '-', '')
		WHERE pm.NewPNO = @NewPNO
		AND t.UMONTH >= @BudgetStartDate) AS r 
	RIGHT OUTER JOIN dbo.tblBudgetMapping AS bm ON r.Acct = bm.AcctNo
GROUP BY r.NAME, r.PeriodDate, bm.SumAcct, bm.SumName, r.NEWPNO
ORDER BY r.NAME, r.PeriodDate, bm.SumAcct
 
END
GO 
SET ANSI_WARNINGS OFF 
SET ANSI_NULLS OFF
SET QUOTED_IDENTIFIER OFF
GO
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros