OpenRowSet works locally but not over networked drives

Question

OpenRowSet works locally but not over networked drives

Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

sqldriver SSChampion Points: 10440 More actions · Answer 1

Elliott Whitlow (7/10/2013)
erikd (7/10/2013)
I've given the clients\SQLService user explicit rights to the folder. from Full Control on down. Nothing is denied. I immediately ran the query, and got the same error as my original post. Let me know if there's something additional I should do (reboot, wait for replication of some sort, etc.).
Thanks
Did you apply the change to existing files? I'm guessing that might be it..
CEWII

Yes, sorry, I applied it to the file itself. Same error. I even went a little crazy and gave the clients\SQLService account full control of every drive and directory on down to the file.

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 2

erikd (7/10/2013)
Elliott Whitlow (7/10/2013)
erikd (7/10/2013)
I've given the clients\SQLService user explicit rights to the folder. from Full Control on down. Nothing is denied. I immediately ran the query, and got the same error as my original post. Let me know if there's something additional I should do (reboot, wait for replication of some sort, etc.).
Thanks
Did you apply the change to existing files? I'm guessing that might be it..
CEWII
Yes, sorry, I applied it to the file itself. Same error. I even went a little crazy and gave the clients\SQLService account full control of every drive and directory on down to the file.

Grrrrrr... Ok, what if you do a:

EXEC master.dbo.xp_cmdshell 'TYPE \\server\share\filename.ext ' what is the output? IF it reads it you should get some goofy stuff..

CEWII

sqldriver SSChampion Points: 10440 More actions · Answer 3

Wacky stuff indeed! Trying to read it out loud kind of makes me feel like handling venomous snakes.

mister.magoo SSC-Forever Points: 47068 More actions · Answer 4

Do you have any mapped drives on the sql server under that domain account?

If so, can you copy the file to one of those and see if you can read it using ORS from there - just wondering is the UNC path is the problem...?:hehe:

EDIT: for clarity, I mean a network storage location mapped to a drive letter on the sql server...

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 5

I looked at the output and immediately latched onto the first 2 characters, PK, are you sure this is an XLS file and not a ZIP file maybe containing an XLS file? I might be reading too much into it..

I took an xls file and zipped it and the first few characters were: PK??¶, that looks pretty similar to your output..

CEWII

sqldriver SSChampion Points: 10440 More actions · Answer 6

mister.magoo (7/10/2013)
Do you have any mapped drives on the sql server under that domain account?
If so, can you copy the file to one of those and see if you can read it using ORS from there - just wondering is the UNC path is the problem...?:hehe:
EDIT: for clarity, I mean a network storage location mapped to a drive letter on the sql server...

Ah, yeah. One of the first things I did was try mapping it to P:, and then also mapping it without a drive letter, just to make sure SQL could even map it, and through both xp_cmdshells executed, the ORS did not.

sqldriver SSChampion Points: 10440 More actions · Answer 7

Elliott Whitlow (7/10/2013)
I looked at the output and immediately latched onto the first 2 characters, PK, are you sure this is an XLS file and not a ZIP file maybe containing an XLS file? I might be reading too much into it..
I took an xls file and zipped it and the first few characters were: PK??¶, that looks pretty similar to your output..
CEWII

I'm positive. That seems to be the first line of output for any .xlsx file in the folder. The only one TYPE reads as human is a text file that's also in there.

mister.magoo SSC-Forever Points: 47068 More actions · Answer 8

Could be an xlsx. They are zips also.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 9

Well I had typed a response and it got lost...

So a quick retype..

The query you are doing seems to be in the spirit of:

http://msdn.microsoft.com/en-us/library/ms179856.aspx

I think the error message:

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.

Might be the real clue, it can't initialize it and I wish we could get a better message.. Errorlog maybe?

You might try messing with the connection string with this as an idea:

http://www.connectionstrings.com/ace-oledb-12-0/[/url]

I also think mister.magoo might be right about the xlsx actually being sort of zipped internally..

CEWII

mister.magoo SSC-Forever Points: 47068 More actions · Answer 10

It's not sort of zipped internally. It is a zip. Change the extension to zip and open it to see. But this is kind of off topic:-D

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

sqldriver SSChampion Points: 10440 More actions · Answer 11

My mind is so blown by what happens when you rename a .xlsx file to .zip and open it that I don't care if you go entirely off topic about it. I did kind of wonder why an empty Excel file was 9 kb,

I've played with connection strings to no avail. The only thing that looked promising was changing the Excel version to 14.0, which yielded an error about not being able to find installable ISAM. My experience with that error leads me to believe that it isn't progress.

bondwithzenith Grasshopper Points: 15 More actions · Answer 12

Did you find a solution for it? I am struck with the same problem and am not able to resolve it for days now.

If its zip or some issue with xlsx file then how could it work on a local drive.

So definitely some sort of permissions or access issue.

I too able to run xp_cmdshell dir on the network path and get the results but could not use ORS. (but ORS works on the same excel file if copied to local C: drive)

Really appreciate your comments as you have faced this problem before.

mzangwa SSC Rookie Points: 42 More actions · Answer 13

Elliott Whitlow: Old thread I know, but thank you so much. Didn't read past your post; as soon as I read it, I put on my "idiot" crown and then went to change the user to a domain user. All worked 100%. (This idiot crown is spiky though. 🙂 )

Brian Stover Mr or Mrs. 500 Points: 511 More actions · Answer 14

I realize this is an old thread, but I'm having the exact same problem sqldriver has described. I've tried all the troubleshooting steps in this thread and got the same results as sqldriver.

Has anyone found a solution to this?

Brian Stover Mr or Mrs. 500 Points: 511 More actions · Answer 15

One additional step I tried:

I'm trying to access an Excel 2010 file from network share using a UNC path. This query actually works against the network share when I'm logged onto the SQL server (via RDP with my credentials), but fails from SSMS on my machine (both on the same domain). The SQL Server is 2012 Standard on Windows 2008 R2 (not sure if that matters)

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=\\<Fully qualified server name>\<path to file>\Book1.xlsx','select * from [Sheet1$]')

When ran from SSMS 2016 on my machine (Windows 10) I get:

Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the 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've tried logging in as the AD account that the SQL service uses and confirmed that it can see and read files in the target directory. I've tried creating a new dummy excel file. Same problem. TSQL works when ran on the server, fails when ran locally on my machine from SSMS.