SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


OpenRowSet works locally but not over networked drives


OpenRowSet works locally but not over networked drives

Author
Message
sqldriver
sqldriver
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2290 Visits: 2536
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
sqldriver
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2290 Visits: 2536
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
mister.magoo
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10981 Visits: 7891
Could be an xlsx. They are zips also.

MM


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




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Elliott Whitlow
    Elliott Whitlow
    SSC-Insane
    SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

    Group: General Forum Members
    Points: 24978 Visits: 5314
    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/

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

    CEWII
    mister.magoo
    mister.magoo
    SSChampion
    SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

    Group: General Forum Members
    Points: 10981 Visits: 7891
    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);




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • sqldriver
    sqldriver
    SSCrazy
    SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

    Group: General Forum Members
    Points: 2290 Visits: 2536
    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
    bondwithzenith
    Forum Newbie
    Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

    Group: General Forum Members
    Points: 1 Visits: 3
    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
    mzangwa
    Grasshopper
    Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

    Group: General Forum Members
    Points: 10 Visits: 102
    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
    Brian Stover
    Valued Member
    Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

    Group: General Forum Members
    Points: 65 Visits: 191
    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
    Brian Stover
    Valued Member
    Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

    Group: General Forum Members
    Points: 65 Visits: 191
    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.

    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search