none
OPENROWSET Failure When Access FILESTREAM FileTable RRS feed

  • Question

  • Hello,

    Wonder if anyone has any ideas about the problem we're facing. Got a webservice thats writing Excel files to a SQL 2014 database using filetable (via Filestream). Everything seems to be working well.

    But when we try to read these files via OPENROWSET using the ACE driver, using the share pathname - we get the generic failure message.

    Msg 7399, Level 16, State 1, Line 19
    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 19
    Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

    Through Windows explorer I can read these Excel files just by selecting them from the share.

    If I manually move the file, via windows explorer from the Filetable 'share' to another folder - then I can successfully read the file via OPENROWSET

    SELECT * 
    FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=NO;IMEX=1;Database=E:\TEMP\TestFile1.xls', 'SELECT F1,F2,F3,F4,F5,F6,F12,F7,F8,F13,F9,F10,F11 FROM [sheet1$]');

    FILESTREAM been given full access on the server and on the database via its configuration interface(s). Just seems everything works perfectly - except can't read the Excel files from the share ;-(

    Would welcome any ideas anyone has. Thanks.

    AJ



    Thursday, April 21, 2016 4:05 PM

Answers

  • I was contacted offline whether I had any official reference for the statement above, which I admittedly wrote from memory.

    I don't use filtetables in real-life myself, but I have encountered questions like this before. I searched my archives and found that there is no official statement backing what I said. And thinking a little more, I think the limitation is another one.

    If you look at https://msdn.microsoft.com/en-us/library/gg492089.aspx#funclist you can see what Wildows file operations that are supported with the file share for a file table and which are not. And if you look at a related topic, you will learn that specifically learn that memory-mapped files are not supported.

    As long we are only doing operations packaged into a program and not writing the Windows file I/O these restrictions are a bit mumbo-jumbo to us, but it's seems reasonable to assume these restrictions strike here. To muddle the waters further when I have played with this using BULK INSERT and OPENROWSET(BULK), I have found that I cannot access a file which is in a filetable share which is on the same instance I'm running the bulk operation from, but I can access it from a different instance on the same machine. This has enforced the idea that there is a block against things going around in circles, but a more likely explanation may be that file share for the other instance is seen as non-local and the BULK provider does not employ memory-mapping in this case.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, April 23, 2016 10:48 AM

All replies

  • It is as simple as this: You cannot access the file share for the filetable from inside SQL Server. Not with OPENROWSET, not through xp_cmdhsell or anything else.

    Thursday, April 21, 2016 9:35 PM
  • I was contacted offline whether I had any official reference for the statement above, which I admittedly wrote from memory.

    I don't use filtetables in real-life myself, but I have encountered questions like this before. I searched my archives and found that there is no official statement backing what I said. And thinking a little more, I think the limitation is another one.

    If you look at https://msdn.microsoft.com/en-us/library/gg492089.aspx#funclist you can see what Wildows file operations that are supported with the file share for a file table and which are not. And if you look at a related topic, you will learn that specifically learn that memory-mapped files are not supported.

    As long we are only doing operations packaged into a program and not writing the Windows file I/O these restrictions are a bit mumbo-jumbo to us, but it's seems reasonable to assume these restrictions strike here. To muddle the waters further when I have played with this using BULK INSERT and OPENROWSET(BULK), I have found that I cannot access a file which is in a filetable share which is on the same instance I'm running the bulk operation from, but I can access it from a different instance on the same machine. This has enforced the idea that there is a block against things going around in circles, but a more likely explanation may be that file share for the other instance is seen as non-local and the BULK provider does not employ memory-mapping in this case.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, April 23, 2016 10:48 AM
  • for anyone who face this problem:

    if you change your root path to your server IP, it works

    for example:

    instead of this:

     select * from openrowset ('Microsoft.ACE.OLEDB.12.0', 
     'Excel 12.0;Database=\\DESKTOP-DSSKTI6\MSSQLSERVER\XtmERP\Files\Projects\38626\a1.xlsx;HDR=yes'
     ,  'select * from [Sheet1$]')

    use this:

     select * from openrowset ('Microsoft.ACE.OLEDB.12.0', 
     'Excel 12.0;Database=\\192.168.1.10\MSSQLSERVER\XtmERP\Files\Projects\38626\a1.xlsx;HDR=yes'
     ,  'select * from [Sheet1$]')

    serverIP: 192.168.1.10


    Gmar

    Saturday, September 14, 2019 11:47 PM