How to determine concurrent user ?

Question

Post reply

How to determine concurrent user ?

Roy Keane

SSC Rookie

Points: 38
More actions
November 7, 2006 at 1:43 am

#91890

Let say I have a system that restricted maximun 5 users login concurrently. I want to check the number of users currently using the system so that I can prompt an error message when the 6th user trying to login. Anyone has the solution ?
Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

sp_who returns info about users and processes connected to SQL Server. It's in BooksOnLine.

Greg

Lowell SSC Guru Points: 323518 More actions · Answer 2

It kind of sounds like he wants a limit for an application, to prevent more than 5 instances, instead of 5 users to the sql database.... am i right?

you'd still use SP_WHO or SP_WHO2 to find out who's connected, but you'd most likely filter on the ProgramName column of that data, and count those users.

You would need to make sure your application uses that in the connection string; ie

oconnection.open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=eg;Application Name=TestApp"

the application would then have to say there are 5 users already connected and terminate.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Roy Keane SSC Rookie Points: 38 More actions · Answer 3

If I filter on the ProgramName column, I need to count the number of user using HostName column ? How if 2 different users connected have same HostName ?

Lowell SSC Guru Points: 323518 More actions · Answer 4

assuming that one machine can run as many instances as you want, but only 5 users, this might get you started:

in my poor example below, you can see that the machine STORMSQL is using two instances of enterprise manager. If i were to uncomment my filter and ProgramName='MyApplication'

we would end up with only rows that have different machines AND using the correct application;

from that recordset you can count rows and make your application bail out or ask for more licenses or whatever.

since hostname=machinename, you generally have one person using the machine unless someone is using a terminal server or something; so you could do a double restriction: no more than 5 instances on any one machine, and no more than 5 hostnames using the application.

NumInstancesPerMachine	hostname	ProgramName
2	STORMSQL	MS SQLEM
1	STORMSQL	SQL Query Analyzer
1	STORMSQL	SQL Query Analyzer - Object Browser

create table #tempwho (

SPID int ,

Status varchar(60) ,

Login varchar(60) ,

HostName varchar(60) ,

BlkBy varchar(60) ,

DBName varchar(60) ,

Command varchar(60) ,

CPUTime int ,

DiskIO int ,

LastBatch varchar(60) ,

ProgramName varchar(60) ,

SPID2 int )

insert into #tempwho

exec sp_who2

select count(ProgramName) as NumInstancesPerMachine, hostname,ProgramName from #tempwho

where spid >=50

--and ProgramName='MyApplication'

group by hostname,ProgramName

drop table #tempwho

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Roy Keane SSC Rookie Points: 38 More actions · Answer 5

Roy Keane

SSC Rookie

Points: 38

November 7, 2006 at 9:00 pm

#670455

Thanks so much. I've solved my problem.

Lowell SSC Guru Points: 323518 More actions · Answer 6

hope what i posted was helpful.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!