Archive

Posts Tagged ‘Linked Server’

SQL Error – Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS…

May 20, 2011 2 comments

Msg 7405, Level 16, State 1, Line 1
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 came through this strange and new error message while our team was deploying build on one of the servers. After debugging and reviewing the code we find the problem which was in one of the hundreds of stored procedures.

The scenario was:
– A stored procedure that uses Linked Server to pull record-set from another SQL Server database.
– Pull records and load this result set on a temp-table.
– For implementing the business logic and to do some calculations the query uses some aggregate functions, like SUM(), MIN(), MAX() on a few columns in that temp table.

Here if any of these aggregated column contain NULL values, it results in an obvious Warning message (not Error), i.e.: Warning: Null value is eliminated by an aggregate or other SET operation.
We could ignore this warning message, but when used in SSIS packages it causes the package to fail. So to avoid this Warning message, “SET ANSI_WARNINGS OFF” option was set at the beginning of the stored procedure.

The stored procedure compiled fine, but when we executed it, it resulted into this strange error: “Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS…”.

We observed that this error is due to setting ANSI_WARNINGS to OFF just before the linked server call. So the workaround was to shift the “SET ANSI_WARNINGS OFF” statement just below the linked server call. And to be on the safer side applied the “SET ANSI_WARNINGS ON” statement at the end of the stored procedure.

MSDN Forum links:
http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/1d501b57-fc58-4fbe-9bec-6c38ad158a62
http://social.msdn.microsoft.com/Forums/en-US/vstsdb/thread/03cab0c7-9e2b-459a-8ffd-21616ac5c465/
http://social.msdn.microsoft.com/Forums/en-IE/vstsdb/thread/67d2b3d2-e0fd-4669-a2e9-ca79c894b8d2

MS KB article regarding this issue: http://support.microsoft.com/kb/296769

Query Excel file source through Linked Server

November 12, 2010 9 comments

In previous post we saw how to setup a Linked Server for MySQL Database. Now lets go with other data sources. Excel files are the most important source of data and report management in a particular department.

When you need to do some query on Excel data, one way is to use Import/Export wizard, push the excel contents to SQL Server and then query on SQL Server DB. Another and easy way is to create a Linked Server to Excel file and query directly the Excel file itself.
 

You just need to create the Excel file and execute the following SQL Statements below:
 

–> For Excel 2003 format:

USE MSDB
GO
EXEC sp_addLinkedServer
	@server= 'XLS_NewSheet',
	@srvproduct = 'Jet 4.0',
	@provider = 'Microsoft.Jet.OLEDB.4.0',
	@datasrc = 'C:\Manoj_Advantage\NewSheet.xls',
	@provstr = 'Excel 5.0; HDR=Yes'

– Now, query your excel file in two ways:

SELECT * FROM OPENQUERY (XLS_NewSheet, 'Select * from [Sheet1$]')
SELECT * FROM XLS_NewSheet...[Sheet1$]

 

–> For Excel 2007 format:

USE MSDB
GO
EXEC sp_addLinkedServer
	@server= 'XLSX_NewSheet',
	@srvproduct = 'ACE 12.0',
	@provider = 'Microsoft.ACE.OLEDB.12.0',
	@datasrc = 'C:\Manoj_Advantage\NewSheet.xlsx',
	@provstr = 'Excel 12.0; HDR=Yes'

– Now, query your excel file in two ways:

SELECT * FROM OPENQUERY (XLSX_NewSheet, 'Select * from [Sheet1$]')
SELECT * FROM XLSX_NewSheet...[Sheet1$]

 

Note: If your excel file don’t have headers, then set HDR=No
 

You may need to execute the following SQL Statements to configure the Linked Server initially:

USE MSDB
GO
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OverRide
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE WITH OverRide
GO

 

>> Check & Subscribe my [YouTube videos] on SQL Server.