I am trying to create and write out a file to a new directory on the same file server, but different file share.

I have researched questions already on Stack Overflow and other forums, nothing has yet to work.

I am using SQL Server Management Studio v 19.0 preview 2.

I have full control on the share that is failing “gillis$” file share from below, along with modify, Read & Execute, List folder contents, Read, Write.

I have only modify, Read & Execute, List folder contents, Read, Write, on the share that is working. Same exact code, just different destination share on the same file server.

When I run using bcp (code will be below) I receive 2 errors
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 17 for SQL Server]Unable to open BCP host data-file

Here is the basics of my code. ##FinalChem is a global temp table. The commented out line to public$epicordata works fine. the line gillis$LisaTest fails. We use this code all the time to the public share to create files

declare @CMD varchar(8000)
declare @FileName varchar (100)
declare @FilePathOut varchar(100)

select @FileName = ‘Chemical Use Report.csv’

select @FilePathOut = ‘server1gillis$LisaTest’ — errors
–Select @FilePathOut = ‘server1public$epicordataEHS Chemical Report’ works fine

SELECT @CMD = ‘bcp “SELECT * FROM ##FinalChem” queryout “‘+ @FilePathOut + @FileName + ‘” -c -t, -T -S’ + @@SERVERNAME;

exec master..xp_cmdshell @CMD