Openrowset problem

Question

Post reply

Openrowset problem

sathiyan00

SSCommitted

Points: 1879
More actions
January 15, 2013 at 5:47 am

#393942

Hi all,
Is that possible to select The data from excel 2007 on sql server 2008 r2(64 bit),os Windows 7 (64 bit)
Using Sql?
I used Following query but it throws error
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Users\sathiyanr\Desktop\dynamic.xlsx',
'SELECT * FROM [Sheet1$]')
Error: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.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Lokesh Vij SSChampion Points: 10836 More actions · Answer 1

Try to execute the following set of commands and check if this works:

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'Ad Hoc Distributed Queries', 1;

GO

RECONFIGURE;

GO

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

sathiyan00 SSCommitted Points: 1879 More actions · Answer 2

sathiyan00

SSCommitted

Points: 1879

January 15, 2013 at 6:17 am

#1577300

I already tried this one lokesh....

but no change...

Lokesh Vij SSChampion Points: 10836 More actions · Answer 3

humm... Try this out then

Execute this first:

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1

GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1

GO

Then try to execute your query with this change:

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

'Excel 8.0;Database=C:\Users\sathiyanr\Desktop\dynamic.xlsx',

'SELECT * FROM [Sheet1$]')

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 4

The ACE drivers are not standard equipment on Windows Server 2008 so in case you;re looking for them you can download a 32 or 64-bit version here:

http://www.microsoft.com/en-us/download/details.aspx?id=13255

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

demonfox SSCertifiable Points: 6289 More actions · Answer 5

Lokesh Vij (1/15/2013)
humm... Try this out then
Execute this first:
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO
Then try to execute your query with this change:
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 8.0;Database=C:\Users\sathiyanr\Desktop\dynamic.xlsx',
'SELECT * FROM [Sheet1$]')

How about this one Lokesh :

EXEC sp_addlinkedserver @server = N'ExcelDataSource',

@srvproduct=N'ExcelData', @provider=N'Microsoft.ACE.OLEDB.12.0',

@datasrc=N'C:\DataFolder\People.xlsx',

@provstr=N'EXCEL 12.0' ;

SELECT col1,col2 FROM OPENQUERY(ExcelShare, 'SELECT * FROM [Sheet1$]')

I guess It can be used; when a particular excel is frequently used and has a plenty of sheets 🙂

(I use it for side testing ; just another way to accessing excel .)

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 6

Here is another syntax for your consideration:

http://www.sqlservercentral.com/Forums/FindPost1407497.aspx

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

sathiyan00 SSCommitted Points: 1879 More actions · Answer 7

is it working for anyone .

loading data in sql server 2008 r2(64 bit) from file(excel 2007,text file) using sql.....

demonfox SSCertifiable Points: 6289 More actions · Answer 8

It does work in R2 ; I have used openrowset in my project for adhoc insertions..

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

sathiyan00 SSCommitted Points: 1879 More actions · Answer 9

MS office 2007 or 2010?

windows 7 62 bit ?

Please guide me ....

sathiyan00 SSCommitted Points: 1879 More actions · Answer 10

after installation of msaccess12.0 driver its working...:-)