How to use for logging

[digisol]

I am very new to SQL and hardly ever use it. I do how ever maintain a proxy server array. So I used a existing SQL server to do the Proxy servers Logging.The database is working just great. The problem is that it is growing very fast and I want the sql server to be able to delete records that are lets say over a hundred days old. I figure that after a hundred days whatever happened back then is not worth keeping. I used to use a access db and the SQL delete query was


DELETE *
FROM webproxy
WHERE (((webproxy.logTime)<[please enter the date]));

this worked great except I couldn't automate the process. With SQL I can.

I want the above query to

1: Work in SQL not just Access

2: instead of asking for a date just delete anything older than a hundred days


If there is any GURUs out there that could save me hours of work I would really be thankful!

 

[foxdev]

You could create a stored procedure, then schedule that procedure to run nightly in SQL Server. You would use the GETDATE() function in place of [please enter the date].

 

[digisol]

This is exactly what I planned to do. The problem is the syntax. Thank you for the getdate clue but i need to figure out what 100 days from getdate would be?

would I use something like


DELETE *
FROM webproxy
where ((webproxy.logtime) 100 < GETDATE())

 

[DarkMan]

You could try:
[tt]
DELETE *
FROM webproxy
WHERE ((webproxy.logtime) < DATEADD(day, -100, getdate()))
[/tt]

 

[digisol]

Darkman you rock!!!!

it works perfectly.

now on to my next project... haveing it search the logs for offensive words and email me the report! I have it so that I can search for one word but am getting tripped up on many ohh well I will see what I can do. If you want to see what I have so far here..

code starts here

declare @porn varchar(255)
Select @porn = 'sex'


SELECT webproxy.DestHost, webproxy.ClientIP, webproxy.logTime, webproxy.uri
FROM webproxy
WHERE (((webproxy.DestHost) Like '%' + @porn + '%'))
ORDER BY webproxy.DestHost

SELECT Winsock.DestHost, Winsock.ClientIP, Winsock.logTime, Winsock.uri
FROM Winsock
WHERE (((Winsock.DestHost) Like '%' + @porn + '%'))
ORDER BY Winsock.DestHost

SELECT webproxy.DestHost, webproxy.ClientIP, webproxy.logTime, webproxy.uri
FROM webproxy
WHERE (((webproxy.uri) Like '%' + @porn + '%'))
ORDER BY webproxy.DestHost

SELECT Winsock.DestHost, Winsock.ClientIP, Winsock.logTime, Winsock.uri
FROM Winsock
WHERE (((Winsock.uri) Like '%' + @porn + '%'))
ORDER BY Winsock.DestHost

If someone is bored, let me know what I would need to do. I have a table that list the words but I can't get it to loop the script with the different variables. I would really like to thank Darkman again, you are my hero.