Sybase sp_who truncates hostname

Question

I am connecting to sql databse using Rapid SQL or isql provided by sybase server. When I issue

sp_who username 

command I should get list of connected users. I do but hostname column is truncated so for example when host address is host0001 it get truncated to host0 this is a problem because there are many hosts which names start with same prefix e.g. host001, host002.... Because of that I do not know which processes are blocking my database and I can not log to the machine and kill those processes. I do not have privileges to kill them from database isql console. I have to log to machine and kill process there (because I have privileges to stop those processes). The question is why sp_who truncates this column and how to prevent it.

Answer 1

sp_who pulls and joins information from several tables to give it's report. It's not intended to produce output that can be used for input on something else. If you need information from sp_who, it's better to just run your own query against sysprocesses. Unfortunately, in this case, doing your own query won't help, as the hostname is not changed by sp_who. In Sybase the hostname is set by the application that is connecting, not the server (try select hostname from master..sysprocesses and you'll see).

One solution would be to add a set clientname or set clienthostname command as part of your connection string from these other clients. I believe this can be done via ODBC. Othewise you can use the sysprocesses.ipaddr column to differentiate between hosts:

select fid, spid, status, suser_name(suid), clienthostname, ipaddr
from master..sysprocesses
where suid = suser_id('username')
order by ipaddr, spid