bcp utility - how to get Number of rows copied

• Jignesh Patel-441242

  SSC Veteran

  Points: 297

  More actions

  March 21, 2009 at 11:47 pm

  #132427

  Hi all,

  Is it possible to get number of rows copied to a file once bcp utility (command) is executed using xp_cmdshell?

  I have written a script to generate a file containing selected records. When I execute this script from SQL Server Management Studio, the result pane displays following result in grid with one column 'output':

  output

  ------

  NULL

  Starting copy...

  1000 rows successfully bulk-copied to host-file. Total received: 1000

  1000 rows successfully bulk-copied to host-file. Total received: 2000

  1000 rows successfully bulk-copied to host-file. Total received: 3000

  NULL

  3359 rows copied.

  Network packet size (bytes): 4096

  Clock Time (ms.) Total : 46 Average : (73021.74 rows per sec.)

  NULL

  ------

  I can see that 3359 records were written in the file.

  I want to get that number in my script.

  Please let me know if that is possible or not.

  Thank you.

• Florian Reischl

  SSC-Dedicated

  Points: 37301

  More actions

  March 22, 2009 at 6:05 am

  #964097

  Hi

  This should work:

  DECLARE @output TABLE (id INT IDENTITY, command NVARCHAR(256))

  INSERT INTO @output

  EXECUTE master.sys.xp_cmdshell 'bcp YourCriteria'

  SELECT *

  FROM @output

  WHERE id = (SELECT MAX(id) - 3 FROM @output)

  Instead of the rows id you also can search for "% rows copied".

  Greets

  Flo

• Jignesh Patel-441242

  SSC Veteran

  Points: 297

  More actions

  March 24, 2009 at 1:47 pm

  #965565

  Thank you,

  Your suggestion worked for me!

  Jignesh