admin管理员组文章数量:1319486
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
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
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.
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
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
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
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.
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
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
Share Improve this question edited Jan 20 at 8:31 jarlh 44.8k8 gold badges50 silver badges67 bronze badges asked Jan 19 at 21:06 PrettyCodePrettyCode 276 bronze badges 9 | Show 4 more comments1 Answer
Reset to default 1Re 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.
- While
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.
本文标签:
版权声明:本文标题:powershell - Export stored procedure results as CSV (UTF 8 BOM format) to external server from SQL Server 2012 - Stack Overflow 内容由网友自发贡献,该文观点仅代表作者本人, 转载请联系作者并注明出处:http://www.betaflare.com/web/1742059081a2418473.html, 本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容,一经查实,本站将立刻删除。
\\ServerName\ShareName\... maybe more folders ...\filename.csv
. – AlwaysLearning Commented Jan 19 at 21:14dir \\sharename
from xp_cmdshell to see what kind of errors you get – siggemannen Commented Jan 19 at 22:35Export as UTF8 BOM
are you trying to open this in Excel? First, usingxp_cmdshell
is almost never a good solution, second just as there'sExport-CSV
there are scripts and commands that export toxlsx
directly. This avoids date and number formatting issues – Panagiotis Kanavos Commented Jan 20 at 8:51