sql server new users: When I run 'select * from master.dbo.sysprocesses' in Query Analyzer I
get all the expected columns. If I run this from a program then I
cannot see certain columns, like hostname. I'm connecting using the sa
user id.

I'm guessing some columns are restricted somehow for security reasons.
Does anyone know how I can get all the columns into my program query?
This is a Delphi program using ODBC to connect to SQL 2000.

Thanks

What information are you seeking? (There may be better methods to get that
information.)

For example, the system function host_name() will provide the name of the
connecting device. There are most likely other system functions to obtain
the data in sysprocesses.

--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."

*Yet Another Certification Exam


[quoted text, click to view]

I want to see the users who are connected to my databases. Also who is
blocked and blocking. I know I can see some of this with sp_who and
related, but I need the info in my program. Same result set restriction
is in effect for those procedures.

The info I need seems readily available in sysprocesses and

Are you trying something like this?

SELECT
spid
, blocked
, d.name AS 'db_name'
, nt_username
, loginame
, u.name AS 'user_name'
, net_address
, hostname
, program_name
, cmd
FROM master.dbo.sysprocesses p
LEFT JOIN master.dbo.sysdatabases d
ON p.dbid =3D d.dbid
LEFT JOIN sysusers u
ON p.uid =3D u.uid
ORDER BY=20
p.db_name
, p.loginame
, p.net_address DESC

Have you tried creating a Stored Procedure and calling the stored =
procedure from the application?

--=20
Arnie Rowland, YACE*=20
"To be successful, your heart must accompany your knowledge."

*Yet Another Certification Exam


[quoted text, click to view]