none
SqlBulkCopy.WriteToServer succeeds or fails depending on the size of data?

    Question

  • Hello,

    I am trying to bulk copy some data from a text file to SqlServer. In my case, the table in SqlServer is simple. It has two columns: Symbol <nchar(5), Primary Key> and Company <nvarchar(50)>. Each row in the text file is Symbol and Company separated by a "#". Below is the code of my bulk copy:


            public static void StartImport(string sourceFile)
            {
                SqlBulkCopy bulkCopy = new SqlBulkCopy(connString_local, SqlBulkCopyOptions.TableLock);
                bulkCopy.DestinationTableName = "dbo.NasdaqSymbols";

                DataTable dt = CreateSymbolDataTable(sourceFile);

                bulkCopy.WriteToServer(dt);
            }

            private static DataTable CreateSymbolDataTable(string filePath)
            {
                DataTable dt = new DataTable();
                DataColumn dc;
                DataRow dr;

                dc = new DataColumn();
                dc.DataType = Type.GetType("System.String");
                dc.ColumnName = "Symbol";
                dc.Unique = true;
                dt.Columns.Add(dc);

                dc = new DataColumn();
                dc.DataType = Type.GetType("System.String");
                dc.ColumnName = "Company";
                dc.Unique = false;
                dt.Columns.Add(dc);

                StreamReader sr = new StreamReader(filePath);
                string input;
                while ((input = sr.ReadLine()) != null)
                {
                    string[] s = input.Split(new string[] { "#" }, StringSplitOptions.None);
                    dr = dt.NewRow();
                    dr["Symbol"] = s[0].Trim();
                    dr["Company"] = s[1].Trim();
                    dt.Rows.Add(dr);
                }
                sr.Close();

                return dt;
            }
     

    The problem is, I got the following exception when I tried to call my StartImport method (thrown from SqlBulkCopy.WriteToServer): System.InvalidOperationException: The given value of type String from the data source cannot be converted to type nvarchar of the specified target column. It turned out that the problem seems not to be String to nvarchar; because when I use a source text file which contains only about a dozen of rows, it works! I have no idea why SqlBulkCopy.WriteToServer works fine on a small set of data. Or is there something I overlooked?


     Thank you for time and help.


    Gary
     

    Wednesday, March 5, 2008 2:32 AM

Answers

  • OK. Now I know what the problem is. The length of the column "Company", which was nvarchar(50), is not big enough. So if there is a single record whose "Company" column length is bigger than 50, it'll cause the whole SqlBulkCopy.WriteToServer() fail, with the aforementioned exception.

    The lesson for me this time is: Don't be too concerned about saving database space by giving a "seemingly-enough" number as a column length. It is more important to make sure the column length is big enough to hold all the values.
    Wednesday, March 5, 2008 5:04 PM

All replies

  • OK. Now I know what the problem is. The length of the column "Company", which was nvarchar(50), is not big enough. So if there is a single record whose "Company" column length is bigger than 50, it'll cause the whole SqlBulkCopy.WriteToServer() fail, with the aforementioned exception.

    The lesson for me this time is: Don't be too concerned about saving database space by giving a "seemingly-enough" number as a column length. It is more important to make sure the column length is big enough to hold all the values.
    Wednesday, March 5, 2008 5:04 PM
  • That was extremely useful -- thanks very much for posting the answer when you found it!
    Wednesday, April 16, 2008 1:41 PM