count number of connections in sql

This is probably a newbie quesiton, but I would like to be able to count how
many users connect to my db for evvery 30 minutes. How may I do this within
sql 2k?
Thank you in advanceSELECT COUNT(DISTINCT spid) FROM master..sysprocesses WHERE spid > 50
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:06F88966-654C-449D-8085-9F87DC7455DF@.microsoft.com...
> This is probably a newbie quesiton, but I would like to be able to count
> how
> many users connect to my db for evvery 30 minutes. How may I do this
> within
> sql 2k?
> Thank you in advance|||Thank you for responding! I think that this code will give me a snapshot of
how many are connected at a given time, but I need to know how many total
have connected in the last 30 minutes. perhaps I can place a trigger on this
table that updates a count in another table whenever a new process (where
spid>50) is spawned?
"Aaron [SQL Server MVP]" wrote:

> SELECT COUNT(DISTINCT spid) FROM master..sysprocesses WHERE spid > 50
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
> news:06F88966-654C-449D-8085-9F87DC7455DF@.microsoft.com...
>
>|||No trigger on system tables, sorry. You will have to poll the table
constantly. Plus, if I connect and get assigned spid 52, you poll, then I
disconnect and someone else connects and gets assigned 52, you won't see the
difference unless you compare the deltas in all columns (which still might
not yield a discrepancy).
You might consider tracking this more from the application side, e.g. if you
have a GUI where people are logging in, then in the stored procedure that
checks their credentials, log the connection in some table. SQL Server
isn't going to provide you this kind of information directly.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:D84485D0-59F2-4298-89DC-04AFDC5B29DA@.microsoft.com...
> Thank you for responding! I think that this code will give me a snapshot
> of
> how many are connected at a given time, but I need to know how many total
> have connected in the last 30 minutes. perhaps I can place a trigger on
> this
> table that updates a count in another table whenever a new process (where
> spid>50) is spawned?
> "Aaron [SQL Server MVP]" wrote:
>|||Thank you for your help!!
"Aaron [SQL Server MVP]" wrote:

> No trigger on system tables, sorry. You will have to poll the table
> constantly. Plus, if I connect and get assigned spid 52, you poll, then I
> disconnect and someone else connects and gets assigned 52, you won't see t
he
> difference unless you compare the deltas in all columns (which still might
> not yield a discrepancy).
> You might consider tracking this more from the application side, e.g. if y
ou
> have a GUI where people are logging in, then in the stored procedure that
> checks their credentials, log the connection in some table. SQL Server
> isn't going to provide you this kind of information directly.
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
> news:D84485D0-59F2-4298-89DC-04AFDC5B29DA@.microsoft.com...
>
>

Count from multiple tables

I have 4 tables
One is a user table and the other three contain records for the users. They all have a USERNAME column
I would like to get a count of records for each table grouped by USERNAME

My output would be:
username,totalFrom1,totalFrom2,totalFrom3

Thanks For the help!Mybe something like:

select
username,
isnull(t1.ttlfrom1,0) ttlfrom1,
isnull(t2.ttlfrom2,0) ttlfrom2,
isnull(t3.ttlfrom3,0) ttlfrom3
from <users> u
left join
(
select username, count(username) ttlfrom1
from
<t1>
group by username
) t1 on t1.username = u.username
left join
(
select username, count(username) ttlfrom2
from
<t2>
group by username
) t2 on t2.username = u.username
left join
(
select username, count(username) ttlfrom3
from
<t3>
group by username
) t3 on t3.username = u.username

|||Thanks! I would never have figured that one out on my own, but I see how it works.
Thanks again
Greg

Could not find stored procedure "addUser" - Please help

Hi ,

Can someone tell me why I am getting the following error while I am trying to add users in a database.:

:System.Data.SqlClient.SqlException Could not find stored procedure "addUser"

where should we normally keep our stored procedures? ( i am new to sql server)

thank you in advance

I presume that addUser is an sp that you have written? If so which database was it create in and which database is your current one?

As an example for many connections master is the default database so it may be created there and you are trying to run in the application database? Or you could be in master now and the SP could be in your database. You can fix this by always doing a "use database" before you run any commands

|||From the point that addUser is not a procedure written by you, I have to tell you that there is actually no adduser procedure in SQL Server by default. Users are created by using the CREATE USER commands and no SP. If you mean the (tobe) deprecated procedurte sp_adduser, you sure have to call it with the right name that you can access it the whole name is that with the sp_ writte in front of your mentioned procedure.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de