March 8, 2012 at 9:27 pm
We get the following error at random times on client machines running SQL Server 2005 Express SP4.
System.Data.SqlClient.SqlException (0x80131904): Violation of UNIQUE KEY constraint 'AK_SeqNo'. Cannot insert duplicate key in object 'dbo.SeqNo'.
We've tried all sorts of sql code but the error still happens. We thought the code below should not fail, but it still does. Can anyone see why?
This stored procedure that creates this error has the following lines:
ALTER PROCEDURE [dbo].[mspResetDayNo] @Today DATETIME
AS
BEGIN
DECLARE @DateSrc DATETIME
DECLARE @StGUID UNIQUEIDENTIFIER
DECLARE @err INT
DECLARE @SeqNo TABLE
(
StGUID UNIQUEIDENTIFIER
,TheDay DATETIME
)
-- Chop off the time part:
SELECT
@DateSrc = DATEADD(d, 0, DATEDIFF(d, 0, @Today))
-- Get Current StGUID
SELECT
@StGUID = dbo.MyStGUID()
-- Insert Record to Check for
INSERT INTO @SeqNo
(StGUID, TheDay)
VALUES
(@StGUID, @DateSrc)
-- Wrap in a Transaction to avoid weird exception errors
BEGIN TRANSACTION
-- If this is the first entry for the day then initialise:
INSERT INTO dbo.SeqNo
(
StGUID
,TheDay
,LastSeqNo
)
SELECT
tmp.StGUID
,tmp.TheDay
,0
FROM
@SeqNo AS tmp
WHERE
NOT EXISTS ( SELECT
*
FROM
dbo.SeqNo AS sn
WHERE
sn.StGUID = tmp.StGUID
AND sn.TheDay = tmp.TheDay )
SET @err = @@ERROR
IF @err <> 0
BEGIN
ROLLBACK TRANSACTION
PRINT N'This should not error!'
END
ELSE
COMMIT TRANSACTION
RETURN(@err)
END
The index is:
ALTER TABLE [dbo].[SeqNo] ADD CONSTRAINT [AK_SeqNo] UNIQUE NONCLUSTERED
(
[TheDay] ASC,
[StGUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
March 8, 2012 at 10:08 pm
Just curious, but what is the definition of this function: dbo.MyStGUID()
March 8, 2012 at 11:35 pm
Here is the function:
ALTER FUNCTION [dbo].[MyStGUID]()
RETURNS uniqueidentifier
AS
BEGIN
DECLARE @sg as uniqueidentifier
SELECT @sg = StGUID FROM Self
RETURN @sg
END
March 8, 2012 at 11:46 pm
What is self?
March 8, 2012 at 11:59 pm
Sorry, yes, I should have explained that.
The [Self] table is simply the machine identification single record table. It contains the identification settings for the current client, one of them being their unique Store ID which is a GUID.
March 9, 2012 at 12:05 am
Try the following:
ALTER PROCEDURE [dbo].[mspResetDayNo] @Today DATETIME
AS
BEGIN
DECLARE @DateSrc DATETIME
DECLARE @StGUID UNIQUEIDENTIFIER
DECLARE @err INT
DECLARE @SeqNo TABLE
(
StGUID UNIQUEIDENTIFIER
,TheDay DATETIME
)
-- Chop off the time part:
SELECT
@DateSrc = DATEADD(d, 0, DATEDIFF(d, 0, @Today))
-- Get Current StGUID
SELECT
@StGUID = dbo.MyStGUID()
-- Insert Record to Check for
INSERT INTO @SeqNo
(StGUID, TheDay)
VALUES
(@StGUID, @DateSrc)
-- Wrap in a Transaction to avoid weird exception errors
BEGIN TRANSACTION
-- If this is the first entry for the day then initialise:
INSERT INTO dbo.SeqNo
(
StGUID
,TheDay
,LastSeqNo
)
SELECT
tmp.StGUID
,tmp.TheDay
,0
FROM
@SeqNo AS tmp
WHERE
NOT EXISTS ( SELECT
*
FROM
dbo.SeqNo AS sn
WHERE
sn.StGUID = @StGUID
AND sn.TheDay = @DateSrc )
SET @err = @@ERROR
IF @err <> 0
BEGIN
ROLLBACK TRANSACTION
PRINT N'This should not error!'
END
ELSE
COMMIT TRANSACTION
RETURN(@err)
END
March 9, 2012 at 12:13 am
Thanks. I'll let you know how it goes. As it happens on random days at random stores, it may take some time to see if it makes a difference.
Just to be curious, why should that make a difference?
Since the guid cannot change it has to be something to do with the date, but what? 1 bit out in removing the time part?
March 9, 2012 at 6:55 am
Not sure. What you have should work.
March 10, 2012 at 10:43 pm
The strange thing is that the original code was basically the follow, which also didn't work:
IF NOT EXIST (SELECT 1 FROM SeqNo WHERE StGUID = @StGUID AND TheDay = @DateSrc)
BEGIN
INSERT INTO SeqNo (StGUID, TheDay, LastSeqNo)
VALUES (@StGUID, @DateSrc, 0) ;
END
I've checked the collation and for the machines having the problem and it's all the same, there isn't a sign of another login accessing this table either at the same time to be a threading issue. It's just like it reads and compares the data one way, and writes it another...?
Could it be the Alternative Key Index be badly formed? Or could it be that it's using a unique constraint, rather than just a unique index?
March 15, 2012 at 7:37 am
I have couple of questions:
1. Why to use table variable if you only insert only one row anyway?
Is your real code will try to insert multiple rows?
If so, are datasets you work with are quite large?
2. Why are you wrapping SINGLE insert statement into transaction?
It's atomic enough! What kind of weird error you are afraid of?
March 15, 2012 at 8:33 am
Maybe I missed it but I can't seem to find a complete declaration of the table and all indexes, could post (or report) the DDL for the table and all the indexes please.
March 15, 2012 at 6:03 pm
@Ten: Answer to your questions:
At first when we got the error, we thought there might be a threading error between the IF NOT EXISTS and the insert, where another connection beat the first to the insert, so we wrapped it in a transaction. Then when it happened after that, we tried a single statement that could not potentially be intercepted but left the transaction for good measure.
No, the actual real code only inserts a single row as well, which is why I'm scratching my head on why the random error.
CREATE TABLE [dbo].[SeqNo](
[SeqNoGUID] [dbo].[DPrimaryKey] ROWGUIDCOL NOT NULL CONSTRAINT [SeqNoAUTO] DEFAULT (newsequentialid()),
[StGUID] [dbo].[DForeignKey] NOT NULL,
[TheDay] [datetime] NOT NULL,
[LastSeqNo] [int] NOT NULL,
CONSTRAINT [PK_SeqNo] PRIMARY KEY NONCLUSTERED
(
[SeqNoGUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [AK_SeqNo] UNIQUE NONCLUSTERED
(
[TheDay] ASC,
[StGUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SeqNo] WITH NOCHECK ADD CONSTRAINT [FK_SeqNo_Str] FOREIGN KEY([StGUID])
REFERENCES [dbo].[Str] ([StGUID])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[SeqNo] CHECK CONSTRAINT [FK_SeqNo_Str]
March 16, 2012 at 5:54 pm
Allow me to make an overly obvious suggestion: Instead of just catching the error, add a logging step in the error handler so you can see which values are failing.
July 16, 2012 at 11:15 pm
Sorry to bring this back to life but it still is happening, less yes now that before but still once a month or so. Still not all client machines and still no consistency between exceptions. Very odd indeed.
We are capturing the exception and all it's stack trace that gets returned to the code, but other than the methods that lead up to the calling code, only show the original error I had on the first post.
I've now change the code yet again (hopefully to clean it up a little more) to combine the check with the insert, added the semicolons and used a Set instead of the Select to assign the @variables as follows:
-- Get Current Store
SET @StGUID = dbo.MyStGUID();
INSERT INTO dbo.SeqNo
(
StGUID
,TheDay
,LastSeqNo
)
SELECT
@StGUID
,@DateSrc
,0
WHERE
NOT EXISTS ( SELECT
1
FROM
dbo.SeqNo AS sn
WHERE
sn.StGUID = @StGUID
AND sn.TheDay = @DateSrc );
July 16, 2012 at 11:44 pm
Let's start fresh. Please post the DDL for the offending table (including the indexes defined), and the code for the entire procedure that is failing. When it errors, when does it error, on the first record for the day of a given store is it totally random?
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply