BCP with No Output & No xp_cmdshell

My problem was that I needed to create a cvs file with an older version of a SQL Server database.

My plan was to use the BCP utility to create the file, and run it though our enterprise scheduling system.  By default, BCP outputs a line for every one thousand rows.  This could be bad for the the enterprise scheduler console output if the file will contain millions of rows.

I quick search of the internet only recommends using xp_cmdshell to resolve my issue.  However I am not going to enable xp_cmdshell for security reasons.

Resolution:

Because BCP is going to run in a command prompt window, I would be able to redirect the STDOUT to NUL.

1>NUL

Example:

bcp "select * from [DW2008].[DBO].[Sales]" queryout "C:\Reports\sales.csv" -T -t"," -c 1>NUL

This will send any of the BCP output to NUL, however if their is an error with command line syntax, it would be displayed.

Limitation:  Any SQL Server errors are considered normal output, and will be suppressed.  If there is a concern for SQL errors, it would be better to redirect the output to a file.