Posts Tagged ‘sp_addlinkedserver’

Querying Excel 2010 from SQL Server in 64-bit environment

July 10, 2012 6 comments

In my previous post I discussed about querying Excel files with both 2003 & 2007 versions from SQL Server.

Here we will see how can we query Excel 2010 files. Will use the same scripts from my previous post to create Linked Server to point to the Excel source. We don’t need to do any change in the scripts for this version, but we will require some additional steps to do, that we’ll see later on.

Please Note: To use a Linked server for fetching records from a particular source first you have to enable the “Ad Hoc Distributed Queries” configuration setting. This is disabled by default, if not enabled you will see the following error message:

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure. For more information about enabling ‘Ad Hoc Distributed Queries’, see “Surface Area Configuration” in SQL Server Books Online.

So let’s first of all enable this:


sp_configure 'show advanced options', 1
sp_configure 'Ad Hoc Distributed Queries', 1

You can also enable this setting graphically by going to “Surface Area Configuration” and enable it.

Now, to access the Excel file there are 2 ways:
1. Directly fetch records from Excel by using the OPENROWSET() function by providing the providers and other options
2. Indirectly by creating a Linked Server first of all, then:
2.a. fetching records from Excel by using OPENQUERY() function or
2.b. by using the Linked Server name within the SELECT query

-- 1. Directly, by using OPENROWSET() function
	'Excel 12.0;Database=E:\SQL Server - Blogs\LinkedServer2010.xlsx;HDR=YES;IMEX=1',
	'SELECT * FROM [Sheet1$]'

-- OR --

-- 2. Indirectly, by Creating Linked Server & using OPENQUERY:
EXEC sp_addLinkedServer
	@server= N'XLSX_2010',
	@srvproduct = N'Excel',
	@provider = N'Microsoft.ACE.OLEDB.12.0',
	@datasrc = N'E:\SQL Server - Blogs\LinkedServer2010.xlsx',
	@provstr = N'Excel 12.0; HDR=Yes';

-- 2.a. Using OPENQUERY() function:
SELECT * FROM OPENQUERY (XLSX_2010, 'Select * from [Sheet1$]')

-- 2.b. Using the Linked Server name within the SELECT query:
SELECT * FROM XLSX_2010...[Sheet1$]

Neither of the above options runs and ends up in following error:

Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider “Microsoft.ACE.OLEDB.12.0″ for linked server “XLSX_2010″.

I searched on net and I got following solution in MSDN forums to register the ACE OLEDB 12.0 provider:

USE [master]

EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1

EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1

The previous error was gone, but this new error surfaced:

Msg 7438, Level 16, State 1, Line 1
The 32-bit OLE DB provider “Microsoft.ACE.OLEDB.12.0″ cannot be loaded in-process on a 64-bit SQL Server.

To resolve this error you need to get the “Access Database Engine 2010″ to facilitate the transfer of data between existing Microsoft Office files such as “Microsoft Office Access 2010″ (*.mdb and *.accdb) files and “Microsoft Office Excel 2010″ (*.xls, *.xlsx, and *.xlsb) files to other data sources such as “Microsoft SQL Server”.

Get the Microsoft Access Database Engine 2010 Redistributable []
There will be two files:
1. AccessDatabaseEngine i.e. 32-bit and
2. AccessDatabaseEngine_x64 i.e. 64-bit
My machine is 64 bit and the SQL Server is also 64 bit, but let’s just check it:

select @@VERSION
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64)   
	Apr 22 2011 19:23:43   
	Copyright (c) Microsoft Corporation  
	Data Center Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1)

So, download and install the second “AccessDatabaseEngine_x64.exe” file.

While installing the 64-bit EXE it gives following error:

You cannot install the 64-bit version of Microsoft Access Database Engine 2010 because you currently have 32-bit Office products installed. If you want to install 64-bit Microsoft Access Database Engine 2010, you will first need to remove the 32-bit installation of Office products. After uninstalling the following product(s), rerun setup in order to install 64-bit version of Microsoft Access Database Engine 2010:
Microsoft Office Professional Plus 2010, Microsoft Office Visio 2010

