0

I felt in love with the filestream capacities of SQL Server giving the possibility to see only files and skip the complexity of hierarchies.

When I import a xml file in a table using openrowset I get this error:

INSERT INTO tblXmlFiles(IdTache, idSuiviTrt, XMLFileName, XMLData)
SELECT 0, 0, '\OMEGA\XMLFiles\XMLFiles\XMLFiles\Test1.xml', CONVERT(XML, BulkColumn)
FROM OPENROWSET(BULK '\OMEGA\XMLFiles\XMLFiles\XMLFiles\Test1.xml', SINGLE_BLOB) AS x;

Msg 4861, Level 16, State 1, Line 16 Cannot bulk load because the file "\OMEGA\XMLFiles\XMLFiles\XMLFiles\Test1.xml" could not be opened. Operating system error code 50(The request is not supported.).

Completion time: 2023-01-26T13:48:40.3212373+01:00

It kind of spoils all the fun because both functionalities are incorporated into SQL Server but apparently the SQL Server team does not let them work together.

On the web I found that the issue might be related to rights but it is impossible to give rights to an filestream network drive. The tab is missing.

Can anyone help me out, please?

I tried to import the same file on a local disk and there it works seamlessly. I tested it on 2019 and 2022. Same result. Also Windows 2019/2022.

rvunen
  • 1
  • Maybe i'm a bit slow, but if your file is on filestream, you can access the file content without needing to OPENROWSET? It's in your Filestream table – siggemannen Jan 26 '23 at 15:04
  • Just SELECT from OPENROWSET to see that this issue is unrelated to Filestream. – David Browne - Microsoft Jan 26 '23 at 15:47
  • @DavidBrowne-Microsoft actually, it is a bit interesting. I have some filestream shares on own server, and i can't openrowset from them either. But regular shares work. Also, the error is 50 and not something other like access denied etc – siggemannen Jan 26 '23 at 16:33
  • You can't open Filestream through Windows directly. Only File Table supports that. – David Browne - Microsoft Jan 26 '23 at 16:48
  • You can still browse to the filestream share (it's usually prefixed with mssqlserver) and you can modify the files with notepad etc. there. So i would say it's a weird limitation that sql server can't do read from the same share. It's not mentioned in the docs either – siggemannen Jan 27 '23 at 10:13
  • The code is ok. Its the stack overflow website that makes from two \ only one. I am using a FileTable. It uses the FileStream functionality. There is no way you can change the permissions in Windows. I am using an sysadmin account in SQL Server and Windows. SQL Server is running under the standard NT Service\MSSQLSERVER account. If you try to add users to the FileTable only public and guest turns up. – rvunen Jan 27 '23 at 12:11
  • The problem is that I have to process 2.2 million XML files some larger than 150 MB and the FileTable takes away the complexity of walking trough directories and unwanted files. You just query for the XML files and you have all the info. Directories are required because Windows NTFS/ReSF goes down above 6000 files in one folder. Both, FileTables and OpenRowset are SQL Server functionalities but they don't work together, which they should because it is all SQL Server. No Oracle or PostgreSQL. SQL Server only. – rvunen Jan 27 '23 at 12:18

1 Answers1

1

Sounds like permissions issue indeed. You need to check that the account your MSSQL instance is running under has all necessary permissions on the shared folder (XMLFiles on OMEGA). For that (provided it's Windows) go to the server, open Properties of the folder and then Sharing and Security tabs. Also is your network path correct (server name prefixed with '\' vs '\\' and 'XMLFiles' repeated 3 times)?

Roman
  • 31
  • 3
  • Note that you need _both_ permissions on the share and permissions on the folder the share points to. – David Browne - Microsoft Jan 26 '23 at 15:46
  • The code is ok. Its the stack overflow website that makes from two \ only one. I am using a FileTable. It uses the FileStream functionality. There is no way you can change the permissions in Windows. I am using an sysadmin account in SQL Server and Windows. SQL Server is running under the standard NT Service\MSSQLSERVER account. If you try to add users to the FileTable only public and guest turns up. – rvunen Jan 27 '23 at 12:11