Recently I have being using SQLMetal that generates dbml and the backing code. I encountered a problem that was obtuse, apparently good code was failing to execute. The message from Linq was "Row not found or changed".
Launching SQL Profiler showed me the SQL
exec sp_executesql N'UPDATE [WebApp].[OperationInfo] SET [WorkflowResultsID] = @p8 WHERE ([MachineVersionID] = @p0) AND ([OperationID] = @p1) AND ([ExecutionDate] = @p2) AND ([OperationType] = @p3) AND ([WorkflowResultsID] IS NULL) AND ([ConfigurationResultsID] IS NULL) AND ([SequenceResultsID] IS NULL) AND ([ExecutionGuids] IS NULL) AND ([WorkRequestID] IS NULL) AND ([LastStep] = @p4) AND ([TrafficRuleCommitStatus] = @p5) AND ([CreatedUTC] = @p6) AND ([LastModifiedUTC] = @p7)', N'@p0 bigint,@p1 bigint,@p2 datetime,@p3 smallint,@p4 varchar(12),@p5 smallint,@p6 datetime,@p7 datetime,@p8 bigint',@p0=1,@p1=6,@p2='2010-08-05 09:56:47.2470000',@p3=1,@p4='Requirements',@p5=0,@p6='2010-08-05 09:56:47.2470000',@p7='2010-08-05 09:56:47.2470000',@p8=3
Manually executing, I got:
Msg 241, Level 16, State 1, Line 4
Conversion failed when converting date and/or time from character string.
Looking at the TSQL, I said huh???!!!???
I then tried an experiment.
Declare @p2 datetime
set @p2='2010-08-05 09:56:47'
set @p2='2010-08-05 09:56:47.247'
set @p2='2010-08-05 09:56:47.2470'
set @p2='2010-08-05 09:56:47.24700'
set @p2='2010-08-05 09:56:47.247000'
set @p2='2010-08-05 09:56:47.2470000'
The red ones gave the above error messages.
Tried it with:
Declare @p2 datetimeoffset
set @p2='2010-08-05 09:56:47'
set @p2='2010-08-05 09:56:47.247'
set @p2='2010-08-05 09:56:47.2470'
set @p2='2010-08-05 09:56:47.24700'
set @p2='2010-08-05 09:56:47.247000'
set @p2='2010-08-05 09:56:47.2470000'
Set @p2='2010-08-05 09:56:47.2470000'
Set @p2='2010-08-05 09:56:47.2470000'
Everything worked....
then with
Declare @p2 datetime2
set @p2='2010-08-05 09:56:47'
set @p2='2010-08-05 09:56:47.247'
set @p2='2010-08-05 09:56:47.2470'
set @p2='2010-08-05 09:56:47.24700'
set @p2='2010-08-05 09:56:47.247000'
set @p2='2010-08-05 09:56:47.2470000'
Set @p2='2010-08-05 09:56:47.2470000'
Set @p2='2010-08-05 09:56:47.2470000'
Everything worked....
In the C# code, I had changed the code from
OperationInfo.CreatedUTC=DateTime.Now;
to
OperationInfo.CreatedUTC=DateTime.UtcNow;
This change was causing the problem!
The solutions are
- OperationInfo.CreatedUTC=DateTime.Now.ToUniversalTime();
- Changing the database column types.
No comments:
Post a Comment