CODEWITHSUNDEEP
c#sql-serversqlbulkcopy
Max Favilli
Max Favilli

Reputation: 6449

sqlbulkcopy exception in production only

I am having a very strange behavior. When I test the application locally, everything works fine, with IIS Express, latest release of VisualStudio 2015 Update 3.

But the live application running on IIS8 on Windows Server 2012, sometimes it doesn't.

Sometimes I get the following exception:

System.InvalidOperationException: The given value of type String from the data source cannot be converted to type nvarchar of the specified target column. ---> System.InvalidOperationException: String or binary data would be truncated.

I log the data processed by the application, if take the exact same data file generating the exception in production, and I run it on my local machine (connected to production DB), it works great. No exception.

The real reason for the exception cannot be the length of the DB column, because I checked the data, field by field. And by the way if it was a problem of string length, I would get the exception locally too.

This is the datatable definition:

  var dt = new DataTable();
  dt.Columns.Add("gid", typeof(int));
  dt.Columns.Add("season", typeof(string));
  dt.Columns.Add("brand", typeof(string));
  dt.Columns.Add("brandid", typeof(string));
  dt.Columns.Add("pattern", typeof(string));
  dt.Columns.Add("width", typeof(string));
  dt.Columns.Add("ratio", typeof(string));
  dt.Columns.Add("diameter", typeof(string));
  dt.Columns.Add("load", typeof(string));
  dt.Columns.Add("speed", typeof(string));
  dt.Columns.Add("isrunflat", typeof(bool));
  dt.Columns.Add("price", typeof(decimal));
  dt.Columns.Add("original_descr", typeof(string));
  dt.Columns.Add("source", typeof(string));
  dt.Columns.Add("createdon", typeof(DateTime));
  dt.Columns.Add("updatedon", typeof(DateTime));
  dt.Columns.Add("eulgas", typeof(string));
  dt.Columns.Add("eulrai", typeof(string));
  dt.Columns.Add("eulnc", typeof(string));
  dt.Columns.Add("euldb", typeof(string));

And this is the database table definition:

CREATE TABLE [dbo].[grabbed_zero](
    [gid] [bigint] NOT NULL,
    [season] [nvarchar](50) NOT NULL,
    [brand] [nvarchar](50) NOT NULL,
    [brandid] [nvarchar](50) NOT NULL,
    [pattern] [nvarchar](100) NOT NULL,
    [width] [nvarchar](10) NOT NULL,
    [ratio] [nvarchar](10) NOT NULL,
    [diameter] [nvarchar](10) NOT NULL,
    [load] [nvarchar](10) NOT NULL,
    [speed] [nvarchar](10) NOT NULL,
    [isrunflat] [int] NOT NULL,
    [price] [decimal](10, 2) NOT NULL,
    [original_descr] [nvarchar](100) NOT NULL,
    [source] [nvarchar](50) NOT NULL,
    [createdon] [datetime] NOT NULL,
    [updatedon] [datetime] NOT NULL,
    [EULGAS] [nvarchar](10) NOT NULL,
    [EULRAI] [nvarchar](10) NOT NULL,
    [EULNC] [nvarchar](10) NOT NULL,
    [EULDB] [nvarchar](10) NOT NULL,
)

I am banging my head against every wall I can find, but still no ideas comes out about what may be the cause for this.

Anyone has any idea?

Upvotes: 4

Views: 441

Answers (2)

Max Favilli
Max Favilli

Reputation: 6449

What I found out at the end is the problem was originated by json deserialization, which was behaving differently in release mode than debug mode.

The data files being bulk copied were originally in json format.

When I found out, I just made a nuget Update-Package -reinstall for newtonsoft package (which was already updated to latest release) and it solved the issue.

Was unrelated to sqlbulkcopy.

Upvotes: 2

Hadi
Hadi

Reputation: 37348

Try adding string dataColumn using the following code:

DataColumn d = new DataColumn(fieldname, typeof(string));
d.MaxLength = 10; //Specify max length
dt.Columns.Add(d);

So the exception will be thrown when inserting data into dataTable and you will specify the column

Upvotes: 0

Related Questions