return SP_WHO sybase result

One Star

return SP_WHO sybase result

Hi
I would like to display the result of SP_WHO into excel file.
the probleme is when I used a tSybaseRow no results return

SP_WHO is de default sybas e fonction return information about DB
Do you have any lead to resolved my issues
DN
One Star

Re: return SP_WHO sybase result

denis,
dont' forget that SP_WHO is a stored procedure...
it's quite special...
see u tomorrow... ( we are working on the same company )
One Star

Re: return SP_WHO sybase result

1) SP_WHO is not "about DB" in general, but specifically about _current user sessions_ in the Sybase data server, _whatever the database_.
2) SP_WHO returns *multiple resultsets* in a TDS stream; that's not easy to handle, and I know no ETL tool that could switch dynamically the column list and column format for every new resultset

If you want a list of current user sessions you can tap directly in the system tables; below a "simple" query that works with ASE 12.5 but has not been tested with 15.x
NB: for sessions that run on another database, you can't get the actual DB user, that would require dynamic sub-queries and admin privileges. Hence the 'unknown' comment.
select ps.spid,
case when ps.spid =@@spid
then '#current'
when ps.cmd ='AWAITING COMMAND'
then 'idle'
else rtrim(ps.cmd) +' ' +rtrim(ps.status)
end as status,
usr.name as "login",
case when ps.dbid =db_id()
then dbusr.name
else 'unknown'
end + ' @ ' +db.name as "db user",
ps.hostname as "client",
case when charindex(ps.hostname, ps.program_name) =1
then ''
else rtrim(ps.program_name)
end as "program",
ps.tran_name as "transaction",
case when ps.blocked =0
then null
else ps.blocked
end as "blocked by"
from master.dbo.sysprocesses ps
join master.dbo.sysdatabases db
on ps.dbid =db.dbid
join master.dbo.syslogins usr
on ps.suid =usr.suid
left join sysusers dbusr
on ps.uid =dbusr.uid
order by
case when ps.spid =@@spid
then -1000
when ps.dbid =db_id()
then case when ps.tran_name is not null or ps.blocked >0 then 0
when ps.cmd ='AWAITING COMMAND' then 666
else 333
end
+ps.spid
else ps.dbid *1000
+case when ps.tran_name is not null or ps.blocked >0 then 0
when ps.cmd ='AWAITING COMMAND' then 666
else 333
end
+ps.spid
end
One Star

Re: return SP_WHO sybase result

SybaseUnHappyUser
I'm using this query . It's pretty same

select p.spid, p.status, l.name, p.hostname, p.program_name, p.hostprocess, p.cmd, p.cpu, p.physical_io, p.memusage, p.blocked, db_name(p.dbid) from master.dbo.sysprocesses p, master.dbo.syslogins l where p.suid *= l.suid