Tom Goff's .Net Musings

Tidbits of information regarding .Net, C#, and SQL Server.

SqlBulkCopy Bug

with one comment

While working on a new feature for my company’s flagship product that uses SqlBulkCopy, I encountered a strange InvalidOperationException exception. I was only receiving this exception when copying data to certain tables. The exact message of the exception was:

Failed to obtain column collation information for the destination table. If the table is not in the current database the name must be qualified using the database name (e.g. [mydb]..[mytable](e.g. [mydb]..[mytable]); this also applies to temporary-tables (e.g. #mytable would be specified as tempdb..#mytable).

After doing some searching, I found this blog by Marco Russo. I found that my tables that were failing indeed had dots in their name. Mr. Russo’s blog led me to his bug report to Microsoft, which was created and confirmed as fixed in their “latest internal build” in April of 2006. I was confused because I always keep up with updates released by Microsoft and I thought for sure they had released the fix by now. To my dismay, they have not released the fix yet, and apparently lots of people (myself now included) are upset with Microsoft because they are so slow to release these types of fixes.

My next step was to try to find a work around that did not require me to change the table name. Unfortunately, the SqlBulkCopy class is sealed up and internally it uses other sealed classes. Since we can’t wait for the next service pack, my only option was to replace dots with underscores. Even if Microsoft were to release the service pack tomorrow, I would still have to implement the workaround since not all our users would apply the service pack immediately after being released.

One user reported on the Microsoft bug report that wrapping the table name with square brackets (e.g. [My.TableName]) fixed the issue for him. I’m not sure that he is correct though, because it did not work for me. Using SQL Profiler, I was able to narrow down the problem to a call to “sp_tablecollations”. SqlBulkCopy passes the table name as “.My.TableName” to this stored procedure. The stored procedure would then have to assume that “My” is the owner and “TableName” is the table name. Keep in mind that “[My.TableName]” was passed as the destination table and SqlBulkCopy stripped the square brackets that surrounded the table name, when building the call to this stored procedure.

Setting aside the fact that these types of bugs take a long time to get fixed, I really wish there was a way for me to “fix” it. Since the class is sealed (and there are no virtual methods), I cannot derive from it and customize it’s behavior. If the class did not use internal classes in it’s implementation, then I could build my own version of SqlBulkCopy. Alas, that is not possible either. As I understand it, Microsoft marks classes as internal or sealed because they don’t have time to test every class (and thus can’t guarantee their functionality) or because they may want to change the signatures of the methods or classes.

I think that .Net bugs are bit more frustrating for me because it is easier to see the underlying code and the actual bug, but I just can’t reach into the code a fix it. Although, I’m sure I’ll survive somehow. If you would like to see this bug in action, then check out my sample project here.

Advertisements

Written by Tom

April 9, 2007 at 1:01 pm

Posted in .Net, Ado.Net

One Response

Subscribe to comments with RSS.

  1. More frustrating if using schema. For example, [DataLoad.RAW].TableName fails. I have to replace the . with _

    Too bad.

    Joe

    September 24, 2009 at 2:19 am


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: