Sql-server – I get a unique key violation and I can’t see why

sql server

Here is my script to create the table, procedure and the exec statement that will fail the 2nd time it runs.

If you run the exec more than 1x you will not update the counter but try and insert a duplicate key… I have now looked at the code for 18 hours so I'm getting "blind" and I am updating it to ways that I think are wrong, perhaps someone with a fresh coffee can spot it immediately and tell me where I'm wrong…

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TcpProcessRecord]') AND type in (N'U'))
BEGIN
    print 'CREATE TABLE [dbo].[TcpProcessRecord]';
    CREATE TABLE [dbo].[TcpProcessRecord]
    (
        [ID] uniqueidentifier NOT NULL DEFAULT NewSequentialID() PRIMARY KEY CLUSTERED,
        [ProcessId] int not null,
        [Created] datetime not null,
        [LastSeen] datetime not null,
        [Remote_CIDR] bigint NOT NULL,
        [ProcessName] nvarchar(64) NOT NULL,
        [Executable] nvarchar(64) NULL,
        [Path] nvarchar(255) NULL,
        [Args] nvarchar(255) NULL,
        [Country] int NOT NULL,
        [MapLocation] varbinary(1000) NULL,
        [MapLocationCs] int,
        [Json] varbinary(8000) NOT NULL
    )
    
    CREATE UNIQUE INDEX IX_TcpProcessRecord_business_key ON [dbo].[TcpProcessRecord]([Created],[ProcessId],[ProcessName],[Remote_CIDR],[Executable],[Path],[Args]) include([id]);
    
END

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TcpProcessRecordMapLocations]') AND type in (N'U'))
BEGIN
    print 'CREATE TABLE [dbo].[TcpProcessRecordMapLocations]';
    CREATE TABLE [dbo].[TcpProcessRecordMapLocations]
    (
        [MapLocationCs] int NOT NULL PRIMARY KEY CLUSTERED,
        [Json] varbinary(1000) NOT NULL
    )
END
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TcpProcessRecordIP]') AND type in (N'U'))
BEGIN
    print 'CREATE TABLE [dbo].[TcpProcessRecordIP]';
    CREATE TABLE [dbo].[TcpProcessRecordIP]
    (
        [CIDR] bigint NOT NULL PRIMARY KEY CLUSTERED,
        [IP] varchar(64) NOT NULL
    )

END


GO
create or alter procedure [dbo].[AddTcpProcessRecord]
@ProcessId int,
@Created datetime,
@CIDR bigint,
@LastSeen datetime,
@IP varchar(64),
@ProcessName nvarchar(64),
@Executable nvarchar(64) null,
@Args nvarchar(255) null,
@Path nvarchar(255) null,
@Country int,
@MapLocation varbinary(1000) NULL,
@MapLocationCs int NULL,
@Json varbinary(8000),
@NewId uniqueidentifier output
as

if(@MapLocation is not null)
begin
    if not exists (select * from [dbo].[TcpProcessRecordMapLocations] where [MapLocationCs] =@MapLocationCs)
    insert into [dbo].[TcpProcessRecordMapLocations]([MapLocationCs], [Json])
    values(@MapLocationCs,@MapLocation);
end;

if not exists (select * from [dbo].[TcpProcessRecordIP] where [CIDR] =@CIDR)
BEGIN
    insert into [dbo].[TcpProcessRecordIP]([CIDR], [IP])
    values(@CIDR,@IP);
END
--new ID does not get the business key
select @NewId= ID
  FROM [dbo].[TcpProcessRecord] 
 where [ProcessId] =@ProcessId 
   and [Created]    =@Created 
   and [ProcessName]=@ProcessName 
   and [Remote_CIDR] = @CIDR
   and ([Executable] = @Executable or ([Executable] is null and @Executable is null))
   and ([Path]       = @Path       or ([Path] is null and @Path is null))
   and ([Args]       = @Args       or ([Args] is null and @Args is null))   
   

IF @newID is null
BEGIN

  DECLARE @op TABLE (ColGuid uniqueidentifier)
