SQL Server Stored Procedure - INSERT command - DateTime error sequence

Question

Procedure is sync, ok? But, how explain that.

I have a table with the fields (not specific like that):

ID    INT PRIMARY KEY IDENTITY, 
DATE  DATETIME NOT NULL

The insert executed by SP is: (This SP is call inside another one)

INSERT TABLE  VALUES ( SYSDATETIME()  )

BUT, the table values are:

ID | DATETIME  
6  | 2017-08-23 09:40:00.000
7  | 2017-08-23 09:39:46.690

How is it possible? The ID 6 is "the most up-to-date", not ID 7.

In our context we need to order by IDENTITY field. I cannot order the query by DateTime, because the others fields. E.g.:

ID | DATETIME                 | ACTION
6  | 2017-08-23 09:40:00.000  | Production Start
7  | 2017-08-23 09:39:46.690  | Production End

I executed the End action before the Start action. The timeline is wrong.

getdate() is used to get the time upto milliseconds where as SysDateTime used to get time up to Nanoseconds — user7715598
– user7715598, Commented Aug 23, 2017 at 13:05
@Srini131 The difference is between minutes first, SysDateTime and nanoseconds should not create any problem. — Vishwanath Dalvi
– Vishwanath Dalvi, Commented Aug 23, 2017 at 13:06
@Srini131 I think the user is asking why ID 6 has a time greater than ID 7, aside from the precision — Jacob H
– Jacob H, Commented Aug 23, 2017 at 13:06
@Claudio (Asker) Was by any chance server clock was reset during this time? Did you notice this only once or this is happening often. — Vishwanath Dalvi
– Vishwanath Dalvi, Commented Aug 23, 2017 at 13:08
This is not possible IMO. Did you accidently update the previous record, is there any other logic in the second sp? — Ross Bush
– Ross Bush, Commented Aug 23, 2017 at 13:09

Maurício Pontalti Neri · Accepted Answer · 2017-08-23 13:33:35Z

1

I suspect that your process had some concurrency hell.

That is the explanation from the MSDN. (better than my previous it)

Consecutive values within a transaction – A transaction inserting multiple rows is not guaranteed to get consecutive values for the rows because other concurrent inserts might occur on the table. If values must be consecutive then the transaction should use an exclusive lock on the table or use the SERIALIZABLE isolation level.

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property

edited Aug 23, 2017 at 13:33

answered Aug 23, 2017 at 13:21

Maurício Pontalti Neri

1,0801 gold badge10 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

17 Comments

Sean Lange Over a year ago

I think you have correctly identified the issue but your explanation is quite confusing.

Maurício Pontalti Neri Over a year ago

I agree with you Sean, I edit it, and I copied the explanation from MSDN.

Claudio Parra Over a year ago

Thanks @MaurícioPontaltiNeri

Claudio Parra Over a year ago

@MaurícioPontaltiNeri I changed my first SP including variable datetime .. DECLARE "@@CurrentDate" = SYSDATETIME () and pass to second one. The new insert is INSERT TABLE VALUES @@CurrentDate , but the result was the same. Is there another way to resolve that ?

sepupic Over a year ago

@Maurício Pontalti Neri, but his code INSERTs 1 only row, where are "Consecutive values within a transaction"?

|

Tab Alleman · Accepted Answer · 2017-08-23 13:08:10Z

0

Your issue is not reproducible. You are doing something other than what you describe.

I ran the following test:

CREATE TABLE dbo.TTest (
 [ID] int PRIMARY KEY IDENTITY(1,1)
, [DT] datetime NOT NULL
);


--run the following two steps manually multiple times
INSERT INTO dbo.TTest
VALUES (SYSDATETIME());

SELECT * FROM dbo.TTest;
--

DROP TABLE dbo.TTest

The result was:

ID  DT
1   2017-08-23 09:05:23.450
2   2017-08-23 09:05:36.397
3   2017-08-23 09:05:57.313

answered Aug 23, 2017 at 13:08

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

Comments

Claudio Parra · Accepted Answer · 2017-08-31 14:28:03Z

0

Error encountered !

The SQL Function SYSDATETIME "Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running." - https://learn.microsoft.com/en-us/sql/t-sql/functions/sysdatetime-transact-sql

On our server we found (at EventViewer logs) two routines that are synchronizing the server clock, conflicting each other.

PreviousTime 2017-08-31T12:03:03.947848200Z
NewTime 2017-08-31T12:02:06.821000000Z
ProcessId 0x854
ProcessName DSHO_SincPCnetV11.exe

PreviousTime 2017-08-31T12:21:02.471426500Z
NewTime 2017-08-31T12:21:59.544982100Z
ProcessId 0x484
ProcessName C:\Windows\System32\svchost.exe

answered Aug 31, 2017 at 14:28

Claudio Parra

114 bronze badges

Collectives™ on Stack Overflow

SQL Server Stored Procedure - INSERT command - DateTime error sequence

3 Answers 3

17 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

17 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related