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 Exel 2007 file Expand / Collapse
Author
Message
Posted Thursday, November 15, 2012 2:07 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, December 17, 2012 4:21 PM
Points: 74, Visits: 316
I can not access an excel file that has no permission issues.

I have SQL Server 2008 R2 installed on a Windows 7 32 bit PC. I down loaded and installed 'Microsoft Access database engine 2010'.

I updated the server config with:
sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

I accessed the Microsoft.ACE.OLEDB.12.0 properties and checked 'Dynamic Parameter' and 'Allow inprocess'

I login with my windows auth account and issued the command:
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'EXCEL 12.0 Xml;Database=C:\Users\Public\Documents\Copy1.xlsx; HDR=YES','SELECT * FROM [Sheet1$]');

which fails with the message:
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

I have been searching the net for more then a day now and I can not find anything that would help me resolve this issue. Any ideas?
Post #1385363
Posted Friday, November 16, 2012 5:07 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:31 PM
Points: 2,389, Visits: 2,924
I guess you have a typo of some kind in the connection string. Can you access and load from the Excel file using the import/export wizard?
When you save the import/export wizard as an SSIS package, you are able to see the connection string used by the package. Perhaps this connection string can lead you to the correct connection string for use with OPENROWSET.


** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1385612
Posted Monday, November 19, 2012 2:55 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, December 17, 2012 4:21 PM
Points: 74, Visits: 316
Via the import/export wizard I can readily import the Excel file. I copied the connection string from the generated SSIS file and tried that in my query window and I have the same non-descript error. I even (thinking it was a permission error ) changed the system TMP and TEMP values to point to c:\tmp and changed the permission to EVERYONE. It had no affect.
Post #1386588
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse