I have some code that raises an exception.
SqlConnection con; con = new SqlConnection(connectionStr); SqlCommand cmd = con.CreateCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.CommandText = @"select [ID], [VERSION_ID], '08/26/2015 09:33:24:717 AM' as [Added_Dt], [Loc_ID] as Column1 from dbo.Source where Added_Dt between CONVERT(DATETIME,'08/24/2015 09:25:43:283 AM') and CONVERT(DATETIME,'08/24/2015 09:25:43:283 AM')"; cmd.CommandTimeout = con.ConnectionTimeout; con.Open(); SqlDataReader rdr = cmd.ExecuteReader(); System.Data.SqlClient.SqlBulkCopy bcp = new SqlBulkCopy(destconnectionStr, SqlBulkCopyOptions.UseInternalTransaction); bcp.BatchSize = (int)Global.BatchWriteThreshold; bcp.DestinationTableName = destinationTableName; bcp.NotifyAfter = (int)Global.BatchWriteThreshold; bcp.SqlRowsCopied += new SqlRowsCopiedEventHandler(bcp_SqlRowsCopied); bcp.WriteToServer(rdr);
destination table(SQL Server 2005): ID (int, not null), VERSION_ID (int, not null),Added_Dt(DATETIME,not null),Column1(varchar2(50) ,not null)
When used with SqlBulkCopy, this throws an error, however when used with ado.net in single updates or straight from SQL Management Studio, it works without any problems. I discovered that by removing the milliseconds, the bulkcopy executed well, but I like to have the milliseconds there. How can the problem be solved?
'08/24/2015 09:25:43:283 AM'
is a date string that is invalid. the last
ought to be a
'08/24/2015 09:25:43.283 AM'
Of course, this converts flawlessly in SSMS:
SELECT CONVERT(DATETIME,'08/24/2015 09:25:43:283 AM');
You must provide the precise exception message if correcting that doesn't work.