How in the world did this happen?
In table [Workperiods], the [Id] jumped by 1000 …
Anyone else have this anomaly?
The only way I can think this could happen is with an scripted statement to set the SEED value for that Identity column …
Wow that is weird I can’t think of any logical reason.
I am going to try to fix this, because I use this WPID as the Z-number for tracking by my Accountant, and if I need to explain this jump, it probably won’t go well.
But first I need to know which tables will be affected by this glitch. So far, the only other table that I can find that contains [WorkPeriodId] is [PeriodicConsumptions].
@emre, any insight to this?
same thing happen when I clone print job and get error UseforPaidTickets. I went to add it manually and ID jump 1000+ too.
Google says that might be a SQL Server bug (or feature).
You can find a lot of similar reported issues.
PS: Sambapos never specifically alters ids.
Thanks for that @emre. Good to hear this was not caused by SambaPOS, or by an upgrade.
Very strange that this “feature” (defect) exists… I run 2014.
In any case, I have developed a script to fix this issue.
But first I need to confirm that the only table linked to [Workperiods].[Id] is:
[PeriodicConsumptions].[WorkPeriodId]
Do you know of an easy way to check this?
EDIT: I used the Generate Scripts function to create a SQL script for the entire database and all tables and then searched the script for “Work” … it seems [PeriodicConsumptions] is the only table having a link to [Workperiods].[Id], so I am good to go. Applying fix…
Do not attempt this if you not know what you are doing, and as always, run a DB BACKUP first!!!
Not for the faint of heart …
USE [SambaPOS5]
GO
set identity_insert [WorkPeriods] ON
GO
--
-- MANUALY set the LAST GOOD Workperiod ID here
--
declare @WPlastgood int = 94
-- enter the value that the ID has jumped
declare @JumpValue int = 1000
--
-- everything else can be calculated automatically
--
declare @WPfirstbad int = @WPlastgood + @JumpValue
declare @WPreseed int = 0
SET @JumpValue = @JumpValue - 1
SELECT @WPreseed=(COUNT([Id])+@WPlastgood-1) FROM [WorkPeriods] WHERE [Id]>@WPlastgood
print @WPreseed
declare @wp table
(
[Id] [int] NOT NULL,
[StartDate] [datetime] NOT NULL,
[EndDate] [datetime] NOT NULL,
[StartDescription] [nvarchar](max) NULL,
[EndDescription] [nvarchar](max) NULL,
[Name] [nvarchar](max) NULL
)
INSERT INTO @wp
SELECT
[Id]-@JumpValue
,[StartDate]
,[EndDate]
,[StartDescription]
,[EndDescription]
,[Name]
FROM [WorkPeriods]
WHERE [Id] > @WPlastgood
--select * from @wp
INSERT INTO [WorkPeriods]
(
[Id]
,[StartDate]
,[EndDate]
,[StartDescription]
,[EndDescription]
,[Name]
)
SELECT
[Id]
,[StartDate]
,[EndDate]
,[StartDescription]
,[EndDescription]
,[Name]
FROM @wp
DELETE FROM [WorkPeriods] WHERE [Id]>=@WPfirstbad
UPDATE [PeriodicConsumptions] SET [WorkPeriodId]=[WorkPeriodId]-@JumpValue WHERE [WorkPeriodId]>=@WPfirstbad
DBCC CHECKIDENT ([WorkPeriods], RESEED, @WPreseed)
SELECT
[Id]
,[StartDate]
,[EndDate]
,[StartDescription]
,[EndDescription]
,[Name]
FROM [dbo].[WorkPeriods]
GO
SELECT
[Id]
,[WorkPeriodId]
,[StartDate]
,[EndDate]
,[LastUpdateTime]
,[Name]
FROM [dbo].[PeriodicConsumptions]
GO
set identity_insert [WorkPeriods] OFF
GO
2 Likes
Ok, this is the 4th time I have seen this happen now… once at one venue and 3 times at my other venue.
I did not notice it happened again until yesterday, and the jump occurred over a month ago… that requires a lot of fixes be made to my system of SQL reports for 30+ days in a row.
So I have now scripted a check for this condition when a Workperiod is started, and notify me if this issue has reared it’s ugly head again, to reduce the amount of work I need to “fix” it.
The script does not fix the problem; it simply checks for the problem:
function readwp() {
var qry = "";
// get Aggregate data
// Min, Max, Count of Workperiod IDs
qry = "SELECT count([Id]), min([Id]), max([Id]) FROM [WorkPeriods]";
var wpAgg = sql.Query(qry).First;
wpAgg=wpAgg.split(',');
var wpCount = wpAgg[0];
var wpMin = wpAgg[1];
var wpMax = wpAgg[2];
var wpCheck = wpMax - wpMin + 1;
// at this point we can already tell if something is wrong
// so we could stop here and return a warning
// but I want more detail, so we won't bail yet
var wpConcurrent = (wpCount==wpCheck ? true : false);
// read all Workperiod IDs, in order, into array
qry = "SELECT [Id] FROM [WorkPeriods] ORDER BY [Id]";
var r = sql.Query(qry).Delimit(',').All;
var rCount = r.Length;
// initialize vars
var s="";
var w=wpMin;
var wpOK;
var wpLastGood=0;
var wpFirstBad=0;
var wpDiff=0;
// loop though Workperiod IDs,
// starting at the Min and compare
for (var i=0; i<rCount; i++) {
// check if the WP record ID matches
wpOK = (r[i]==w ? true : false);
if (!wpOK) {
wpLastGood = w-1;
wpFirstBad = r[i];
wpDiff = wpFirstBad - wpLastGood;
}
// build info string
s += "rec:"+r[i] + " Comp:" + w + " OK:" + wpOK + " LG:" + wpLastGood + " FB:" + wpFirstBad + " DIFF:" + wpDiff + "\r\n";
// break out of the loop
// as soon as we find the first BAD ID
if (!wpOK) {
break;
}
w++;
}
// return a warning that something is wrong
if (!wpOK) {
return "Workperiods are NOT concurrent!" + "\r\n" + "rec:"+r[i] + " Comp:" + w + " OK:" + wpOK + " LG:" + wpLastGood + " FB:" + wpFirstBad + " DIFF:" + wpDiff + "\r\n";
}
//return s;
//return "Min:"+wpMin+"\r\nMax:"+wpMax+"\r\nCount:"+wpCount+"\r\nCheck:"+wpCheck+"\r\nLen:"+rCount+"\r\nConcurrent:"+wpConcurrent;
// return an OK if all is well
return "Workperiods are concurrent.";
}
3 Likes