SP producing an impossible error
-
ISSUE:
Application is throwing UNIQUE KEY insert errors in PROD that should be logically impossible from the stored procedure that is generating them. The error would be expected with the previous version of the proc, but proc is confirmed (via definiton in sys.sql_modules) as new code that should not produce this error.
The proc is a fairly standard IF NOT EXISTS INSERT ELSE SELECT Existing matching row. Multiple people have reviewed that there should be no way to reach the INSERT if the record is a duplicate. FWIW, this proc was recently upgraded to this pattern from a basic INSERT statement with no checking for uniqueness
This morning the same test case would reliably cause the same error in DEV. I execute the SQL code as an ad-hoc query with identical parameters in DEV through SSMS (instead of the application) Oddly, this ran without out error, and the failing test case suddenly started working in DEV, and has never failed since.
I attempted running the code ad hoc in Production. It did run, but did not cause the test case to start working.
We've recompiled the SP, rebuilt all relevant Indexes and Statistics, with no success.
We've confirmed that both procs are identical in definition in both environments. Application code is identical in both environments.
I've got a request started to do a profiler trace in PROD to see exactly what is being run and exactly what the arguments are. However, it will take some time before it will be run due to the red-tape associated with production apps.
Any suggestion on what might be causing a stored proc to execute as a previous version of itself rather than it's modern version? If so, how can I force the PROD version to refresh its cached version? Any likely alternate causes (other than it might be executing its previous version)?
Warm Regards,Greg Wilsonsolidrockstable.com
-
does the proc insert potentially multiple rows?
is the proc generating a unique key value? a bad join in a SELECT statement could pass the not exists, but make the insert do multiple rows, which violates the PK:
a gross example.
CREATE TABLE Example(id int primary key, otherdata varchar(30))
IF NOT EXISTS(SELECT 1 FROM EXAMPLE WHERE ID = 1)
INSERT INTO Example(id,otherdata)
SELECT 1 ,name FROM sys.columns
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Any suggestion on what might be causing a stored proc to execute as a previous version of itself rather than it's modern version? If so, how can I force the PROD version to refresh its cached version? Any likely alternate causes (other than it might be executing its previous version)?
It is absolutely NOT running an old cached version. SQL will not do that. When a stored proc is compile part of that process is to clear the cache for any references to the object.
There is a bug in the code, not in the sql server engine.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Lowell (6/24/2013)
does the proc insert potentially multiple rows?is the proc generating a unique key value? a bad join in a SELECT statement could pass the not exists, but make the insert do multiple rows, which violates the PK:
a gross example.
CREATE TABLE Example(id int primary key, otherdata varchar(30))
IF NOT EXISTS(SELECT 1 FROM EXAMPLE WHERE ID = 1)
INSERT INTO Example(id,otherdata)
SELECT 1 ,name FROM sys.columns
Sadly, no, not that easy. Below is the relevant code (some variable names changed to protect the innocent):
ALTER TABLE [Foo].[FooAccounts]ADD CONSTRAINT [UniqueAccountInformation] UNIQUE NONCLUSTERED
(
[Code] ASC,
[UniqueKeyPart2] 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]
GO
CREATE PROCEDURE [Foo].[ProblemProc]
@ID AS INT OUTPUT ,
@code AS VARCHAR(100) ,
@maskedCode AS VARCHAR(17) ,
@UniqueKeyPart2 AS INT ,
@lastModifiedBy AS VARCHAR(25) ,
@lastModifiedComputerName AS VARCHAR(25) ,
@lastModifiedDate AS DATETIME
AS
BEGIN
IF NOT EXISTS ( SELECT 1
FROM Foo.FooAccounts
WHERE Code = @code
AND UniqueKeyPart2 = @UniqueKeyPart2 )
BEGIN
INSERT INTO Foo.FooAccounts
( Code ,
MaskedCode ,
UniqueKeyPart2 ,
LastModifiedBy ,
LastModifiedComputerName ,
LastModifiedDate
)
VALUES ( @code ,
@maskedCode ,
@UniqueKeyPart2 ,
@lastModifiedBy ,
@lastModifiedComputerName ,
@lastModifiedDate
)
SET @ID = SCOPE_IDENTITY()
END
ELSE
SELECT @ID = FooAccountId
FROM Foo.FooAccounts
WHERE Code = @code
AND UniqueKeyPart2 = @UniqueKeyPart2
END
GO
I'm really struggling to figure out how this proc can generate a Unique Key insert error.
Hopefully, someone else's eyes can spot something I haven't.
But since this same code executes just fine in SSMS and doesn't produce the error, there may be something causing something else to run. What that is, I'm running low on ideas as to what it could be.
Warm Regards,Greg Wilsonsolidrockstable.com
-
Possibly one or both values/columns are null.
In that case the IF NOT EXISTS will yield TRUE so the INSERT-statement will be executed.
"Walking on water and developing software from a specification are easy if both are frozen." -- Edward V. Berard, "Life-Cycle Approaches"
-
The trace we ran confirmed that the code that was executing was not what we expected to be executing. Further analysis found (quite shockingly) human/process err.
The check of "Is the code in PROD identical to the code in DEV" was filtered for things that had changed in PROD during the deployment. Can you guess what that missed?
It missed that the relevant proc was never updated because the filter on the check culled out the relevant proc as it filtered.
I'm not telling who wrote the script that didn't filter correctly. :ermm: I certainly won't mention if it was written by the person writting this post. :crying:
Warm Regards,Greg Wilsonsolidrockstable.com
-
Check with sys.sql_modules whether your new S.P. code indeed in your database, not in master or any other databases. Sometimes during deployment people tend to omit USE...
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply