2

I am inserting data into SQL 2005 using SQLXMLBulkLoad.SQLXMLBulkload.3.0

My data table has following column: objDataTable.Columns.Add("TaskDateTime", System.Type.GetType("System.DateTime"))

My bulk insert schema has following definition: <xsd:element name="DepartureTime" type="xsd:date" /> (Using xmlns:xsd="http://www.w3.org/2001/XMLSchema")

And I am getting 'Invalid character value for cast specification' exception.

Any advice?

Tim S. Van Haren
  • 8,861
  • 2
  • 30
  • 34
Usman Akram
  • 271
  • 6
  • 16
  • The problem is with 'T' in temp XML file. The date is stored as 2010-01-01T12:29+00:00 If I change the XML to 2010-01-01 12:29+00:00 and execute, it runs successfully. – Usman Akram Oct 27 '10 at 14:43

2 Answers2

3

Solved!.

Changed column type from: objDataTable.Columns.Add("TaskDateTime", System.Type.GetType("System.DateTime")) to objDataTable.Columns.Add("TaskDateTime", System.Type.GetType("System.String"))

and I am storing my value as .ToString("yyyy-MM-dd HH:mm") + ":00+00:00" in the data table. when creating XML file on disk, it simply writes it as string and the schema file for bulk insert reads it as datetime.

Usman Akram
  • 271
  • 6
  • 16
  • If you're using SQLXML to create your database schema from an XSD, change the XSD dateTime elements to string and run. Unfortunately, SQL Server (or .NET for that matter) doesn't handle ISO 8660-1 date/time strings very nimbly. It's still very useful for an 80% schema build with some manual intervention afterwards. – ssamuel May 29 '13 at 22:25
1

The problem is blanks in the source columns........ this is what is causing the invalid character value for cast specification.

subhash
  • 276
  • 1
  • 1