Heterogeneous query error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Heterogeneous query error

Author

Topic

knox203
Starting Member

31 Posts

Posted - 2009-07-20 : 13:27:06

Hello all, I have a query that first pulls data from my AD Domain Controller through a linked server connection, then puts the data in a temporary table. The query runs just find in SQL Management Studio, but as soon as I try to run it from my webpage, I get an error that reads:
"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". I have set "ANSI_NULLS and ANSI_WARNINGS" ON within the query, but I still get the same error.

Here's my query:


SET ANSI_NULLS ON
SET ANSI_WARNINGS ON

SELECT * INTO #Temp
FROM openquery 
(
ADSI,'SELECT givenname, sn, mail
FROM ''LDAP://DC=domain,DC=com''
WHERE objectClass = ''user''
')

SELECT	t.[Year], t.[Month], t.[Day], t.[Time], t.[Body],
		(CASE WHEN ad.givenname IS NULL AND ad.sn IS NULL THEN 'Expired' + ' ' + '(' + SUBSTRING(t.[From], 1, 9) + ')' ELSE ad.givenname + ' ' + ad.sn COLLATE SQL_Latin1_General_CP1_CI_AS END) AS [From], 
		(CASE WHEN ac.givenname IS NULL AND ac.sn IS NULL THEN 'Expired' + ' ' + '(' + SUBSTRING(t.[To], 1, 9) + ')' ELSE ac.givenname + ' ' + ac.sn COLLATE SQL_Latin1_General_CP1_CI_AS END) AS [To]
FROM
	(
		SELECT	DATEPART(YEAR, DATEADD(hh, -7, m1.MessageIdTime)) AS [Year],
				DATEPART(MONTH, DATEADD(hh, -7, m1.MessageIdTime)) AS [Month],
				DATEPART(DAY, DATEADD(hh, -7, m1.MessageIdTime)) AS [Day],
				RIGHT('00'+CAST(DATEPART(HOUR, DATEADD(hh, -7, m1.MessageIdTime)) AS VARCHAR(2)), 2) + ':' + 
				RIGHT('00'+CAST(DATEPART(MINUTE, m1.MessageIdTime) AS VARCHAR(2)), 2) + ':' + 
				RIGHT('00'+CAST(DATEPART(SECOND, m1.MessageIdTime) AS VARCHAR(2)), 2) AS [Time], 
				CAST(m1.Body AS TEXT) AS [Body], u1.UserUri AS [From], u2.UserUri AS [To]
		FROM	LcsLog.dbo.Messages AS m1 INNER JOIN
				LcsLog.dbo.Users AS u1 ON m1.FromId = u1.UserId INNER JOIN
				LcsLog.dbo.Users AS u2 ON m1.ToId = u2.UserId
		WHERE     (u1.UserUri = 'user103@domain.com') AND (m1.Toast IS NULL) AND (DATEADD(hh, -7, m1.MessageIdTime) BETWEEN '07/01/09' AND '07/20/09')
		UNION ALL
		SELECT	DATEPART(YEAR, DATEADD(hh, -7, m2.MessageIdTime)) AS [Year],
				DATEPART(MONTH, DATEADD(hh, -7, m2.MessageIdTime)) AS [Month],
				DATEPART(DAY, DATEADD(hh, -7, m2.MessageIdTime)) AS [Day],
				RIGHT('00'+CAST(DATEPART(HOUR, DATEADD(hh, -7, m2.MessageIdTime)) AS VARCHAR(2)), 2) + ':' + 
				RIGHT('00'+CAST(DATEPART(MINUTE, m2.MessageIdTime) AS VARCHAR(2)), 2) + ':' + 
				RIGHT('00'+CAST(DATEPART(SECOND, m2.MessageIdTime) AS VARCHAR(2)), 2) AS [Time], 
				CAST(m2.Body AS TEXT) AS [Body], u2.UserUri AS [From], u1.UserUri AS [To]
		FROM	LcsLog.dbo.Messages AS m2 INNER JOIN
				LcsLog.dbo.Users AS u2 ON m2.FromId = u2.UserId INNER JOIN
				LcsLog.dbo.Users AS u1 ON m2.ToId = u1.UserId
		WHERE	(u1.UserUri = 'user103@domain.com') AND (m2.Toast IS NULL) AND (DATEADD(hh, -7, m2.MessageIdTime) BETWEEN '07/01/09' AND '07/20/09')
	) t LEFT JOIN
	#Temp ad ON t.[From] = ad.mail COLLATE SQL_Latin1_General_CP1_CI_AS LEFT JOIN
	#Temp ac ON t.[To] = ac.mail COLLATE SQL_Latin1_General_CP1_CI_AS
ORDER BY t.[Year], t.[Month], t.[Day], t.[Time]

DROP TABLE #Temp
SET ANSI_NULLS OFF
SET ANSI_WARNINGS OFF

Can anyone help shed some light on this? Thanks!

- Adam

knox203
Starting Member

31 Posts

Posted - 2009-07-20 : 17:42:35

I've figured it out; I needed to create a stored procedure, then apply "SET ANSI_NULLS ON" and "SET ANSI_WARNINGS ON" to my web page calling the stored procedure.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources