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.
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)?