On Oct 7, 8:06*am, "kai" <kaili...@bells outh.netwrote:
Hi,
*I use VB 2008 as front end, SQL Server 2005/2008 as back end, I want
control number of cucurrent users log on to the database, I am trying to use
a stored procedure, but it did not work. Is this possible? Please help.
Thanks
Kai
Hi Kai,
As part of your application's login, you might want to try querying
sys.sysprocesse s. In SQL 2005/2008, this is a system view that shows
information on all current connections to your SQL Server Instance.
http://msdn.microsoft.com/en-us/library/ms179881.aspx
When your VB app connects to the database, you are able to supply the
program_name as part of the connection string-- this application name
is available in sysprocesses. I presume that your interest in
counting connections is so that you can control licensing of your
application. You can use this basic query to count connections by
your application:
SELECT COUNT(*)
FROM master.dbo.sysp rocesses
WHERE program_name = 'MyApplication'
If your application creates multiple connections to the database, you
might end up counting that single user more than once. If that's the
case, you might need to try a variation utilizing other columns in
sysprocesses.
SELECT COUNT(DISTINCT hostname)
FROM master.dbo.sysp rocesses
WHERE program_name = 'MyApplication'
SELECT COUNT(DISTINCT nt_username)
FROM master.dbo.sysp rocesses
WHERE program_name = 'MyApplication'