Databases
StackOverflow2013
Postsdbo
POHow can I set column type when using SqlBulkCopy to insert into a sql_variant column
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow can I set column type when using SqlBulkCopy to insert into a sql_variant column
    primarykey
    data
    text
    <p>I'm using SqlBulkCopy to insert/update from a .net DataTable object to a SQL Server table that includes a sql_variant column. However SqlBulkCopy insists on storing DateTime values put into that column as sql type 'datetime' when what I need is 'datetime2'. </p> <p>My DataTable is defined like this:</p> <pre><code>DataTable dataTable = new DataTable(); dataTable.Columns.Add(new DataColumn("VariantValue", typeof(object))); //this represents my sql_variant column </code></pre> <p>Then I throw some data in there that requires a 'datetime2' to store.</p> <pre><code>DataRow row = dataTable.NewRow(); row[0] = DateTime.MinValue; dataTable.Rows.Add(row); </code></pre> <p>And then I use SqlBulkCopy to get that data into Sql Server:</p> <pre><code>using (SqlBulkCopy bulk = new SqlBulkCopy(myConnection)) { bulk.DestinationTableName = "tblDestination"; bulk.WriteToServer(dataTable); } </code></pre> <p>My bulk copy will fail if a DateTime value is present in the data table that falls outside the range of the sql 'datetime' type (such as '1/1/0001'). That's why the column needs to be of type 'datetime2'.</p> <p>When you're writing normal insert statements that insert into a sql_variant column you can control what the type of the variant column is by using CAST or CONVERT. For example:</p> <pre><code>insert into [tblDestination] (VariantValue) values (CAST('1/1/0001' AS datetime2)) </code></pre> <p>Then if you were to display the actual type of the variant column like this:</p> <pre><code>SELECT SQL_VARIANT_PROPERTY(VariantValue,'BaseType') AS basetype FROM test </code></pre> <p>You'd see that indeed it is being stored as a 'datetime2'.</p> <p>But I'm using SqlBulkCopy and, as far as I know, there's no place to tell it that .net DateTime objects should be stored in columns of type 'datetime2' and not 'datetime'. There's no place on the DataTable object, that I know of, to declare this either. Can anyone help me figure out how to get this to happen?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USBowserKingKoopa
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow can I set column type when using SqlBulkCopy to insert into a sql_variant column
      1. USBowserKingKoopa
      1. VTBountyStart
    2. VO
      singulars
      1. POHow can I set column type when using SqlBulkCopy to insert into a sql_variant column
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POHow can I set column type when using SqlBulkCopy to insert into a sql_variant column
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload