Decrease the output of bcp export

Question

In our project we are using bcp command to export about million of rows and logging the output to an output file. For bcp import I can control the output of bcp command by using the -b switch which specifies the no. of rows to be imported in a batch. The output which is something like this :

Starting copy...
1000 rows sent to SQL Server. Total sent: 1000
1000 rows sent to SQL Server. Total sent: 2000
1000 rows sent to SQL Server. Total sent: 3000
1000 rows sent to SQL Server. Total sent: 4000
1000 rows sent to SQL Server. Total sent: 5000
1000 rows sent to SQL Server. Total sent: 6000
1000 rows sent to SQL Server. Total sent: 7000
1000 rows sent to SQL Server. Total sent: 8000
1000 rows sent to SQL Server. Total sent: 9000
1000 rows sent to SQL Server. Total sent: 10000
1000 rows sent to SQL Server. Total sent: 11000
1000 rows sent to SQL Server. Total sent: 12000
SQLState = 22001, NativeError = 0

can easily be reduced by increasing the number sent with -b switch :

Starting copy...
10000 rows sent to SQL Server. Total sent: 10000
SQLState = 22001, NativeError = 0

12406 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 75     Average : (165413.3 rows per sec.)

But for bcp export I can't control the output and for a million rows the log becomes too big, For eg. the below command

bcp  Temp.dbo.TestTable out outdata.txt -t , -f file.fmt -S Server -U user-P password -m 10

outputs this :

Starting copy...
1000 rows successfully bulk-copied to host-file. Total received: 1000
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 11 for SQL Server]Warning: BCP import with a format file will convert empty strings in delimited columns to NULL.
1000 rows successfully bulk-copied to host-file. Total received: 2000
1000 rows successfully bulk-copied to host-file. Total received: 3000
1000 rows successfully bulk-copied to host-file. Total received: 4000
1000 rows successfully bulk-copied to host-file. Total received: 5000
1000 rows successfully bulk-copied to host-file. Total received: 6000
1000 rows successfully bulk-copied to host-file. Total received: 7000
1000 rows successfully bulk-copied to host-file. Total received: 8000
1000 rows successfully bulk-copied to host-file. Total received: 9000
1000 rows successfully bulk-copied to host-file. Total received: 10000
1000 rows successfully bulk-copied to host-file. Total received: 11000
1000 rows successfully bulk-copied to host-file. Total received: 12000
1000 rows successfully bulk-copied to host-file. Total received: 13000
1000 rows successfully bulk-copied to host-file. Total received: 14000
1000 rows successfully bulk-copied to host-file. Total received: 15000
1000 rows successfully bulk-copied to host-file. Total received: 16000
1000 rows successfully bulk-copied to host-file. Total received: 17000
1000 rows successfully bulk-copied to host-file. Total received: 18000
1000 rows successfully bulk-copied to host-file. Total received: 19000
1000 rows successfully bulk-copied to host-file. Total received: 20000
1000 rows successfully bulk-copied to host-file. Total received: 21000
1000 rows successfully bulk-copied to host-file. Total received: 22000

I have tried passing -b switch with bcp out but it always exports them in batch of 1000 and filtering the rows by greping or seding them will take too much time. Thanks for your help.

Answer 1

There seems to be no solution for this within bcp. There is a workaround, however; package your bcp command line into an xp_cmdshell statement and specify the no_output option:

EXEC xp_cmdshell "bcp  Temp.dbo.TestTable out outdata.txt -t , -f file.fmt -S Server -U user-P password -m 10", no_output

Source: click here