question

PadmanabhanVenkatesh-6789 avatar image
0 Votes"
PadmanabhanVenkatesh-6789 asked ErlandSommarskog answered

BCP - Identify error rows

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-generaldotnet-csharp
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks. Is there any other option to get the exact row which has caused the error in BCP ?

0 Votes 0 ·
ErlandSommarskog avatar image ErlandSommarskog PadmanabhanVenkatesh-6789 ·

Maybe you can be a bit more specific about what errors you are seeing? To wit, I can really map what you say to your command, but maybe I'm missing something.

0 Votes 0 ·
TimonYang-MSFT avatar image
0 Votes"
TimonYang-MSFT answered TomPhillips-1744 commented

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi.
Thanks for the reply. Below is the error details from the initial logs:

Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 17 for SQL Server]Warning: BCP import with a format file will convert empty strings in delimited columns to NULL.
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
SQLState = 22001, NativeError = 8152
Error = [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]String or binary data would be truncated.

3247 rows copied.
Network packet size (bytes): 4096

0 Votes 0 ·
TomPhillips-1744 avatar image TomPhillips-1744 PadmanabhanVenkatesh-6789 ·

Your c# code is using queryout to create a file. However, this error is on importing the file.

This error indicates you are trying to put a string or value which is too big for the database to handle. You will be much happier with bulk insert/bcp if you always import into a "staging" table with varchar(max) fields, and then use INSERT/UPDATE/MERGE to do the data conversions and copy to your target table. You will have much more control over errors and how to handle them.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered PadmanabhanVenkatesh-6789 commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi.
Below is the query :

SELECT
QUOTENAME(REPLACE(REPLACE(COMMENT,CHAR(13),''),CHAR(10), ''),'""') ,
QUOTENAME('¸','""') AS DELIMITS
FROM TableName1 WITH (NOLOCK)
WHERE SEQ_NUM IN (
SELECT SEQ_NUM FROM Table2 WITH (NOLOCK)
WHERE CAST(LAST_UPDATE as date) >= '2020-12-01' and
CAST(LAST_UPDATE as date) <= '2020-12-31' )

The column comment is of type nvarchar(max)

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.