April 22, 2008 at 2:08 am
We have a product that maintains a license for number of users that can login simultaneously.
Currently the system uses a connect stored procedure which gets called by our software when the user logs in as follows:-
INSERT INTO [Connection] ([User],LoginTime)
SELECT SYSTEM_USER, [login_time]
FROM [master].[dbo].[sysprocesses]
WHERE [master].[dbo].[sysprocesses].[spid] = @@SPID
We also have stored procedure called cleanconnections which gets called at login time and also at various times in the product when users open and close clients which does this:-
DELETE FROM Connection
WHERE NOT EXISTS(
SELECT * FROM [master].[dbo].[sysprocesses]
WHERE [Connection].[ConnectionId] = [master].[dbo].[sysprocesses].[spid]
AND [Connection].[LoginTime] = [master].[dbo].[sysprocesses].[login_time])
The theory is then we can do a select count(*) from connections in our software to find out how many users are connected at anyone time to check the licence limit is not being exceeded.
We have found recently though that connections have been lost from our connections table and can only assume the login time has changed in sysprocesses as we can't see the SPID will have been removed or could it???
I have read posts that indicate sysprocesses is a SQL 2000 view and alternatives should be use for 2005 if so what are these alternatives?
Our product needs to work with 2000, 2005 32 bit, 64 bit and also a clustered environment. We can alter the stored procs accordingly for the target environment.
Thanks
April 22, 2008 at 6:04 am
I would recommend using the system stored procedure sp_who or sp_who2. If you create a temp table, you can insert the results of the procedure into the temp table and then query for your application.
This would be a much safer approach than querying system tables that MS can change any time they want.
April 22, 2008 at 2:57 pm
SPID's do get re-used, you know.
SysProcesses should be fine.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 23, 2008 at 1:17 am
Thanks for the repliies guys.
SPID's do get re-used, you know.
Yes we are aware of that, and that is covered by our cleanconnections sp, also when we connect we check to see if @@SPID exists in our local connection table, if it does then it get's removed.
One gotcha we did have was that we were checking the logintime from sysprocesses against our copy of the logintime and found that sometimes the sysprocesses one changed resulting in the connection being removed from our connections table. So we now only check the on SPID.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply