SqlBulkCopy got exception : "Received an invalid column length from the bcp client for colid ."

Question

I have code using SqlBulkCopy to clone a lot of tables, it used to work before, but very weird, recently got exception

Received an invalid column length from the bcp client for colid

I have search this exception and still not solve my problem.

sqlBulkCopy.WriteToServer(reader) will raise this exception if a table has two continous columns which are both of type Char(1) or nvarchar(nn), and both have NULL. Sometime, changing the SqlBulkCopy.BatchSize makes it work, but many times, it will not.

After simplify, I have test case as follow, and it is reproduceable on two servers:

Create a table like below: (tested on SQL Server 2012 SP 4 and SQL Server 2016 SP2)

 IF OBJECT_ID('dbo.TestTable', 'U') IS NOT NULL
     DROP TABLE dbo.TestTable;

 CREATE TABLE [dbo].[TestTable]
 (
     [value2] [char](1) NULL,
     [value1] [char](1) NULL
 ) ON [PRIMARY]
 GO


 DECLARE @i int = 0

 WHILE @i < 262
 BEGIN
     SET @i = @i + 1

     INSERT INTO [dbo].[TestTable]([value2], [value1]) 
     VALUES (null, null)
 END

C# console (.net framework 4.7) code as below

 class Program
 {
     // [change here] 
     static string sourceConn = @"Server={YourServer};Database={YourDatabase};User ID={userYourName};Password={yourPassword};connect timeout=15";

     static void Main(string[] args)
     {
         CopyTable(sourceConn, sourceConn, "TestTable", "testTableBAK");
         Console.ReadLine();
     }

     static void CopyTable(string sConnSource, string sConnDest, string sTableSource, string sTableDest)
     {
         if (IsTableExist(sConnDest, sTableDest))
         {
             RunNonQuerySQL(sConnDest, "DROP TABLE " + sTableDest);
             Console.WriteLine($"existing table  {sTableDest} dropped");
         }

         CopySchema(sConnDest, sTableSource, sTableDest);

         using (SqlConnection connSource = new SqlConnection(sConnSource))
         {
             connSource.Open();
             SqlCommand cmd = new SqlCommand();
             cmd.Connection = connSource;
             cmd.CommandText = "SELECT * FROM " + sTableSource;

             // using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(sConnDest, SqlBulkCopyOptions.KeepNulls | SqlBulkCopyOptions.KeepIdentity))
             using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(sConnDest))
             {
                 // sqlBulkCopy.BatchSize = 1380; // this optional setting will work if set value smaller than 1397 for testTable on my new server (SQL server  13.0.5102.14)
                 // sqlBulkCopy.BatchSize = 261; // this optional setting will work if set value smaller than 261 for testTable on 2 older server (SQL server  11.0.7001)
                 sqlBulkCopy.DestinationTableName = sTableDest;
                 SqlDataReader reader = cmd.ExecuteReader();

                 try
                 {
                     // exception here 
                     sqlBulkCopy.WriteToServer(reader);  
                     Console.WriteLine("table copied");
                 }
                 catch (SqlException ex)
                 {
                     Console.WriteLine(ex.Message);
                 }

                 sqlBulkCopy.Close();
             }
         }
     }

     static bool IsTableExist(string sConn, string sTableName)
     {
         bool result = false;

         using (SqlConnection conn = new SqlConnection(sConn))
         {
             conn.Open();
             SqlCommand cmd = new SqlCommand();
             string[] s = sTableName.Split('.');

             if (s.Length > 1)
             {
                 cmd.CommandText = "select count (*) as counter from information_schema.tables where table_name = '" + s[1] + "' and TABLE_SCHEMA='" + s[0] + "'";
             }
             else
             {
                 cmd.CommandText = "select count (*) as counter from information_schema.tables  where table_name = '" + sTableName + "'";
             }
             cmd.Connection = conn;
             var count = Convert.ToInt32(cmd.ExecuteScalar());
             result = count > 0;
         }

         return result;
     }

     static bool RunNonQuerySQL(string sConn, string sSQL)
     {
         bool result = false;

         using (SqlConnection conn = new SqlConnection(sConn))
         {
             conn.Open();

             SqlCommand cmd = new SqlCommand();
             cmd.CommandText = sSQL;
             cmd.Connection = conn;

             var count = cmd.ExecuteNonQuery();
             result = true;
         }

         return result;
     }

     static public bool CopySchema(string sConn, string sTableSource, string sTableDest)
     {
         return RunQuerySQL(sConn, "select * into " + sTableDest + " from " + sTableSource + " where 1=2");
     }

     static public bool RunQuerySQL(string sConn, string sSQL)
     {
         using (SqlConnection conn = new SqlConnection(sConn))
         {
             conn.Open();

             SqlCommand cmd = new SqlCommand();
             cmd.CommandText = sSQL;
             cmd.Connection = conn;

             SqlDataReader reader = cmd.ExecuteReader();

             if (reader.Read())
             {
                 return true;
             }
             else
             {
                 return false;
             }
         }
     }
 }

erm, RunNonQuerySQL() always returns true or could throw. that doesn't make any sense. — Mitch Wheat, Jan 12 '21 at 05:15
If you have any columns that are VARCHAR smaller than 4, watch out for NULL in your data getting misinterpreted as the 4char string "NULL" — Mitch Wheat, Jan 12 '21 at 05:17
https://stackoverflow.com/questions/10442686/received-an-invalid-column-length-from-the-bcp-client-for-colid-6 — Mitch Wheat, Jan 12 '21 at 05:18
Aside: you should be disposing your commands and readers with `using` — Charlieface, Jan 12 '21 at 09:31
Hi, Mitch, I had read the link you give above, but it is different from my case, it is import from csv file and data length is longer than column defined. — Joe Tian, Jan 12 '21 at 16:17

score 0 · Answer 1 · answered Mar 16 '22 at 14:12

I just experienced this error.

I tried to insert a string with a lenght of 5 into a table column with a definition of "varchar(4)".

The error message in my case was: "Received an invalid column length from the bcp client for colid 2".

"Colid 2" refered to the second column of the row (DataRow) that was part of the DataTable which I used as parameter for the call to the SqlBulkCopy.WriteToServer(DataTable table) method.

The solution in my case was to add validation code that checks the lenght of the strings in my input data before trying to call SqlBulkCopy.WriteToServer().

SqlBulkCopy got exception : "Received an invalid column length from the bcp client for colid ."

1 Answers1