SQLServer Quiz 2011 - Query out Data from Excel to 64-bit SQL Server table
Unfortunately, since there is no 64 bit provider, wecannot create linked servers directly to these data sources through (64 bit) SQL Server. From any 64 bit process, these providers are not available. But there is a work around.
My solution is the same solution proposed by iazakharov. OPENROWSSET() function is used to access any OLE DB data source. All we need is the right OLEDB driver.
There are few issues in loading excel sheet data into SQL Server 2008 64-bit.
First one is, we should have Ad Distributed Queries Enabled, which is shown below:
sp_configure 'show advanced options', 1;
sp_configure 'Ad Hoc Distributed Queries', 1;
Second one is we have to have Access Database Engine 2010 drivers installed and this should be of 64 bit.
My example excel file is located in D:\School. The sheet containing the list of Customers called TestSheet.
T-SQL to insert data from excel file to a SQL Server table is:
INSERT INTO Customers
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;HDR=YES;Database=D:\School\TestSheet.xlsx;',
'SELECT * FROM [TestSheet$]');
Furthermore, the query assumes that the first row contains the header, since we set it HDR=YES.
Issues that I encountered in loading data from excel to SQL Server and vice versa:
To resolve this issue, I enabled the Ad Distributed Queries using sp_configure advanced options. The OPENROWSET() function expects that the Ad Hoc Distributed Queries option is enabled on the server. When that’s not the case you’ll see the following 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.
The OPENROWSET() function uses OLE DB, so it needs a driver for your data source, in this case for Excel. If the right driver is not installed, you’ll see the following error (or similar, depends on the version used).
Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
To solve the issue, install the right driver and try again using the above query.
Opendatasource function can also be used with extended properties.
The OPENROWSET() can be a useful function, given the right circumstances. But in the wrong setting it can be quite cumbersome to get to work.