Getting connnected users count missing

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
ti.jensen
Posts: 4
Joined: Thu 14 Sep 2006 07:29

Getting connnected users count missing

Post by ti.jensen » Tue 30 Nov 2010 16:16

I'm using IBDAC 2.70.0.45 and SDAC 4.70.0.44. With IBDAC, I can easily get a list of connected database users, but it seems that I can't get that with Micosoft SQL?

bork
Devart Team
Posts: 648
Joined: Fri 12 Mar 2010 07:55

Post by bork » Wed 01 Dec 2010 11:53

Hello

Try to execute the following code:

MSQuery1.SQL.Text := 'exec sp_who;';
MSQuery1.Execute;

ti.jensen
Posts: 4
Joined: Thu 14 Sep 2006 07:29

Post by ti.jensen » Wed 01 Dec 2010 15:59

Thanks; I think I can use that. I get a list of connections, with a hostname column. By counting the number of unique hostnames, I can get a count of "connections".

I found sp_who rather slow. sp_who2 is much faster.

Do you know of a way of using 'exec sp_who2' in a query?
Then I could have a where clause to limit the output of 'exec sp_who2'.

Something like 'select exec sp_who2 where xx=yy'.

ti.jensen
Posts: 4
Joined: Thu 14 Sep 2006 07:29

Post by ti.jensen » Wed 01 Dec 2010 16:16

I tried this, which runs very fast:

use master;
select count(hostname) as UserCount from sys.sysprocesses with (nolock)
where hostname'' and dbid0 and db_name(dbid)='MyDatabaseName'
group by hostname

It returns a count in UserCount as the number of hosts connected to the database.

ti.jensen
Posts: 4
Joined: Thu 14 Sep 2006 07:29

Post by ti.jensen » Wed 01 Dec 2010 16:40

Sorry, this works better:

use [MyDatabaseName];
select count(hostname) as UserCount from sys.sysprocesses with (nolock)
where hostname'' and dbid0 and db_name(dbid)='MyDatabaseName'
group by hostname

bork
Devart Team
Posts: 648
Joined: Fri 12 Mar 2010 07:55

Post by bork » Thu 02 Dec 2010 08:13

It is good to see that this problem has been solved. If any other questions come up, please contact me.

Post Reply