| Author |
 Topic  |
|
|
dmaxj
Posting Yak Master
165 Posts |
 Posted - 04/12/2012 : 10:41:13
|
I have a two node cluster hosting my SQL Server 2008 R2 instance 64-bit. I get this error with I try to run my query on the active node:
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 3
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
Here is my query:
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0'
,'Excel 12.0 Xml;IMEX=1
;Database=C:\excel\test_2003.xls;'
,'Select * From [Sheet1$]')
GO
I get the correct result set when I run the query on the inactive node using the same SQL instance. Any clues as to how to resolve this problem?
Regards |
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 04/12/2012 : 10:54:07
|
| You're referencing the C: drive which is local to the node. Move the Excel file to shared storage (preferred) or use a UNC path (\\server\c$\Excel\). |
 |
|
|
dmaxj
Posting Yak Master
165 Posts |
Posted - 04/12/2012 : 11:29:43
|
Hmmm - Well, I do have the file local to each node at C:\excel - so should I still have this problem? In addition, I caused a failover and got the same result when the nodes swapped roles - meaning the new active node game the same error and the inactive node gave the correct result set.
So do you think that I should still pursue using a shared location to solve my problem? |
|
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 04/12/2012 : 11:40:27
|
| Yes. The C drives of each node are independent and the file could get out of sync. Best way to be sure you're accessing the same file is on shared storage, or on a completely separate server via UNC path. |
|
|
|
dmaxj
Posting Yak Master
165 Posts |
Posted - 04/12/2012 : 12:29:30
|
Ok - I have moved the excel file to a shared location. I still get the same error for the active node and I get the result set for the inactive node. In addition, I tried to set up a linked server to the excel file. I get the exact same results when using OPENROWSET.
I have the Microsoft Access database engine 2010(English) driver installed - fyi. |
|
|
|
russell
Pyro-ma-ni-yak
USA
4984 Posts |
Posted - 04/12/2012 : 12:31:23
|
| Make sure the file isn't in use -- including by a linked server. Excel is a single user app. |
|
|
|
dmaxj
Posting Yak Master
165 Posts |
Posted - 04/12/2012 : 12:55:18
|
Yes, the files are not in use - I even placed the file in a new location with my credentials and the sql service account credentials only - still getting the same result.
|
|
|
|
dmaxj
Posting Yak Master
165 Posts |
Posted - 04/17/2012 : 15:03:37
|
I am reporting back that this issue has been RESOLVED:
The solution lies with allowing the SQL Service account read/write/fullAccess privileges on a TEMP FOLDER used by SQL Server -
C:\Users\<serviceAccount>\AppData\Local\Temp
Browse the the above path, right click on Temp, Properties, Security - Add the SQL Service Account (or have someone with privileges to add the SQL Service Account)
After I applied the changes, my query worked.
Regards |
|
|
| |
Topic |
|
openrowset only works on inactive node
