是系统的存储过程,在master库里可以找到,这是2000的SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO-- ============================================= -- sp_MSget_current_activity -- ============================================= alter procedure dbo.sp_MSget_current_activity @id int = 0, @option int = 0, @obj nvarchar(386) = null, @spid int = 0 asif (@id = 0) begin raiserror(N'No SPID specified (spid = %d)', 1, 1, @id) return(-1) endif (@option <= 0 or @option > 5) begin raiserror(N'Invalid option %d', 1, 1, @option) return(-1) enddeclare @stmt as nvarchar(4000)-- ============================================= -- make tables SPID depended -- ============================================= declare @locktab as sysname declare @proctab as sysnameset @locktab = N'##lockinfo' + rtrim(convert(nvarchar(5), @id)) set @proctab = N'##procinfo' + rtrim(convert(nvarchar(5), @id))if (@option = 1) begin -- process info (overview of all processes by SPID) set @stmt = N'select [Process ID], [User], [Database], [Status], [Open Transactions], [Command], [Application], [Wait Time], [Wait Type], [Wait Resource], [CPU], [Physical IO], [Memory Usage], [Login Time], [Last Batch], [Host], [Net Library], [Net Address], [Blocked By], [Blocking], [Execution Context ID] from ' + @proctab + ' order by [Process ID],[Execution Context ID]' end else if (@option = 2) begin -- distinct spid list (old) -- set @stmt = N'select [Process ID], [Blocking], [Blocked By] from ' @proctab + ' order by [Process ID]' -- distinct spid list, only spids with locks set @stmt = N'select distinct L.[Process ID], P.[Blocking], P.[Blocked By] from ' + @locktab + ' L, ' + @proctab + ' P where L.[Process ID] = P.[Process ID] order by L.[Process ID]' end else if (@option = 3) begin -- distinct object list set @stmt = N'select distinct [Object] from ' + @locktab + ' order by [Object]' end else if (@option = 4) begin -- locks per spid if (@spid = 0) begin raiserror(N'Error @spid parameter not specified (option %d)', 1, 1, @option) return(-1) end set @stmt = N'select [Object], [Lock Type], [Mode], [Status], [Owner], [Index], [Resource] from ' + @locktab + ' where [Process ID] = ' + rtrim(convert(nvarchar(10), @spid)) + ' order by [Object]' end else if (@option = 5) begin -- locks per object if (@obj is null) begin raiserror(N'Error @obj parameter not specified (option %d)', 1, 1, @option) return(-1) end -- locked object is db if parsename(@obj,3) is null begin set @stmt = N'select [Process ID], [Lock Type], [Mode], [Status], [Owner], [Index], [Resource] from ' + @locktab + ' where [Object] = ''' + @obj + ''' and [ObjID] = 0' end -- locked object is table else begin set @stmt = N'select [Process ID], [Lock Type], [Mode], [Status], [Owner], [Index], [Resource] from ' + @locktab + ' where [Object] = ''' + parsename(@obj,3) + '.' + parsename(@obj,2) + '.' + parsename(@obj,1) + '''' end end exec (@stmt) return(0) -- ============================================= -- end sp_MSget_current_activity -- =============================================GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
回11楼: 我用你之前给的代码,稍微改动一下,如下: select distinct hostname from sys.sysprocesses where not hostname = ''and datediff(mi,login_time,getdate())<=4 即可找到最近5分钟里面有和Server通信的HOSTNAME,然后我把不属于这个集合的HOSTNAME对应的用户的“在线状态”设为不在线就可以了。 问题解决,结贴!
sp_MSget_current_activity 64,1
Host列就是
貌似不是SQL server的事儿。 到别的板块看看 ?
select hostname,login_time,last_batch,[status]
from sys.sysprocesses
where not hostname = ''
我只需查看最近1分钟里面和Server通信的所有HOSTNAME,将不在这个集合里面的HOSTNAME对应的用户的“在线标记”设为不在线状态。
是系统的存储过程,在master库里可以找到,这是2000的SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO-- =============================================
-- sp_MSget_current_activity
-- =============================================
alter procedure dbo.sp_MSget_current_activity @id int = 0, @option int = 0, @obj nvarchar(386) = null, @spid int = 0
asif (@id = 0)
begin
raiserror(N'No SPID specified (spid = %d)', 1, 1, @id)
return(-1)
endif (@option <= 0 or @option > 5)
begin
raiserror(N'Invalid option %d', 1, 1, @option)
return(-1)
enddeclare @stmt as nvarchar(4000)-- =============================================
-- make tables SPID depended
-- =============================================
declare @locktab as sysname
declare @proctab as sysnameset @locktab = N'##lockinfo' + rtrim(convert(nvarchar(5), @id))
set @proctab = N'##procinfo' + rtrim(convert(nvarchar(5), @id))if (@option = 1)
begin
-- process info (overview of all processes by SPID)
set @stmt = N'select [Process ID], [User], [Database], [Status], [Open Transactions], [Command], [Application], [Wait Time], [Wait Type], [Wait Resource], [CPU], [Physical IO], [Memory Usage], [Login Time], [Last Batch], [Host], [Net Library], [Net Address], [Blocked By], [Blocking], [Execution Context ID] from ' + @proctab + ' order by [Process ID],[Execution Context ID]'
end
else if (@option = 2)
begin
-- distinct spid list (old)
-- set @stmt = N'select [Process ID], [Blocking], [Blocked By] from ' @proctab + ' order by [Process ID]' -- distinct spid list, only spids with locks
set @stmt = N'select distinct L.[Process ID], P.[Blocking], P.[Blocked By] from ' + @locktab + ' L, ' + @proctab + ' P where L.[Process ID] = P.[Process ID] order by L.[Process ID]'
end
else if (@option = 3)
begin
-- distinct object list
set @stmt = N'select distinct [Object] from ' + @locktab + ' order by [Object]'
end
else if (@option = 4)
begin
-- locks per spid
if (@spid = 0)
begin
raiserror(N'Error @spid parameter not specified (option %d)', 1, 1, @option)
return(-1)
end
set @stmt = N'select [Object], [Lock Type], [Mode], [Status], [Owner], [Index], [Resource] from ' + @locktab + ' where [Process ID] = ' + rtrim(convert(nvarchar(10), @spid)) + ' order by [Object]'
end
else if (@option = 5)
begin
-- locks per object
if (@obj is null)
begin
raiserror(N'Error @obj parameter not specified (option %d)', 1, 1, @option)
return(-1)
end
-- locked object is db
if parsename(@obj,3) is null
begin
set @stmt = N'select [Process ID], [Lock Type], [Mode], [Status], [Owner], [Index], [Resource] from ' + @locktab + ' where [Object] = ''' + @obj + ''' and [ObjID] = 0'
end
-- locked object is table
else
begin
set @stmt = N'select [Process ID], [Lock Type], [Mode], [Status], [Owner], [Index], [Resource] from ' + @locktab + ' where [Object] = ''' + parsename(@obj,3) + '.' + parsename(@obj,2) + '.' + parsename(@obj,1) + ''''
end
end
exec (@stmt)
return(0)
-- =============================================
-- end sp_MSget_current_activity
-- =============================================GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
sp_MSget_current_activity
dbo stored procedure
我用你之前给的代码,稍微改动一下,如下:
select distinct hostname
from sys.sysprocesses
where not hostname = ''and datediff(mi,login_time,getdate())<=4
即可找到最近5分钟里面有和Server通信的HOSTNAME,然后我把不属于这个集合的HOSTNAME对应的用户的“在线状态”设为不在线就可以了。
问题解决,结贴!