BCP to Export Query to Pipe Delimited File

Question

Post reply

BCP to Export Query to Pipe Delimited File

AMCiti

SSC-Addicted

Points: 496
More actions
January 4, 2021 at 8:27 pm
Go to Answer
#3827265
Dear Group:
I have a requirement to create a SQL Job that exports a query to a Pipe / Vertical bar delimited file (|) and save it on a network drive in either *.txt or *.csv format. Right now, I am just trying to get this to work inside SSMS, calling the BCP command and exporting the stored procedure in the proper format to the network location, but not able to get it working.
I have been researching this and there are two methods for this.
1. Use the export data wizard to create a job and schedule that to run. But this method, if we need to make changes, I believe we cannot change the SSIS package that is created so we lose flexibility
2. Use the BCP command to export the file.
I greatly prefer to use option #2, the BCP command, but I am having problems. I just cannot seem to get the syntax correct and hoping someone could show me what I am missing:
This is my command: Exec master..xp_cmdshell 'bcp EXEC [dbo].[usp_Report_1123] ''7786'' -t| out \\networkDrive\Reports\REPORT_1123\report1123.csv -T'
But I get the following messages:
output
'out' is not recognized as an internal or external command,
operable program or batch file.
NULL
The stored procedure does work and returns data. The network path, if I enter it into my computer, finds the path. But I am not sure what I am missing and hoping someone could help.

Ken McKelvey

SSCoach

Points: 18859

January 5, 2021 at 12:47 pm

Answer

#3827545

I do this quite a bit with a powershell step in a sql agent job.

Something like:

$SQLparams = @{
  'ServerInstance' = '.';
  'Database' = 'YourDB';
  'ErrorAction' = 'Stop';
  'Query' = 'SELECT Cols FROM YourView' }

$MyPath = "YourPath\YourFile.txt"

Invoke-Sqlcmd @SQLparams |
  ConvertTo-Csv -Delimiter '|' -NoTypeInformation |
  % {$_ -replace '"',''} |
  Out-File -Encoding  UTF8 $MyPath

#Get rid of UTF-8-BOM encoding
$MyFile = Get-Content $MyPath
$Utf8NoBomEncoding = New-Object System.Text.UTF8Encoding $False
[System.IO.File]::WriteAllLines($MyPath,$MyFile,$Utf8NoBomEncoding)

All of my output files are less than 20,000 lines. If your output file is greater you may need to look at a batch size to save on memory.

This reply was modified 3 years, 11 months ago by Ken McKelvey.

January 5, 2021 at 2:41 pm

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ratbak Ten Centuries Points: 1382 More actions · Answer 1

bcp utility: out is used for table or view source. queryout is required for query (e.g., stored procedure) source.

What does "7786" represent? If it's a parameter of the stored procedure, I don' t think it should be quoted separately.

Jeffrey Williams SSC Guru Points: 90305 More actions · Answer 2

If you use the Import/Export wizard to create a package - you have the option of saving that package and can edit/change the package using SSDT (Visual Studio).

However - if you do not have Integration Services installed and have not installed and configured the Integration Services catalog then you would not be able to deploy the package and automate the process.

You could also use Powershell to accomplish this - but you probably need to upgrade: https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/export-csv?view=powershell-7.1

If you are going to need to do this more often then SSIS would make sense - if this is not something you will be doing often then BCP or Powershell would be better options, and I would schedule those in Windows Task Scheduler outside of SQL Server.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeffrey Williams SSC Guru Points: 90305 More actions · Answer 3

stephen.aa wrote:

HEY JEFFREY (jeffrey-williams-3188), HOW ABOUT FUCKING STOPPING SENDING ME 25 EMAILS A DAY FOR SOMETHING I NEVER SIGNED UP FOR!!!

I am not sending you anything - if you are receiving emails from SQL Server Central then you should work with them about why they are sending you emails. Yelling at me isn't going to help.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

ZZartin SSC-Dedicated Points: 30921 More actions · Answer 4

All of my output files are less than 20,000 lines. If your output file is greater you may need to look at a batch size to save on memory.

There's also nothing stopping you from simply calling bcp from your powershell script. Which can be helpful if you want to add in any kind of file management on top of it.