November 15, 2012 at 2:07 pm
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?
November 16, 2012 at 5:07 am
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.
November 19, 2012 at 2:55 pm
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply