Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2002
    Posts
    19

    Unanswered: BCP Error handling

    I am running some bcp copies through a Sql job. I am copying 35 tables in individual steps. However, sometimes the bcp step fails to copy the data, and I want the step to fail if the data is not copied properly. Is that possible? If so, how? Any help is greatly appreciated.
    Last edited by rpeoplesjr; 03-03-05 at 17:56.

  2. #2
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121
    Are your BCP statements in a stored procedure?

  3. #3
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121
    Here is something I hope will help:

    declare
    @FILENAME_C nvarchar(255),
    @sqlstmt2 nvarchar(255),
    @outputcatch varchar(1000)

    SET @FILENAME_C= 'c:\it.txt'
    SET @SQLSTMT2='BCP "SELECT claimnumber from demo.dbo.wcclm1" queryout '+@FILENAME_C+' /c /t, /r \n /U demo /P demo /S '+@@servername
    EXEC @outputcatch=MASTER.DBO.XP_CMDSHELL @SQLSTMT2, no_output --@outputcatch=OUTPUT

    select @outputcatch

    Note that this sample has a simple select statement. If I change claimnumber to clamnumber @outputcatch is 1 due to failure. Put it back to claimnumber and it returns 0 which indicates success.

    Just keep looking at @outputcatch and if you get a 1 RETURN.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •