openrowset only works on inactive node

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

openrowset only works on inactive node

Author

Topic

dmaxj
Posting Yak Master

174 Posts

Posted - 2012-04-12 : 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

15732 Posts

Posted - 2012-04-12 : 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

174 Posts

Posted - 2012-04-12 : 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

15732 Posts

Posted - 2012-04-12 : 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

174 Posts

Posted - 2012-04-12 : 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

5072 Posts

Posted - 2012-04-12 : 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

174 Posts

Posted - 2012-04-12 : 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

174 Posts

Posted - 2012-04-17 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources