SQL select query not returning records, even though the data insert operation is successful.
We have a table [dbo].[Login] in our database where data is inserted using the below stored procedure. The data is inserted by a windows service written in dot net. It has been working until last month and the records in the table were growing several 100 thousands. Recently, customer complained that the data in not reflected in the reports. Hence, we have investigated and found that, there is no insertion error from the stored procedure, but when we select the table no new records have been returned. We then tried setting the SQL transaction isolation level to Read un-Committed then we are able to see the newly inserted records from the table. We have reviewed the insertion stored procedure but didn't find anything suspicious.
CREATE PROCEDURE [dbo].[gp_Login_Insert]
(
@ID int OUTPUT, /*-- the automatically generated value will be returned as an output parameter*/
@ReaderHardWareID int,
@CardID int,
@EmpID int,
@UEmpID varchar(50),
@Type varchar(15),
@DateTime datetime,
@CreatedByEmployeeId int,
@CreatedDate datetime,
@ModifiedByEmployeeId int,
@ModifiedDate datetime,
@version_ts timestamp
)
AS /* ** Add a row to the Login table */
if not exists(select 1 from Login where EmpID=@EmpID and DateTime=@DateTime)
begin
declare @eventCount int
select @eventCount=count(*) from [login]
where EmpID=@EmpID and [DateTime] between DATEADD(dd, 0, DATEDIFF(dd, 0, @DateTime)) and @DateTime
if(@eventCount<500)
Begin
BEGIN TRANSACTION;
BEGIN TRY
INSERT INTO [Login]
( [ReaderHardWareID], [CardID], [EmpID],[UEmpID],[Type], [DateTime], [CreatedByEmployeeId], [CreatedDate], [ModifiedByEmployeeId], [ModifiedDate] )
VALUES
( @ReaderHardWareID, @CardID, @EmpID,@UEmpID,@Type, @DateTime, @CreatedByEmployeeId, GETUTCDATE(), @ModifiedByEmployeeId, GETUTCDATE() )
SET @ID = (SELECT SCOPE_IDENTITY())
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER()
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
End
END
Select Query on table returns the following results:
select transaction isolation level read uncommitted
select COUNT(*) from Login where CalFlag is null
returns 59341
select transaction isolation level read committed
select COUNT(*) from Login where CalFlag is null
returns 0
Expected Result: Since SQL server default transaction isolation is Read Committed it should return the correct row count 59341 in Read Committed mode. But it is not doing so.
SQL server version: Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 >2017 17:04:49 Copyright (C) 2017 Microsoft Corporation Express Edition (64-bit) >on Windows 10 Pro 10.0 (Build 18362: )
Thanks in advance, Ujjal Sarmah
