Sybase sp_who截断主机名 [英] Sybase sp_who truncates hostname

问题描述

我正在使用sybase服务器提供的Rapid SQL或isql连接到sql数据库。当我发出

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

sp_who username 

命令我应该获得连接用户的列表。我可以，但是主机名列被截断，因此例如当主机地址为host0001时，它会被截断为host0，这是一个问题，因为有许多主机名以相同的前缀开头，例如host001，host002 ....因此，我不知道哪些进程正在阻止我的数据库，并且我无法登录到计算机并杀死这些进程。我没有特权从数据库isql控制台中杀死它们。我必须登录计算机并在那里杀死进程（因为我有特权停止这些进程）。问题是为什么sp_who会截断此列以及如何阻止它。

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.

推荐答案

sp_who 从多个表中提取并合并信息以给出报告。它并非旨在产生可用于其他输入的输出。如果您需要sp_who的信息，最好对 sysprocesses 运行自己的查询。
不幸的是，在这种情况下，执行自己的查询无济于事，因为主机名不会被 sp_who 更改。在Sybase中，主机名由连接的应用程序设置，而不是由服务器设置（尝试从master..sysprocesses 中选择主机名，您会看到）。

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).

一种解决方案是将 set clientname 或 set clienthostname 命令添加为来自其他客户端的连接字符串的一部分。我相信可以通过ODBC完成。否则，您可以使用 sysprocesses.ipaddr 列来区分主机：

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

这篇关于Sybase sp_who截断主机名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！