Cannot import data from excel 2003 to database using openrowset function

Question

This is my laptop spec :

OS : Windows 7 - 64bit , Database : SQL SERVER 2008 R2 , Microsoft Office : Microsoft Office 2007,

My problem is : When I run my procedure to import data from excel 2003 (xls) to database (SQL Server 2008) , I've got this error :

<span style="color:red">OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for 
      distributed queries because the provider is configured to run in 
      single-threaded apartment mode.</span>

this is my procedure :

SELECT * FROM  OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=d:\test.xls', 
                          'SELECT * FROM [Sheet1$]')

I've try to fix with :

Install AccessDatabaseEngine.exe

Run below on SQL:

EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
EXEC sp_configure 'show advanced options', 1;
GO
EXEC sp_MSset_oledb_prop N'Microsoft.Jet.OLEDB.4.0', N'AllowInProcess', 1
GO
EXEC sp_MSset_oledb_prop N'Microsoft.Jet.OLEDB.4.0', N'DynamicParameters', 1

, but I still get the error. Can somebody help me?

Yoko Zunna · Accepted Answer · 2012-03-28 08:57:08Z

SQL Query for OPENROWSET function :--

1) Open SQL Server Management Studio

2) Open the query pad and write the following commands

3) For Excel 97 – 2003 file that is files with extension XLS use

SELECT * INTO [dbo].[Addresses]
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\SQL Scripts\msp.xls;IMEX=1;HRD=Yes','SELECT * FROM [Sheet1$]')

i. It will create table with name Addresses in the current selected database.

ii. Microsoft.Jet.OLEDB.4.0 is the driver use for conversion

iii. Excel file with path - D:\SQL Scripts\msp.xls

iv. IMEX=1 property included, columns that contain intermixed data types are treated as string/text data types.

v. HRD =Yes property means the top row of excel file consists of Column Header name

vi. Sheet1 is the name of the sheet you want to import

vii. Excel 8.0 specifies that it is 97 – 2003 format excel file

4) To use filter query the user can use the where clause also with this command like

SELECT * INTO [dbo].[Addresses]
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\SQL Scripts\msp.xls;IMEX=1;HRD=Yes','SELECT * FROM [Sheet1$]') where [column_name]=’value’

5) To copy the excel file in predefine SQL table use OPENROWSET function with insert command like:-

Create table Custom (Source_IP_ADD varchar(20),API_NAME varchar(50),COUNT_NO varchar(5),CLIENT_ID varchar(50),Date_OF_INVOKE varchar(50))

INSERT INTO [dbo].[Custom] ( [Source_IP_ADD], [API_NAME], [COUNT_NO], [CLIENT_ID], [Date_OF_INVOKE])

    SELECT [Source_IP_ADDR], [API_NAME], [COUNT_NO], [CLIENT_ID], [Date_INVOK] FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\SQL Scripts\msp.xls;IMEX=1;HRD=Yes','SELECT * FROM [Sheet1$]')

6) For Excel 2007 – 2010 file that is files with extension XLSX use

SELECT * INTO [dbo].[Addresses] FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0' ,'Excel 12.0;Database=D:\SQL Scripts\msp.xlsx;HDR=YES;IMEX=1' ,'SELECT * FROM [Sheet1$]')

i. It will create table with name Addresses in the current selected database

ii. Microsoft.ACE.OLEDB.12.0 is the driver use for conversion

iii. Excel file with path - D:\SQL Scripts\msp.xlsx

iv. IMEX=1 property included, columns that contain intermixed data types are treated as string/text data types.

v. HRD =Yes property means the top row of excel file consists of Column Header name

vi. Sheet1 is the name of the sheet you want to import

vii. Excel 12.0 specifies that it is 2007 – 2010 format excel file

7) To copy the excel file in predefine SQL table use OPENROWSET function with insert command like:-

Create table Custom (Source_IP_ADD varchar(20),API_NAME varchar(50),COUNT_NO varchar(5),CLIENT_ID varchar(50),Date_OF_INVOKE varchar(50))

INSERT INTO [dbo].[Custom] ( [Source_IP_ADD], [API_NAME], [COUNT_NO], [CLIENT_ID], [Date_OF_INVOKE])
SELECT [Source_IP_ADDR], [API_NAME], [COUNT_NO], [CLIENT_ID], [Date_INVOK] FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0' ,'Excel 12.0;Database=D:\SQL Scripts\msp.xlsx;HDR=YES;IMEX=1' ,'SELECT * FROM [Sheet1$]')

I still get an error like this : Msg 7308, Level 16, State 1, Line 1 OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

Collectives™ on Stack Overflow

Cannot import data from excel 2003 to database using openrowset function

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related