Getting the number of connected users

Question

Post reply

Getting the number of connected users

Charlottecb

Hall of Fame

Points: 3932
More actions
March 2, 2004 at 5:53 am

#107312

Hi All,
Can someone tell me how to get the number of users connected to my db (SQLServer 2000).
Thanks
CCB

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

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

noeld SSC Guru Points: 96590 More actions · Answer 1

Select Count(Distinct loginame) as NoOfUsers

FROM OPENROWSET('SQLOLEDB','Server=(local);trusted_connection=yes','SET FMTONLY OFF exec master..sp_who') AS Q

Where Q.dbName ='YourDBName'

* Noel

Charlottecb Hall of Fame Points: 3932 More actions · Answer 2

Can I do this without the OpenRows set, as I am running it from PowerBuilder and I don't think it will like this.

Ideally I want it in a stored proc and without having to specify a connection string as I will already be connected to the db.

Thanks

CCB

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

As you seem you only want this for a specific db and not the whole server, you can pipe the results of sp_who into a (temp) table and select then only those for your db in question.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Charlottecb Hall of Fame Points: 3932 More actions · Answer 4

Charlottecb

Hall of Fame

Points: 3932

March 2, 2004 at 7:12 am

#496653

How would I do this? (I am new to this)

Thanks

CCB

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

OK, although I'm not sure what is better and not quite sure on the size of the columns.

if object_id('#t_users') is not null

drop table #t_users

go

create table #t_users(

spid int,

ecid int,

status nvarchar(20),

loginame nvarchar(50),

hostname nvarchar(50),

blk nvarchar(10),

dbname nvarchar(50),

cmd nvarchar(200))

insert into #t_users exec sp_who

select * from #t_users where dbname = 'your_dbname' --and status 'Active'

drop table #t_users

go

By directly querying the system tables you can get the results by doing a

select

spid, status, loginame, hostname, blocked, db_name(dbid), cmd

from

master..sysprocesses

where

db_name(dbid)='JTheseus'

...but as this is not recommended, just forget the latter.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

noeld SSC Guru Points: 96590 More actions · Answer 6

CCB:

You Can wrap the Above code in an sp:

Like:

CREATE PROCEDURE usp_Users_in_DB (@dbname varchar(128))

AS

Select Count(Distinct loginame) as NoOfUsers

FROM OPENROWSET('SQLOLEDB','Server=(local);trusted_connection=yes','SET FMTONLY OFF exec master..sp_who') AS Q

Where Q.dbName =@dbname

Or if you prefer the tmp table method:

CREATE PROCEDURE usp_Users_in_DB (@dbname varchar(128))

AS

if object_id('#T1) is not null

drop table #T1

GO

CREATE TABLE #T1 (

[spid] [smallint] ,

[ecid] [smallint] ,

[status] [nchar] (30) ,

[loginame] [nvarchar] (128) ,

[hostname] [nchar] (128) ,

[blk] [char] (5) ,

[dbname] [nvarchar] (128) ,

[cmd] [nchar] (16)

)

INSERT INTO #T1 EXEC sp_who

Select Count(Distinct loginame) as NoOfUsers

FROM #T1 Q Where Q.dbName = @dbname

* Noel

Charlottecb Hall of Fame Points: 3932 More actions · Answer 7

Thanks for your help Noeld / Frank.

Much appreciated.

CCB