Archive

Posts Tagged ‘the use of Ad Hoc Distributed Queries’

SQL Server – Import Data from Excel using T-SQL

15.04.2013 6 comments

To import data from an Excel file to SQL Server you can use SQL Server Import and Export Wizard. You can also import Excel data using T-SQL OPENROWSET function. OPENROWSET function can be used to import data from and OLEDB data source.

For this example I have created a simple excel file which has a single row of data and column headers, and the sheet is named [Employees] as below:

image

I have stored this file in my C: root folder.

 

Now to import this data to SQL Server you can use OPENROWSET as below:

SELECT *

FROM   OPENROWSET('Microsoft.ACE.OLEDB.12.0',

       'Excel 12.0 Xml;HDR=YES;Database=C:Emp.xlsx',

       'SELECT * FROM [Employees$]')

Result Set:

image

As you can see this is very easy and straight forward. However, the above query only displays data from Excel file, it does not import it to database.

To import this data to SQL Server Table you can modify the SELECT statement to SELECT INTO

USE [SqlAndMe]

GO

 

SELECT * INTO dbo.ImportedEmployeeData

FROM   OPENROWSET('Microsoft.ACE.OLEDB.12.0',

       'Excel 12.0 Xml;HDR=YES;Database=C:Emp.xlsx',

       'SELECT * FROM [Employees$]')

GO

The above statement will create a new table ImportedEmployeeData and import excel data to the table.

 

Although this method seems straight forward, there are a few errors you may encounter:

 

Error Message 1:

Msg 7399, Level 16, State 1, Line 2

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error.

The provider did not give any information about the error.

 

Msg 7303, Level 16, State 1, Line 2

Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Cause 1: You may receive this error if the Excel file is currently opened.

Solution 1: Close the Excel file if it is currently opened and try executing again.

 

Error Message 2:

Msg 15281, Level 16, State 1, Line 2

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', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online.

Cause 2: The cause of this error is mentioned in the error message. You may receive this error message if Ad Hoc Distributed Queries are not enabled by system administrator.

Solution 2: You can use sp_configure to enable this component. Since this is an advanced option you also need to enable 'Show Advanced Options' to enable this component. Use below commands to enable this:

EXEC sp_configure 'Show Advanced Options', 1

RECONFIGURE

GO

 

EXEC sp_configure 'Ad Hoc Distributed Queries', 1

RECONFIGURE

GO

Result Set:

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.

Configuration option 'Ad Hoc Distributed Queries' changed from 1 to 1. Run the RECONFIGURE statement to install.

 

Error Message 3:

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)".

Cause 3: You may receive this error message if registry settings are not set properly

Solution 3: To resolve this error, run below commands to fix registry issues:

EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1

GO

 

EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1

GO

 

Hope This Helps!

Vishal

Categories: SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012, Working With Data Tags: .xls file, .xlsx file import, A system administrator can enable, ad hoc distributed queries, Allow In Process, AllowInProcess, because this component is turned off, by using sp_configure, Cannot create an instance of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server, Cannot initialize the data source object, component Ad Hoc Distributed Queries, Database, Dynamic Parameters, DynamicParameters, excel to sql server, EXEC, for linked server (null) reported an error, For more information about enabling, GO, import and export wizard, import data from excel, import data from excel file, import data from xls file, import data from xlsx file, import excel data to sql server, import export wizard, import from oledb source, import oledb data, Microsoft.ACE.OLEDB.12.0, Msg 15281 Level 16 State 1, Msg 7302 Level 16 State 1, Msg 7303 Level 16 State 1, Msg 7399 Level 16 State 1, OLE DB provider Microsoft.ACE.OLEDB.12.0 for linked server (null), open row set, openrowset, openrowset function, part of the security configuration for this server, Query, reconfigure, scripts, search for Ad Hoc Distributed Queries, SELECT * FROM OPENROWSET, SELECT * INTO FROM OPENROWSET, show advanced options, sp_configure, sp_MSSet_oledb_prop, SQL, SQL 2008 R2, Sql And Me, sql openrowset function, SQL Server, SQL Server blocked access to STATEMENT OpenRowset/OpenDatasource, SQL Server Books Online, sql server import and export wizard, SQLServer, SQLServer 2008 R2, The OLE DB provider, The provider did not give any information about the error, the use of Ad Hoc Distributed Queries, using openrowset, using openrowset to import data, Vishal Gajjar