SqlBulkCopy Failed to obtain column collation information for the destination table

collation sqlbulkcopy

Question

I am getting this error when I try to write rows to a table via SqlBulkCopy and a DataTable object.

Before going any further, let me say that I am aware of the Microsoft KB article below. Every post out there regarding this error references that article. However, I DO NOT have dots in my table or schema name. The table exists in the default schema for the user account, so the table name alone should suffice.

http://support.microsoft.com/kb/944389

Here is the code which performs the bulk write operation:

SqlConnection cn = new SqlConnection(cs);
cn.Open();

SqlTransaction tr = cn.BeginTransaction();

try
{
    using (SqlBulkCopy copy = new SqlBulkCopy(cn, SqlBulkCopyOptions.Default, tr))
    {
        copy.DestinationTableName = CircCountTableName;
        copy.ColumnMappings.Add("CirculationRangeID", "CirculationRangeID");
        copy.ColumnMappings.Add("GeographyID", "GeographyID");
        copy.ColumnMappings.Add("CircCountModelID", "CircCountModelID");
        copy.ColumnMappings.Add("Monday", "Monday");
        copy.ColumnMappings.Add("Tuesday", "Tuesday");
        copy.ColumnMappings.Add("Wednesday", "Wednesday");
        copy.ColumnMappings.Add("Thursday", "Thursday");
        copy.ColumnMappings.Add("Friday", "Friday");
        copy.ColumnMappings.Add("Saturday", "Saturday");
        copy.ColumnMappings.Add("Sunday", "Sunday");
        copy.ColumnMappings.Add("DataSource", "DataSource");
        copy.ColumnMappings.Add("DataSourceID", "DataSourceID");
        copy.ColumnMappings.Add("CreateDate", "CreateDate");
        copy.ColumnMappings.Add("LastUpdateDate", "LastUpdateDate");
        copy.ColumnMappings.Add("LastUpdateUser", "LastUpdateUser");

        copy.WriteToServer(circCounts);

        tr.Commit();
    }
}
catch (Exception ex)
{
    tr.Rollback();
}
finally
{
    cn.Close();
}

Has any one else encountered this problem when the cause was something other than dot notation? I suspect it's a permissions issue, but I'm not entirely convinced.

Thank you.

Popular Answer

I have no idea why this would make a difference, but when I gave the account used to connect to the database the right to Grant the View Definition permission - under Database Properties / Permissions - the error went away.




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why