Getting connnected users count missing

[ti.jensen]

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]

Hello

Try to execute the following code:

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

[ti.jensen]

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]

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]

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]

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