error in importing excel using TSQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

error in importing excel using TSQL

Author

Topic

mary_itohan
Posting Yak Master

191 Posts

Posted - 2014-03-12 : 19:36:43

hello guys,
am trying to import an excel workbook from a stored procedure and get the error below.

Here is my script

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'DataSource=c:\book1.xls;Extended Properties=Excel 8.0', Sheet1$)

and hers is the error, both locally and on the server

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

kindly advise

_____________________

Yes O !

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-03-12 : 19:51:12

Run this and then try your script again:

exec sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
exec sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

mary_itohan
Posting Yak Master

191 Posts

Posted - 2014-03-13 : 05:49:56

still get this error

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

_____________________

Yes O !

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-03-13 : 12:31:27

I would suggest googling that error then as there is a ton of hits for that error.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2014-03-18 : 06:29:12

I had this problem back in the day, you are probably using a 64 bit version which would not work. If so, this might help you

https://www.google.co.uk/search?q=import+excel+into+sql+stored+procedure%2C+64bit+systems&oq=import+excel+into+sql+stored+procedure%2C+64bit+systems&aqs=chrome..69i57.18232j0j7&sourceid=chrome&espv=210&es_sm=93&ie=UTF-8

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources