BCP - Identify error rows

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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