--insert will fail as the entry violates the duplicate  
  insert into [dbo].[TcpProcessRecord]([ProcessId],[Created],[LastSeen],[Remote_CIDR],[ProcessName],[Executable],[Path],[Country],[MapLocationCs],[Json]) 
  OUTPUT inserted.[ID] INTO @op
  values(@ProcessId,@Created,@LastSeen,@CIDR,@ProcessName,@Executable,@Path,@Country,@MapLocationCs ,@Json)

   select @NewId= ColGuid from @op
END
else
UPDATE [dbo].[TcpProcessRecord]
   set [LastSeen]=@LastSeen
where [ID]=@NewId


return 0;
GO

declare @p14 uniqueidentifier
set @p14=NULL
exec [dbo].[AddTcpProcessRecord] @ProcessId=9380
,@Created='2020-12-20 05:37:52.917'
,@CIDR=675544612,@LastSeen='2020-12-20 17:29:56.690'
,@IP='40.67.254.36'
,@ProcessName=N'OneDrive'
,@Executable=N'OneDrive.exe'
,@Args=N'/background'
,@Path=N'C:\Users\W2307\AppData\Local\Microsoft\OneDrive'
,@Country=372
,@MapLocation=0x7B2243494452223A3637353534343631322C2243697479223A224475626C696E222C22436F756E747279223A224972656C616E64222C2247656F223A3337322C224C61746974757465223A35332E333333312C224C6F6E676974756465223A2D362E323438392C22495041646472657373223A2234302E36372E3235342E3336227D,@MapLocationCs=617657200,@Json=0x7B226C61223A223139322E3136382E322E313030222C227261223A2234302E36372E3235342E3336222C2270223A393338302C2273223A322C2261223A224F6E654472697665222C226C70223A3434332C227270223A34393732312C226C223A3337322C2265223A22433A5C5C55736572735C5C57323330375C5C417070446174615C5C4C6F63616C5C5C4D6963726F736F66745C5C4F6E6544726976655C5C4F6E6544726976652E657865222C226671646E223A224445534B544F502D50443945323239222C22646F6D61696E223A22222C226561223A222F6261636B67726F756E64222C226573223A22323032302D31322D32305430353A33373A35322E393137313636362B30313A3030222C226569223A7B2241766541223A22222C2241766542223A224D6963726F736F667420436F72706F726174696F6E222C2241766543223A313030352C2241766544223A224D6963726F736F6674204F6E654472697665222C2241766545223A32302C2241766546223A3230312C2241766547223A22433A5C5C55736572735C5C57323330375C5C417070446174615C5C4C6F63616C5C5C4D6963726F736F66745C5C4F6E6544726976655C5C4F6E6544726976652E657865222C2241766548223A392C2241766549223A2232302E3230312E313030352E30303039222C224176654A223A22436C69656E74204170706C69636174696F6E222C224176654B223A66616C73652C224176654C223A66616C73652C224176654D223A66616C73652C224176654E223A66616C73652C224176654F223A747275652C2241766550223A22456E676C6973682028556E697465642053746174657329222C2241766551223A22C2A9204D6963726F736F667420436F72706F726174696F6E2E20416C6C207269676874732072657365727665642E222C2241766552223A22222C2241766553223A224F6E6544726976652E657865222C2241766554223A22222C2241766555223A313030352C2241766556223A32302C2241766557223A3230312C2241766558223A224D6963726F736F6674204F6E654472697665222C2241766559223A392C224176655A223A2232302E3230312E313030352E30303039222C224176654141223A22622F6275696C642F30333037373066302D616232362D383930652D386365622D393033316134363765333339222C224176654142223A22323032302D31322D31315430383A30393A34312E373436343932355A222C224176654143223A22323032302D30372D30335430393A35323A30332E393336373139365A222C224176654144223A22312E3835204D42227D2C2274223A22323032302D31322D32305430353A33373A35322E393137313636362B30313A3030222C226C75223A22323032302D31322D32305431373A32393A35372E313033333832355A222C226775223A6E756C6C2C226373223A342C226E223A322C224651444E223A224445534B544F502D50443945323239222C22446F6D61696E223A22222C224C6F63616C506F7274223A34393732317D
,@NewID=@p14 output
select @p14

Best Answer

I found that I missed adding the [Args] column (came later) to the inserted, it always had null therefore if a service did have arguments it would generate a duplicate...

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TcpProcessRecord]') AND type in (N'U'))
    BEGIN
        print 'CREATE TABLE [dbo].[TcpProcessRecord]';
        CREATE TABLE [dbo].[TcpProcessRecord]
        (
            [ID] uniqueidentifier NOT NULL DEFAULT NewSequentialID() PRIMARY KEY CLUSTERED,
            [ProcessId] int not null,
            [Created] datetime not null,
            [LastSeen] datetime not null,
            [Remote_CIDR] bigint NOT NULL,
            [ProcessName] nvarchar(64) NOT NULL,
            [Executable] nvarchar(64) NULL,
            [Path] nvarchar(255) NULL,
            [Args] nvarchar(255) NULL,
            [Country] int NOT NULL,
            [MapLocation] varbinary(1000) NULL,
            [MapLocationCs] int,
            [Json] varbinary(8000) NOT NULL
        )
        
        CREATE UNIQUE INDEX IX_TcpProcessRecord_business_key ON [dbo].[TcpProcessRecord](
        [Created],[ProcessId],[ProcessName],[Remote_CIDR],[Executable],[Path],[Args]) include([id]);
        
    END
    
    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TcpProcessRecordMapLocations]') AND type in (N'U'))
    BEGIN
        print 'CREATE TABLE [dbo].[TcpProcessRecordMapLocations]';
        CREATE TABLE [dbo].[TcpProcessRecordMapLocations]
        (
            [MapLocationCs] int NOT NULL PRIMARY KEY CLUSTERED,
            [Json] varbinary(1000) NOT NULL
        )
    END
    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TcpProcessRecordIP]') AND type in (N'U'))
    BEGIN
        print 'CREATE TABLE [dbo].[TcpProcessRecordIP]';
        CREATE TABLE [dbo].[TcpProcessRecordIP]
        (
            [CIDR] bigint NOT NULL PRIMARY KEY CLUSTERED,
            [IP] varchar(64) NOT NULL
        )
    
    END
    
    
    GO
    create or alter procedure [dbo].[AddTcpProcessRecord]
    @ProcessId int,
    @Created datetime,
    @CIDR bigint,
    @LastSeen datetime,
    @IP varchar(64),
    @ProcessName nvarchar(64),
    @Executable nvarchar(64) null,
    @Args nvarchar(255) null,
    @Path nvarchar(255) null,
    @Country int,
    @MapLocation varbinary(1000) NULL,
    @MapLocationCs int NULL,
    @Json varbinary(8000),
    @NewId uniqueidentifier output
    as
    
    if(@MapLocation is not null)
    begin
        if not exists (select * from [dbo].[TcpProcessRecordMapLocations] where [MapLocationCs] =@MapLocationCs)
        insert into [dbo].[TcpProcessRecordMapLocations]([MapLocationCs], [Json])
        values(@MapLocationCs,@MapLocation);
    end;
    
    if not exists (select * from [dbo].[TcpProcessRecordIP] where [CIDR] =@CIDR)
    BEGIN
        insert into [dbo].[TcpProcessRecordIP]([CIDR], [IP])
        values(@CIDR,@IP);
    END
    --new ID does not get the business key
    select @NewId= ID
      FROM [dbo].[TcpProcessRecord] 
     where [ProcessId]=@ProcessId
    and [Created] =@Created  
    and [ProcessName]=@ProcessName
    and [Remote_CIDR]=@CIDR
    and [Executable]=@Executable
    and [Path]=@Path
    and [Args] =@Args
    
       
    
    IF @newID is null
    BEGIN
    
      DECLARE @op TABLE (ColGuid uniqueidentifier)
    --insert will fail as the entry violates the duplicate  
      insert into [dbo].[TcpProcessRecord]([ProcessId],[Created],[LastSeen],[Remote_CIDR],[ProcessName],[Executable],[Path],[Args],[Country],[MapLocationCs],[Json]) 
      OUTPUT inserted.[ID] INTO @op
      values(@ProcessId ,@Created ,@LastSeen  ,@CIDR       ,@ProcessName  ,@Executable ,@Path,@Args ,@Country ,@MapLocationCs ,@Json)
    
       select @NewId= ColGuid from @op
    END
    else
    UPDATE [dbo].[TcpProcessRecord]
       set [LastSeen]=@LastSeen
    where [ID]=@NewId
    
    
    return 0;
    GO
    
    declare @p14 uniqueidentifier
    set @p14=NULL
    exec [dbo].[AddTcpProcessRecord] @ProcessId=9380
    ,@Created='2020-12-20 05:37:52.917'
    ,@CIDR=675544612,@LastSeen='2020-12-20 17:29:56.690'
    ,@IP='40.67.254.36'
    ,@ProcessName=N'OneDrive'
    ,@Executable=N'OneDrive.exe'
    ,@Args=N'/background'
    ,@Path=N'C:\Users\W2307\AppData\Local\Microsoft\OneDrive'
    ,@Country=372
    ,@MapLocation=0x7B2243494452223A3637353534343631322C2243697479223A224475626C696E222C22436F756E747279223A224972656C616E64222C2247656F223A3337322C224C61746974757465223A35332E333333312C224C6F6E676974756465223A2D362E323438392C22495041646472657373223A2234302E36372E3235342E3336227D,@MapLocationCs=617657200,@Json=0x7B226C61223A223139322E3136382E322E313030222C227261223A2234302E36372E3235342E3336222C2270223A393338302C2273223A322C2261223A224F6E654472697665222C226C70223A3434332C227270223A34393732312C226C223A3337322C2265223A22433A5C5C55736572735C5C57323330375C5C417070446174615C5C4C6F63616C5C5C4D6963726F736F66745C5C4F6E6544726976655C5C4F6E6544726976652E657865222C226671646E223A224445534B544F502D50443945323239222C22646F6D61696E223A22222C226561223A222F6261636B67726F756E64222C226573223A22323032302D31322D32305430353A33373A35322E393137313636362B30313A3030222C226569223A7B2241766541223A22222C2241766542223A224D6963726F736F667420436F72706F726174696F6E222C2241766543223A313030352C2241766544223A224D6963726F736F6674204F6E654472697665222C2241766545223A32302C2241766546223A3230312C2241766547223A22433A5C5C55736572735C5C57323330375C5C417070446174615C5C4C6F63616C5C5C4D6963726F736F66745C5C4F6E6544726976655C5C4F6E6544726976652E657865222C2241766548223A392C2241766549223A2232302E3230312E313030352E30303039222C224176654A223A22436C69656E74204170706C69636174696F6E222C224176654B223A66616C73652C224176654C223A66616C73652C224176654D223A66616C73652C224176654E223A66616C73652C224176654F223A747275652C2241766550223A22456E676C6973682028556E697465642053746174657329222C2241766551223A22C2A9204D6963726F736F667420436F72706F726174696F6E2E20416C6C207269676874732072657365727665642E222C2241766552223A22222C2241766553223A224F6E6544726976652E657865222C2241766554223A22222C2241766555223A313030352C2241766556223A32302C2241766557223A3230312C2241766558223A224D6963726F736F6674204F6E654472697665222C2241766559223A392C224176655A223A2232302E3230312E313030352E30303039222C224176654141223A22622F6275696C642F30333037373066302D616232362D383930652D386365622D393033316134363765333339222C224176654142223A22323032302D31322D31315430383A30393A34312E373436343932355A222C224176654143223A22323032302D30372D30335430393A35323A30332E393336373139365A222C224176654144223A22312E3835204D42227D2C2274223A22323032302D31322D32305430353A33373A35322E393137313636362B30313A3030222C226C75223A22323032302D31322D32305431373A32393A35372E313033333832355A222C226775223A6E756C6C2C226373223A342C226E223A322C224651444E223A224445534B544F502D50443945323239222C22446F6D61696E223A22222C224C6F63616C506F7274223A34393732317D
    ,@NewID=@p14 output
    select @p14