BCP Handling

BCP Handling

• jphoekstra

  Hey how do I capture this in a table... betn thanks !!!
  
  

• Tim Goldstein

  thanks a lot..

  any idea how to trap the number of rows that were transffered during the bcp out process...

  
  
  

• PetterL

  In addition, you can also specify an error file for your bcp command, and then check it afterwards for any content:

  DECLARE @FileName varchar(50),
          @bcpCommand varchar(2000)
  
  SET @FileName = 'E:\TestBCPOut.txt'
  SET @bcpCommand = 'bcp "SELECT * FROM pubs1..authors ORDER BY au_lname" queryout "'
  SET @bcpCommand = @bcpCommand + @FileName + '" -c -U -P -ee:\myBCPerror.txt'

  declare @ret int
  EXEC @ret=master..xp_cmdshell @bcpCommand

  CREATE TABLE #bcperr(input varchar(255) null)
  INSERT #bcperr(input) EXEC master..xp_cmdshell 'type e:\myBCPerror.txt'
  IF EXISTS(SELECT * FROM #bcperror WHERE input IS NOT NULL)
  RAISERROR('There was an error with the BCP command.', 16, 1)
  DROP TABLE #bcperr

  You can also just do a SELECT * FROM #bcperr to get the actual error rows.

  
  
  

• learning_in_texas

  declare @ret int
  EXEC @ret=master..xp_cmdshell @bcpCommand
  
  
  
  
  

• Gil tap

  If you also specify an output file with the -o parameter, you can 'parse' it and grab the line with the rows total in it.

  declare @rc int
  EXEC @rc = master..xp_cmdshell 'find c:\myBCPoutput.txt "rows copied"'

  output                       
  ------------------------------
  NULL
  ---------- C:\MYBCPOUTPUT.TXT
  23 rows copied.
  NULL

  (4 row(s) affected)
  

  =;o)

  /Kenneth

  
  

• megachuck1

  Execute osql from where

  And which of the above

  Can you elaborate a bit on what you want to do

  /Kenneth

  
  

• Steve Heffern

  Here's an example.

  create table #bcpResult
  ( result varchar(50) null )
  go

  declare @rc int

  insert #bcpResult
  EXEC @rc = master..xp_cmdshell 'find c:\myBCPoutput.txt "rows copied"'
  go
  select * from #bcpResult where result like '%rows copied%'
  go
  drop table #bcpResult
  go
  

  =;o)
  /Kenneth

  
  

• Fabim

  Is is possible...........
  
  

• Miguel Angel Sánchez

  This is very helpful... I am using command line utilities only. How do I execute this using osql

  
  
  
• BCP Handling