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

admin管理员组

文章数量:1320185

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

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

• sql-server
• powershell
• t-sql
• sql-server-2012

Share Improve this question Follow edited Jan 20 at 8:31 jarlh 44.8k88 gold badges5050 silver badges6767 bronze badges asked Jan 19 at 21:06 PrettyCodePrettyCode 2766 bronze badges 9

• Does your server account (or proxy account if you run under such) have access to the provided share? – siggemannen Commented Jan 19 at 21:11
• The posted UNC paths aren't consistent but they need to have a doubled backslash before the server name, i.e.: \\ServerName\ShareName\... maybe more folders ...\filename.csv. – AlwaysLearning Commented Jan 19 at 21:14
• 1 RDP to ServerName and find the folder which is shared and take properties and check Share settings, the AD account should be able to access both the share and also have write permissions to the "Security" of the share. You can try running dir \\sharename from xp_cmdshell to see what kind of errors you get – siggemannen Commented Jan 19 at 22:35
• 2 Export as UTF8 BOM are you trying to open this in Excel? First, using xp_cmdshell is almost never a good solution, second just as there's Export-CSV there are scripts and commands that export to xlsx directly. This avoids date and number formatting issues – Panagiotis Kanavos Commented Jan 20 at 8:51
• 1 And why does the client ask for this? You have to find the real requirement. The only case where this is needed, is if they intend to open those files by double-cllicking and opening a default application like Excel. In which case it's better to save to Excel directly. – Panagiotis Kanavos Commented Jan 21 at 7:59

 |  Show 4 more comments

1 Answer 1

Sorted by: Reset to default 1

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.}

本文标签：