| Author |
Topic |
|
roy_kty
Starting Member
29 Posts |
 Posted - 2006-10-25 : 22:50:38
|
| Dear all,Now I have a table and need to count how many ppl connect to this table in current time, however those people may use the same longin name to access the table. So may I ask how to use sql to count the number of user??Thx for your answer!Roy |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-10-25 : 22:52:30
|
[code]select count(distinct user) from table[/code]
KH |
 |
|
|
roy_kty
Starting Member
29 Posts |
Posted - 2006-10-25 : 23:13:41
|
| Oh sorry I want to count how many people connect to the database but not tableI want to ask is the user can count different computer with same login name?because I have an application used in different computers and use same login name to login into the database.Thx for your answer |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-10-25 : 23:17:45
|
Is the user login information stored in any tables ? Or try to use the SQL Profiler to monitor
KH |
|
|
|
roy_kty
Starting Member
29 Posts |
Posted - 2006-10-25 : 23:33:53
|
| I have not any table to store the login information(only store the login name and password for authorization)so I think use SQL Profiler to monitor. |
|
|
|
roy_kty
Starting Member
29 Posts |
Posted - 2006-10-27 : 05:17:54
|
| Seems I run sp_who can find out the hostnames connect to the databaseThx for your help!! :) |
|
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2006-10-27 : 15:02:58
|
| You can run a query against master.dbo.syslocks. That table will give you information on all of the locks in the system at that exact time. The first field is the ID field of the object. So you can just say give me all the locks against this known objectid and voila, a list of users and the lock types against that table.Hope it helps,DaltonBlessings aren't so much a matter of "if they come" but "are you noticing them." |
|
|
|
roy_kty
Starting Member
29 Posts |
Posted - 2006-10-27 : 22:50:37
|
| I have tried the query and only have idmay I ask how can I find the information??Thx for your answer!! |
|
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2006-10-27 : 23:23:50
|
| Inside your database, you would issue a query like "select * from master.dbo.syslocks where id = object_id(Your_Table_Name)"That query will return a bunch of information regarding anyone that currently (meaning that precise millisecond) of time has some kind of lock on the table in question. Once you see the information returned you might want to change the query to something like:select count(distinct spid) ..... in order to get a count of the unique user processes locking the table. There is no way of know who read data from the table 1 millisecond before your query and is looking at those results on their screen right now. Or getting a count if the command they use doesn't issue any kind of lock at all when it reads the data. This is the best I can come up with in terms of knowing who is actively touching the table at that exact moment in time.Hope it helps,DaltonBlessings aren't so much a matter of "if they come" but "are you noticing them." |
|
|
|
|
How to use sql to count the user link to table