Hi

I been following this tutorial on how to do a linq to sql batch insert.

http://www.codeproject.com/KB/linq/BulkOperations_LinqToSQL.aspx

However I have a datetime field in my database and I keep getting this error.

System.Data.SqlClient.SqlException was unhandled Message="Conversion failed when converting datetime from character string." Source=".Net SqlClient Data Provider"
ErrorCode=-2146232060 Class=16
LineNumber=7 Number=241
Procedure="spTEST_InsertXMLTEST_TEST" Server="" State=1 StackTrace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

I am not sure why when I just take the datetime in the generated xml file and manually copy it into sql server 2005 it has no problem with it and converts it just fine.

This is my SP

CREATE PROCEDURE [dbo].[spTEST_InsertXMLTEST_TEST](@UpdatedProdData nText)
AS 
 DECLARE @hDoc int   

 exec sp_xml_preparedocument @hDoc OUTPUT,@UpdatedProdData 

 INSERT INTO UserTable(CreateDate)
 SELECT XMLProdTable.CreateDate
    FROM OPENXML(@hDoc, 'ArrayOfUserTable/UserTable', 2)   
       WITH (                
                CreateDate datetime
            ) XMLProdTable

EXEC sp_xml_removedocument @hDoc

C# code

using (TestDataContext db = new TestDataContext())
{
   UserTable[] testRecords = new UserTable[1];
   for (int count = 0; count < 1; count++)
   {
      UserTable testRecord = new UserTable()
      {
         CreateDate = DateTime.Now                     
      };

      testRecords[count] = testRecord;
   }

   StringBuilder sBuilder = new StringBuilder();
   System.IO.StringWriter sWriter = new System.IO.StringWriter(sBuilder);
   XmlSerializer serializer = new XmlSerializer(typeof(UserTable[]));
   serializer.Serialize(sWriter, testRecords);
   db.spTEST_InsertXMLTEST_TEST(sBuilder.ToString());
}

Rendered XML Doc

<?xml version="1.0" encoding="utf-16"?>
<ArrayOfUserTable xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <UserTable>
    <CreateDate>2010-05-19T19:35:54.9339251-07:00</CreateDate>
  </UserTable>
</ArrayOfUserTable>