Select the product you need help with

Error message when you use the SqlBulkCopy class to copy data from a resource to a data table in the .NET Framework 2.0: "System.InvalidOperationException: Failed to obtain column collation information for the destination table"

Article ID: 944389 - View products that this article applies to.

SYMPTOMS

In a Microsoft .NET Framework 2.0 application, you use the SqlBulkCopy class to copy data from a System.Data.DataTable to a table in Microsoft SQL Server. If the table name contains a dot character (.), you receive the following error message:

System.InvalidOperationException was unhandled
Message="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)."
Source="System.Data"

Back to the top | Give Feedback

CAUSE

This problem is caused by an issue in the System.Data.dll file.

Back to the top | Give Feedback

WORKAROUND

To work around this problem, avoid dot characters (.) in table names.

If you have to use a dot character (.) in a table name, use the dot character (.) as part of the SQL naming convention. For example, use a table name that resembles the following:

server.database.schema.tablename

Back to the top | Give Feedback

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

Back to the top | Give Feedback

MORE INFORMATION

Steps to reproduce the behavior

In the Northwind database, run the following SQL statement:

select * into [test.33] where 1 = 0
--Create a table named [test.33].

Create a Visual C# application, and then run the following code:

static void Main(string[] args)
        {
            SqlConnection con = new SqlConnection("Integrated Security=SSPI;Initial Catalog=Northwind;Data Source=.");
            con.Open();
            SqlConnection dst = new SqlConnection("Integrated Security=SSPI;Initial Catalog=Northwind;Data Source=.");
            dst.Open();
            SqlCommand com = new SqlCommand("select * from customers", con);
            SqlDataReader dr = com.ExecuteReader();
            SqlBulkCopy sbc = new SqlBulkCopy(dst);
            sbc.DestinationTableName = "Northwind.dbo.[Test.33]";
            sbc.WriteToServer(dr); // The failure occurs here.
            dst.Close();
            con.Close();
        }

Call stack information

at System.Data.SqlClient.SqlBulkCopy.AnalyzeTargetAndCreateUpdateBulkCommand(BulkCopySimpleResultSet internalResults)
       at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal()
       at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount)
       at System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader)

Back to the top | Give Feedback