CODEWITHSUNDEEP
.netvb.netexceptionsqlbulkcopy
Bacon
Bacon

Reputation: 823

SqlBulkCopy Exception Doesn't Contain Column Name

If the DataTable passed into to SqlBulkCopy.WriteToServer() contains values that will fail to convert, I get an InvalidOperationException that displays the value that failed to convert. The column name doesn't display anywhere in the exception from what I could tell. I'm trying to log the column name and value when this occurs. Is there a way to retrieve this information?

System.InvalidOperationException: The given value of type SqlDecimal from the 
    data source cannot be converted to type decimal of the specified target column. 
    ---> System.InvalidOperationException: The given value of type SqlDecimal from 
    the data source cannot be converted to type decimal of the specified target column. 
    ---> System.ArgumentException: Parameter value '9999999999999999999999999999.00' 
    is out of range.

Upvotes: 4

Views: 3015

Answers (1)

Gary Wright
Gary Wright

Reputation: 2469

I think the short answer is no, there is no way to get SqlBulkCopy to tell you which column is causing the problem.

In your case, I think you need to add your own validation to all decimal columns in your .net code. The number that is failing has 28 digits to the left of the decimal place, so any SQL column with a precision less than decimal(28, 0) would fail this insert. Take a look at this question regarding validating .net decimals for SQL:

Validating decimal in C# for storage in SQL Server

In a more general sense, you can get a similar error if your .net DataTable column ordering does not match your SQL table. In a table that has columns in the following order VarcharColumn,IntColumn,DecimalColumn, even though you have named the columns, the below table would fail to insert:

        DataTable tbl = new DataTable();
        tbl.Columns.Add("IntColumn", Type.GetType("System.Int32"));            
        tbl.Columns.Add("VarcharColumn", Type.GetType("System.String"));                        
        tbl.Columns.Add("DecimalColumn", Type.GetType("System.Decimal"));

You would need to either re-order the C# columns above or provide column mappings:

        SqlBulkCopy blkCpy = new SqlBulkCopy(conn);            
        blkCpy.ColumnMappings.Add("VarcharColumn", "VarcharColumn");
        blkCpy.ColumnMappings.Add("IntColumn", "IntColumn");
        blkCpy.ColumnMappings.Add("DecimalColumn", "DecimalColumn");

One other trick is if you are debugging a batch where you know one particular row is failing to insert, you can use the SQLRowsCopied event of SQLBulkCopy to identify which row is causing the problem. For example:

        SqlBulkCopy blkCpy = new SqlBulkCopy(conn);
        blkCpy.SqlRowsCopied += blkCpy_SqlRowsCopied;
        blkCpy.NotifyAfter = 1;

Then in the event handler:

    static void blkCpy_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
    {
        Console.WriteLine("Copied {0} rows", e.RowsCopied);
    }

Upvotes: 3

Related Questions