Export stored procedure results as CSV (UTF 8 BOM format) to external server from SQL Server 2012

I am able to export the stored procedure results as CSV to the local drive on SQL Server, but I am having difficulty exporting the results to an external server (or shared drive location).

I am able to use only BCP command from command prompt but it has limited functionality. Also, the BCP command only exports ASCII. I want to export as UTF-8 BOM format.

I have implemented the following methods to export the results as csv:

The sp_configure 'xp_cmdshell' is already turned ON

1. Use BCP command from SQL Server:

   declare @sql varchar(8000) 
   
   select @sql = 'BCP "EXEC [dbName].[dbo].[StoredProcedureName]
                      @AreaID=NULL" queryout "\\serverlocation\Test\FileExport_Test_1.CSV"-T
                      -S SourceServerName -c -t,'     
   exec master..xp_cmdshell @sql
   

   This command is exporting csv when exporting to local server location

   I get this error when I execute the above command:

   Error = [Microsoft][SQL Server Native Client 11.0]Unable to open BCP host data-file

2. Use BCP command from Command Prompt.

   C:\Windows\System32>BCP "EXEC [dbName].[dbo].[StoredProcedureName] @AreaID=NULL" queryout "\\serverlocation\Test\FileExport_Test_1.CSV"-T
         -S SourceServerName -c -t
   

   The file is exported to the external server location but I don't want to use this method as I want to use dynamic filename and pass dynamic parameter values to the stored procedure.

3. Use Powershell:

   PS SQLSERVER:\> $filename = "\\serverlocation\Test\FileExport_Test_1"
   Invoke-Sqlcmd -Query "EXEC [dbName].[dbo].[StoredProcedureName] 
          @AreaID=NULL" -ServerInstance "SourceServerName" |
          Export-Csv -Path "$filename.csv" -NoTypeInformation
   

   I get the following error:

   Export-Csv : Cannot open file because the current provider (Microsoft.SqlServer.Management.PSProvider\SqlServer) cannot open a file.
   At line:3 char:1

   • Export-Csv -Path "$filename.csv" -NoTypeInformation
   • CategoryInfo : InvalidArgument: (:) [Export-Csv], PSInvalidOperationException
   • FullyQualifiedErrorId : ReadWriteFileNotFileSystemProvider,Microsoft.PowerShell.Commands.ExportCsvCommand

4. Export the CSV from SSRS as Windows Share. But the CSV is adding double quotes around a field which has quotes. So, cant use this method to export CSV

I am not very familiar with SSIS and learning SSIS will take some time and I have to provide the solution in very short time frame. :(

Please help

Re 2 (use from cmd.exe, aka Command Prompt):

I want to use dynamic filename and pass dynamic parameter values to the stored procedure.

Analogous to your PowerShell solution attempt, you can set a cmd.exe variable that you can reference in your command, by enclosing the variable name in %...%:

:: From cmd.exe / a batch file
set "filename=\\serverlocation\Test\FileExport_Test_1"
BCP "EXEC [dbName].[dbo].[StoredProcedureName] @AreaID=NULL" queryout "%filename%.csv" -T -S SourceServerName -c -t

• Note: In the absence of a -C argument, the character encoding used for writing the output file is implied by the current console window's code page, as reflect in [Console]::OutputEncoding from PowerShell; that code page defaults to the legacy system locale's OEM code page, such as CP437 on US-English systems.

  • While -C 65001 would result in UTF-8 encoding, it would not include a BOM, however.

Re 3 (use of Invoke-SqlCmd from PowerShell):

Your only problem is that you're invoking your command while the current location is set to SQLSERVER:\, which prevents you from using a UNC path as-is, because it is - perhaps surprisingly - interpreted as a relative path, i.e. relative to the provider underlying the current location.^[1]

The simplest solution is to use a provider prefix in front of your UNC path, namely that of the FileSystem provider, FileSystem:::

# From PowerShell
Invoke-Sqlcmd -Query "EXEC [dbName].[dbo].[StoredProcedureName] 
       @AreaID=NULL" -ServerInstance "SourceServerName" |
       Export-Csv -Path "FileSystem::$filename.csv" -NoTypeInformation -Encoding utf8

• Note: -Encoding utf8 was added to ensure that the output CSV file is UTF-8 with BOM in Windows PowerShell.
  By contrast, in PowerShell (Core) 7 you'd have to use -Encoding utf8BOM

^{[1] See this answer for additional information.}