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 12»»

Openrowset problem Expand / Collapse
Author
Message
Posted Tuesday, January 15, 2013 5:47 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 15, 2014 2:05 PM
Points: 73, Visits: 475
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.






Post #1407202
Posted Tuesday, January 15, 2013 6:12 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 6:38 PM
Points: 1,371, Visits: 1,560
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
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1407218
Posted Tuesday, January 15, 2013 6:17 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 15, 2014 2:05 PM
Points: 73, Visits: 475
I already tried this one lokesh....
but no change...
Post #1407221
Posted Tuesday, January 15, 2013 6:54 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 6:38 PM
Points: 1,371, Visits: 1,560
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
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1407251
Posted Tuesday, January 15, 2013 11:48 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:23 AM
Points: 7,098, Visits: 12,605
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
Post #1407625
Posted Wednesday, January 16, 2013 2:36 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:15 PM
Points: 1,129, Visits: 1,163
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
Post #1407701
Posted Wednesday, January 16, 2013 8:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:23 AM
Points: 7,098, Visits: 12,605
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
Post #1407868
Posted Thursday, January 17, 2013 3:19 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 15, 2014 2:05 PM
Points: 73, Visits: 475
is it working for anyone .
loading data in sql server 2008 r2(64 bit) from file(excel 2007,text file) using sql.....
Post #1408271
Posted Thursday, January 17, 2013 6:49 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:15 PM
Points: 1,129, Visits: 1,163
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
Post #1408401
Posted Thursday, January 17, 2013 9:57 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 15, 2014 2:05 PM
Points: 73, Visits: 475
MS office 2007 or 2010?

windows 7 62 bit ?



Please guide me ....
Post #1408711
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse