BCP - Identify error rows

Padmanabhan, Venkatesh 241 Reputation points
2021-07-14T04:02:28.273+00:00

Hi.

I am using the BCP command in my c# console application to transfer the data from table to a delimited text file.
The process works fine, but errors out at time due to data issues . I am trying to find , if there is a way to capture the row containing the error in a log file.

Below is the existing code:

public static void ExecuteProcess(string fileName, string Query, string servername, string dbinstance)
        {
using (var process = new Process())
              {

                  process.StartInfo.UseShellExecute = false;
                  process.StartInfo.CreateNoWindow = true;
                  process.StartInfo.RedirectStandardError = true;
                  process.StartInfo.RedirectStandardOutput = true;
                  process.StartInfo.FileName = "BCP";

                  process.StartInfo.Arguments = "\"" + Query + " \" queryout " + "\"" + fileName.Trim() + "\"" + " -T -S " + servername + " -d " + dbinstance + " -b 500 -c -C 65001 -t~";           

                  process.Start();

                  string outputString = process.StandardOutput.ReadToEnd();
                  string errorString = process.StandardError.ReadToEnd();
}
              }

How to capture the error row from table ? Thanks

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,132 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,534 questions
0 comments No comments
{count} votes

4 answers

Sort by: Most helpful
  1. Erland Sommarskog 104.3K Reputation points MVP
    2021-07-14T07:53:29.743+00:00

    You can use the -e option for BCP to divert error messages to a file, and this will give you more information.

    However, that is mainly applicable when importing files. You seem to be running a queryout operation, and the errors you would get here would be with the query itself. In that case you would have to parse the output from BCP.


  2. Timon Yang-MSFT 9,576 Reputation points
    2021-07-14T08:01:49.703+00:00

    Unfortunately, BCP itself does not seem to support such operations.

    But I have an idea. The outputString we get when the query succeeds and fails is different. We can judge whether an error has occurred based on its content.

    If it is determined that an error has occurred, if it is not the first row of data errors, the outputString will contain "n rows copied".

                    string outputString = process.StandardOutput.ReadToEnd();  
                    string errorString = process.StandardError.ReadToEnd();  
      
                    var index = outputString.IndexOf("rows copied");  
                    var rowNumber = outputString.Substring(index - 2, 1);  
    

    After we get the value of n, then the (n+1)th row is the wrong row, and we can use ADO.Net to get the data of that row.

    Select *  
    From   
    (  
        Select   
          Row_Number() Over (Order By id) As RowNum  
        , *  
        From Table_4  
    ) t2  
    Where RowNum = rowNumber+1   
    

    In this way, we can get the first error row, and then we can continue to execute the bcp command, but the sql can be modified to select * from table where id> x.

    If it is the first line of error, the method is similar. According to the specific string to determine this is the case, query the first line.


    If the response is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


  3. Erland Sommarskog 104.3K Reputation points MVP
    2021-07-15T22:15:55.85+00:00

    Ok, so this is not an issue with BCP as such, but with the query. BCP knows as little as you and I know what is wrong, it only relays the error error message.

    Since I don't see the query, I can't say what the problem may be, but since BCP spews out a couple "1000 rows successfully bulk-copied to host-file. ", I can tell that it is has started to produce rows.

    We would need to see the query to understand where things may be going wrong.


  4. Erland Sommarskog 104.3K Reputation points MVP
    2021-07-19T08:50:11.863+00:00

    You get that error, because quotename() does not accept input over 8000 bytes. But that error message is only the tip of the iceberg. Quotename() is a great function, but it is designed only to handle identifiers, so if input is more than 128 characters, you get NULL back. So even you would not get that error message, your file would be missing a lot of data.

    Here is a function you can use instead:

    CREATE FUNCTION quotestring(@str nvarchar(MAX)) RETURNS nvarchar(MAX) AS
    BEGIN
       DECLARE @ret nvarchar(MAX),
               @sq  nchar(1) = ''''
       SELECT @ret = replace(@str, @sq, @sq + @sq)
       RETURN(@sq + @ret + @sq)
    END
    
    0 comments No comments