SqlBulkCopy.WriteToServer (DataTable) ( SqlBulkCopy DataTable) RRS feed

  • Question


    I have a sql server 2005 table w/ a column type of nvarchar(50)


    DataTable dt = new DataTable();
        DataColumn dc;
        DataRow dr;

        dc = new DataColumn();
        dc.DataType = System.Type.GetType("System.String");
        dc.ColumnName = "c1";
        dc.Unique = false;


    when i run this



    i get the following exception

    {"The given value of type String from the data source cannot be converted to type nvarchar of the specified target column."}


    Inner Exception

    String of Binary data would be truncated.


    How can i fix this?


    the System.Type.GetType("System.String");  does not have System.NVarChar and

    System.Data.SqlDbType.NVarChar does not work either


    Tuesday, December 4, 2007 1:30 PM

All replies

  • Can you post more code? I'd like to see what you're doing in the bulk copy itself. I'm going to assume that you've checked your column mappings on the BulkCopy object and they match up with the DataTable and that you've specified the correct destination.
    Tuesday, December 4, 2007 1:41 PM
  • ok... here's how i set the destination table...



    string connectionString = "Data Source=xxx;Initial Catalog=xxx;Integrated Security=True";


    System.Data.SqlClient.SqlBulkCopy bulkcopy = new System.Data.SqlClient.SqlBulkCopy(connection)


    bulkcopy.DestinationTableName = "table1";




    the problem is how do i set the

    Datatable's DataColumn.DataType to match the DestinationTable's Datatype of nvarchar ????????




    Tuesday, December 4, 2007 2:35 PM

    ok, here's the solution...


    It seems like the datatable has to contain every column that the Table contains !!!! 


    So even if your only inserting a few columns the DataTable MUST contain all columns in the table...

    Wednesday, December 5, 2007 1:41 PM
  • Actually, no you don't. That's why I specifically asked if you check your column mappings. SqlBulkCopy has a property called ColumnMappings that you HAVE to use if the two tables have different structures. If you fail to use it, you will get the error you were getting. All you have to do is specify source column name and destination column name in the ColumnMappings property and it will move only those columns you've specified.


    Check this out:


    Wednesday, December 5, 2007 3:25 PM