So, this error tells that the MS Office product installed in my system is 32-bit, that I was not aware of.
To get this resolved you need to have everything in 64-bit environment. So, I will uninstall the 32-bit version of MS Office and re-install MS Office with 64-bit version.

Now if you again run the above queries, you will see them running successfully and pulling records from Excel file.

–> So, what additional steps we did for Excel 2010 to work?

Step 1:
Registered the Microsoft.ACE.OLEDB.12.0 provider for AllowInProcess & DynamicParameters properties.
This option registeres the provider and both the properties in registry. You can check what system stored procedure [sp_MSset_oledb_prop] does with sp_helptext SP.

EXEC sp_helptext 'sp_MSset_oledb_prop'

You can also go and check the registry by typing “regedit” in RUN.
Go to HKEY_LOCAL_MACHINE -> SOFTWARE -> Microsoft -> Microsoft SQL Server -> MSSQL10_50.MSSQLSERVER08R2 -> Providers
A new folder entry is made for “Microsoft.ACE.OLEDB.12.0″ provider.
And Inside this folder 2 new entries are created for both the properties.
Check the image below:

Step 2:
We installed the “Access Database Engine 2010″ drivers to setup a link between MS Office 2010 products and MS SQL Server.

Query Excel file source through Linked Server

November 12, 2010 7 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:

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:

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:

sp_configure 'show advanced options', 1
sp_configure 'Ad Hoc Distributed Queries', 1

Useful Link:

Linked Server in MS SQL Server

November 10, 2010 1 comment

Linked Servers provides access to external datasources be it another databases like Oracle, MySQL, or Excel files.

– Remote server access.
– The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.
– The ability to address diverse data sources similarly.

MSDN Links on Linked Servers:

My idea here is to query MySQL tables in MS SQL Server as I’m more comfortable with MS SQL queries and semantics. I don’t know if or how I can use Ranking functions, case statements, etc in MySQL DB. Plus I’m also not comfortable in writing queries in DOS like editor or any other freeware tool.

Linked Server to MySQL:

Before creating a Linked Server for MySQL you need to install the MySQL ODBC connector.
Download MySQL ODBC Client:

Now we need a DSN that will act as a bridge between for creating the Linked Server:
Create a System DSN:
– On Control Pannel -> Admin Tools -> Data Sources (ODBC), Select System DSN tab, click ADD, Selct “MySQL ODBC 3.51 Driver”, Click Finish.
– A new pop-up will come up, “Connector/ODBC 3.51.27 – Configure Data Source Name”.
– On Login Tab: Set fields, Click Test.
– On Advanced Tab, go to following tabs and check the options:
– Flag1: Return Matching Rows, Allow Big Results, Use Compressed Protocol, Change BIGINT columns to Int, Safe
– Flag2: Don”t Prompt Upon Connect, Ignore # In Table Name
– Flag3: Return Table Names for SQLDescribeCol, Disable Transactions
– Click “OK”

Create a New Linked Server:
– On SSMS under Object Explorer go to “Server Objects” -> “Linked Servers”, Richt Click and select “New Linked Server”
– Set an appropriate name on “Linked Server”, like: MYSQL_LINK
– Server Type = Select “Other Data Source” radio button.
– Set Provider = Microsoft OLE DB Provider for ODBC Drivers
– Set the “Product Name” & “Data Source” field you set on configuring the DSN.

This can also be setup by following SQL statements:

-- Create New Linked Server
EXEC sp_addlinkedserver
   @server = 'MYSQL_LINK',
   @srvproduct = 'MySQLDatabase',
   @provider = 'MSDASQL',
   @datasrc = 'MySQLKayako'

-- Pull list of all Servers
select * from sys.servers
EXEC sp_linkedservers

-- Drop the Linked Server
EXEC sp_dropserver 'MYSQL_LINK'

Now you can query the tables and other objects of MySQL database by using OPENQUERY function as shown below:

-- Select a table or view
-- Execute a function

OPENQUERY() also helps in selecting a Stored Procedure result just like a table.



Get every new post delivered to your Inbox.

Join 450 other followers