How to save the stored procedure output to file when it's executed in SQL Server

Question

I want to save the stored procedure output directly to a file.

I have used the BCP command, but still its not saving the file.

This is the command using BCP:

bcp " SELECT TOP (1000) column FROM table " queryout "D:\spoutput.csv" -S servername -U username -P password -c -T 

Answer 1

I would use Powershell for this instead of BCP unless there's some feature of bcp that you specifically need it for. I may be in the minority, but I find bcp kind of clunky to work with. It's great for quickly loading data, but for more routine tasks like this, I prefer a different approach.

You can use Invoke-SqlCmd to execute any arbitrary SQL you want; in this case, a stored procedure. Then pipe the results of that to Export-Csv to directly write it to a file (you could also pipe it to ConvertTo-Csv if you wanted to work with the CSV more in Powershell).

I've found that most common things you can configure for outputting a file through bcp you can also do through PowerShell. And in fact, powershell can do some things bcp cannot such as handling quoting of fields.

# the server you want to run the proc on
$serverInstance = "localhost\lemur"
# the database the proc lives on
$database = "test"
# the path where you want the output file created
$outputFile = ".\output.csv"

# NOTE: You could do this in one go; I split it into multiple lines for clarity
$results = Invoke-SqlCmd -ServerInstance $serverInstance -Database $database -Query "exec dbo.GetDatabases"
$results | Export-Csv -Path $outputFile -Delimiter '|' -NoTypeInformation