Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

OpenRowSet works locally but not over networked drives Expand / Collapse
Author
Message
Posted Wednesday, July 10, 2013 2:14 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 11:13 AM
Points: 467, Visits: 1,883
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...?

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.
Post #1472346
Posted Wednesday, July 10, 2013 2:17 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 11:13 AM
Points: 467, Visits: 1,883
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.
Post #1472350
Posted Wednesday, July 10, 2013 2:35 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:19 PM
Points: 1,796, Visits: 5,799
Could be an xlsx. They are zips also.

MM


  • MMGrid Addin
  • MMNose Addin


  • 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

  • Post #1472366
    Posted Wednesday, July 10, 2013 3:25 PM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Thursday, October 9, 2014 1:02 PM
    Points: 6,032, Visits: 5,284
    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
    Post #1472378
    Posted Wednesday, July 10, 2013 3:31 PM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Yesterday @ 4:19 PM
    Points: 1,796, Visits: 5,799
    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

    MM


  • MMGrid Addin
  • MMNose Addin


  • 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

  • Post #1472380
    Posted Wednesday, July 10, 2013 4:24 PM


    SSC-Addicted

    SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

    Group: General Forum Members
    Last Login: 2 days ago @ 11:13 AM
    Points: 467, Visits: 1,883
    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.
    Post #1472394
    Posted Tuesday, February 11, 2014 8:27 AM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Tuesday, February 11, 2014 8:49 AM
    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.
    Post #1540235
    « Prev Topic | Next Topic »

    Add to briefcase «««123

    Permissions Expand / Collapse