Click here to monitor SSC
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
Mr or Mrs. 500
Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)

Group: General Forum Members
Points: 562 Visits: 2492
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
Mr or Mrs. 500
Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)

Group: General Forum Members
Points: 562 Visits: 2492
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
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: 2264 Visits: 7820
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
    SSCertifiable
    SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

    Group: General Forum Members
    Points: 6208 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
    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: 2264 Visits: 7820
    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
    Mr or Mrs. 500
    Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)

    Group: General Forum Members
    Points: 562 Visits: 2492
    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
    Forum Newbie
    Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

    Group: General Forum Members
    Points: 2 Visits: 87
    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. :-) )
    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