January 14, 2009 at 9:23 am
I have a query that I wrote to find the user names in a login table that have been inactive for more than an hour and then deletes the records with that users name, each user can have multiple entries in the table. This works fine if running from the query window, but if I try to make into a job so I can run it on a schedule it deletes all the records in the table where the user name isn't NULL(there are entries in the table with a NULL user name). Here is the query,
DECLARE @user_name AS varchar(40)
DECLARE Inactive_Users CURSOR LOCAL FOR
SELECT USER_ID
FROM LOGINS
GROUP BY USER_ID
HAVING (NOT (USER_ID IS NULL)) AND (DATEDIFF(hh, MAX(LAST_ACTIVITY), GETDATE()) >= 1)
OPEN Inactive_Users
FETCH Inactive_Users INTO @user_name
WHILE @@fetch_status = 0
BEGIN
DELETE FROM dbo.LOGINS WHERE [USER_ID] = @user_name
FETCH NEXT FROM Inactive_Users INTO @user_name
END
CLOSE Inactive_Users
DEALLOCATE Inactive_Users
Am I missing something when taking it to a scheduled job?
thanks
January 14, 2009 at 9:30 am
Why the cursor?
DELETE FROM Logins
WHERE UserID IN (SELECT USER_ID
FROM LOGINS
WHERE UserID IS NOT NULL
GROUP BY USER_ID
HAVING ( (DATEDIFF(hh, MAX(LAST_ACTIVITY), GETDATE()) >= 1))
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 14, 2009 at 9:44 am
The cursor code came with the software but was used for finding users for a different reason. I just changed it to look for the inactivity. I will give yours a whirl. Thanks for the help.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply