Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Loading data from Excel file into SQL table Expand / Collapse
Author
Message
Posted Wednesday, January 12, 2011 5:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, January 15, 2011 2:41 PM
Points: 2, Visits: 67
Hello everyone,

I have an Excel file with just one field and it contains numbers. I want to read the Excel file into a table in SQL Server. I have tried to use

select * into tablename
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\testing.xls;HDR=YES', 'SELECT * FROM [Sheet1$]')

However I am unsuccessful because I get the following error

Msg 7308, Level 16, State 1, Line 2
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.

Can you please help me out. I dont want to use SSIS. All I want is T-SQL.
Thank you all in advance.
Post #1046936
Posted Thursday, January 13, 2011 3:44 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, September 25, 2014 12:23 AM
Points: 594, Visits: 1,098
Hi

Try this useful link

http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/import-export-to-excel.aspx
Post #1047132
Posted Thursday, January 13, 2011 3:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:15 AM
Points: 13,017, Visits: 10,800
Junglee_George (1/13/2011)
Hi

Try this useful link

http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/import-export-to-excel.aspx



Did you read the original question? The link you provided just explains how to import data from excel using the OPENROWSET command. The OP has already done that, there is a problem with the provider.

@divya: check if distributed queries are enabled in your db:

EXEC sp_configure 'ad hoc distributed queries'





How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1047137
Posted Thursday, January 13, 2011 7:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:27 PM
Points: 13,776, Visits: 28,178
Just curious, have you tried using the Import Data Wizard? I'm not sure it will meet your needs or not, but you can frequently get a lot more error information out of that than with OPENROWSET. It might help, just to try.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1047292
Posted Friday, January 14, 2011 11:38 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, September 28, 2014 1:41 PM
Points: 959, Visits: 2,888
Here are the steps to allow distributed ad hoc queries:

sp_configure 'show advanced options', 1
reconfigure

sp_configure 'Ad Hoc Distributed Queries', 1

reconfigure with override

This should handle the problem.
Todd Fifield
Post #1048105
Posted Saturday, January 15, 2011 2:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, January 15, 2011 2:41 PM
Points: 2, Visits: 67
Hi all,

sp_configure 'show advanced options', 1
reconfigure

sp_configure 'Ad Hoc Distributed Queries', 1

reconfigure with override


With this script I was able to read from the Excel file. Thank you all for helping me..
Post #1048381
Posted Saturday, January 15, 2011 7:21 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, September 28, 2014 1:41 PM
Points: 959, Visits: 2,888
Divya,
Glad to be of help.
Todd Fifield
Post #1048392
Posted Wednesday, August 7, 2013 4:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 7, 2013 5:38 PM
Points: 3, Visits: 12
Hi all,

I've checked my configuration with sp_configure and 'Ad Hoc Distributed Queries' value is 1.

And when I exec my script to import Excel data into SQL table it returns this:

Msg 7308, Level 16, State 1, Line 2
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.

The syntax is OK and the Configuration too. So, Why I'm getting that error message?

Thanks in advance.
Post #1482102
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse