Violation of Primary Key - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Violation of Primary Key

Author

Topic

savvy95
Starting Member

23 Posts

Posted - 2006-06-14 : 10:21:15

hello everyone,
I hope someone can help me.....

I've been collecting Security Logs from my Win3k servers for approx 1.5 months now with no problems and putting the result set into SQL where I can analyze it. Last week the script would only insert a small number of events, even though there were thousands. Upon investigation, I discovered that one culprit is "Violation of Primary Key". The Primary Key is Record Number.

What code can I use to check if the record has been added and if so not add it.

Here's my code:


MM_capacity_STRING = "Driver={SQL Server};Server=<ServerName>;Database=eventLogs;Uid=;Pwd="
MM_editConnection = MM_capacity_STRING
set conn_temp = CreateObject("ADODB.Connection")
conn_temp.open = MM_editConnection
set conn = CreateObject("ADODB.Command")
conn.ActiveConnection = MM_editConnection
set rs = Createobject("ADODB.RecordSet")

strComputer = "<Computername"
Set objWMIService = GetObject("winmgmts:" _
 & "{impersonationLevel=impersonate,(Security,Backup)}!\\" & strComputer & "\root\cimv2")
Set colLoggedEvents = objWMIService.ExecQuery _
 ("SELECT * FROM Win32_NTLogEvent WHERE Logfile = 'Security'")
For Each objEvent in colLoggedEvents
 Category = objEvent.Category
    Computer_Name = objEvent.ComputerName
    Event_Code = objEvent.EventCode
    Message = objEvent.Message
    Record_Number = objEvent.RecordNumber
    Source_Name = objEvent.SourceName
    Time_Written = objEvent.TimeWritten
    Event_Type = objEvent.type
    User = objEvent.User


set rs = Createobject("ADODB.RecordSet")
RoleStr = "insert into logs (logname, computername, eventcode, recordnumber, sourcename, eventtype, writtendate, username, message) values ('Security', '" & Computer_Name & "', '" & Event_Code & "', '" &  Record_Number & "', '" &  Source_Name & "', '" &  Event_Type & "', '" &  Time_Written & "', '" &  User & "', '" &  Message & "')"

rs.open RoleStr , conn_temp

Next
rs.close

Thank you for you help

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-14 : 13:05:05

Obviously there is a duplicate for the Record_Number variable when insert into log table.

Peter Larsson
Helsingborg, Sweden

savvy95
Starting Member

23 Posts

Posted - 2006-06-14 : 16:47:59

Obviously, But I'm asking for assistance in writing code to check if the record has been added and if so not add it.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-15 : 03:34:34

If not exists(Select * from table where logname='somename')
--Insert record
else
--Dont Insert

Madhivanan

Failing to plan is Planning to fail

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-06-15 : 21:21:35

create a stored procedure where you can check existence of the record and pass the field values into for record insertion

--------------------
keeping it simple...

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-16 : 00:15:41

[code]
insert into logs
(logname,
computername,
eventcode,
recordnumber,
sourcename,
eventtype,
writtendate,
username,
message)
select
a.*
from
(
select
logname = 'val1',
computername = 'val2',
eventcode = 'val3',
recordnumber = 'val4',
sourcename = 'val5',
eventtype = 'val6',
writtendate = 'val7',
username = 'val8',
message = 'val9'
) a
left join
logs b
on a.recordnumber = b.recordnumber
where
-- Insert only when null
b.recordnumber is null

[/code]

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources