Cantidad de usuarios conectados desde una aplicación

[Mauro Pasetti [ar]]

Hola:
Les cuento que cuando me conecto a la base de datos lo realizo con la
siguiente información:

Conexión= GLO:ServerName & ',' & GLO:DataBase & ',' & GLO:UsuarioAPP & ',' &
LOC:PasswordMotor & ';LANGUAGE=Spanish;APP=Advgold;WSID=' & GLO:Computadora.

GLO:UsuarioAPP es el usuario que se loguea al sistema (ID de usuario) en
Windows 2000 Server.

Mi intención es saber cuántos usuarios están conectados a la base de datos
por medio de mi aplicación "Advgold"
Les comento que todavía no he consultado la ayuda al respecto pero ya estoy
en eso.
También me gustaría saber si es posible conocer la cantidad de conexiones
desde una misma estación de trabajo (la variable que se envía es
GLO:Computadora) para la misma aplicación.

Desde ya muchas gracias

[Eladio Rincón]

sp_who te devuelve todos los usuarios conectados al servidor; podrías
recuperarlo desde un recordset, y recorrerlo para buscar los que su
program_name sea 'tu_programa'; algo así:

dim rs as adodb.recordset
set rs = new adodb.recordset
set rs = conn.execute ("sp_who")
do while not rs.eof
debug.print rs!program_name & ";" & rs!loginame
loop

Tambien puedes consultar la tabla sysprocesses de la bd master, aunque
Microsoft no recomienda que se consulten las tablas de sistema por eso de la
compatibilidad con versiones posteriores:

select distinct loginame from master..sysprocesses where program_name =
'tu_programa'

--
Eladio Rincón
tvel...@torrevieja.infoville.net

"Mauro Pasetti [ar]" <mau...@ipp.com.ar> escribió en el mensaje
news:OdubrQo8AHA.1320@tkmsftngp05...

[Ezequiel]

Prueba con este SP

Saludos

create procedure SP_CONEXIONES_ACTIVAS
(
@spid int = 0,
@dbname nvarchar(30) = N'%',
@loginame nvarchar(256) = N'%',
@hostname nvarchar(256) = N'%',
@programname nvarchar(256) = N'%',
@ntusername nvarchar(256) = N'%',
@onlyblocked bit = 0,
@orderby varchar(30) = 'spid',
@report varchar(10) = NULL
)
as
if (@dbname <> N'%') and (db_id(@dbname) is null)
begin
raiserror ('Invalid database name specified.', 1, 2)
return(-1)
end
set @orderby = lower(isnull(@orderby, 'spid'))
if (@orderby not in ('spid', 'cpu', 'cpu desc', 'io', 'io
desc', 'host', 'program',
'ntuser', 'sqllogin', 'lastbatch desc', 'lastbatch'))
begin
raiserror ('Invalid order by option specified.', 1, 2)
return(-1)
end
set @report = lower(@report)
if @report not in ('users') and @report is not null
begin
raiserror ('Invalid report option specified.', 1, 2)
return(-1)
end
if @report = 'users'
begin
select convert(varchar(30), nt_username) as "NT User Name",
convert(varchar(50), loginame) as "SQL Login Name",
count(*) as "Number of Connections"
from master..sysprocesses
group by nt_username, loginame
order by 3 desc, 2, 1
compute sum(count(*)), count(convert(varchar(50), loginame))
return(0)
end
if @onlyblocked = 1 and
exists(select * from master..sysprocesses where blocked > 0)
select spid as "Blocking Process ID",
convert(varchar(15), case dbid when 0 then 'no database context'
else db_name(dbid)
end) as "Database",
cmd as "Current Command",
convert(varchar(20), status) as "Process ID Status",
blocked as "Blocking Process ID",
cpu as "CPU Time",
physical_io "DISK I/O",
last_batch as "Last Batch",
lastwaittype as "Last Waittype",
waittime as "Current Waittime",
convert(varchar(30), waitresource) as "Wait Resource",
convert(varchar(15), hostname) as "Host Name",
convert(varchar(30), program_name) as "Program Name",
convert(varchar(20), nt_username) as "NT User Name",
convert(varchar(30), loginame) as "SQL Login Name"
from master..sysprocesses p1
where exists(select * from master..sysprocesses p2
where p2.blocked = p1.spid)

select spid as "Process ID",
convert(varchar(15), case dbid when 0 then 'no database context'
else db_name(dbid)
end) as "Database",
cmd as "Current Command",
convert(varchar(20), status) as "Process ID Status",
blocked as "Blocking Process ID",
cpu as "CPU Time",
physical_io "DISK I/O",
last_batch as "Last Batch",
lastwaittype as "Last Waittype",
waittime as "Current Waittime",
convert(varchar(30), waitresource) as "Wait Resource",
convert(varchar(15), hostname) as "Host Name",
convert(varchar(30), program_name) as "Program Name",
convert(varchar(20), nt_username) as "NT User Name",
convert(varchar(30), loginame) as "SQL Login Name"
from master..sysprocesses
where nt_username like @ntusername + '%' and
program_name like @programname + '%' and
hostname like @hostname + '%' and
loginame like @loginame + '%' and
spid = (case @spid when 0 then spid else @spid end) and
dbid = (case when @dbname = '%' then dbid else db_id(@dbname) end) and
((blocked > 0 and @onlyblocked = 1) or (@onlyblocked = 0))
order by case @orderby
when 'spid' then spid
when 'cpu' then cpu
when 'cpu desc' then -1*cpu
when 'io' then physical_io

when 'io desc' then -1*physical_io
when 'host' then 0
when 'program' then 0
when 'ntuser' then 0
when 'sqllogin' then 0
when 'lastbatch desc' then -1*cast(last_batch as float)
end,
case @orderby
when 'spid' then ''
when 'cpu' then ''
when 'cpu desc' then ''
when 'io' then ''
when 'io desc' then ''
when 'host' then hostname
when 'program' then program_name
when 'ntuser' then nt_username
when 'sqllogin' then loginame
when 'lastbatch' then convert(varchar, last_batch, 121)
end

-----Mensaje original-----

--
Eladio Rincón
tvel...@torrevieja.infoville.net

.