SQLSERVER 执行sp_who2 显示的LastBatch列时间显示不正确
来源：互联网 发布：最好的英语听力软件 编辑：程序博客网 时间：2024/05/16 17:00
SET QUOTED_IDENTIFIER ONSET ANSI_NULLS ONGOcreate procedure sys.sp_who2 --- 1995/11/03 10:16 @loginame sysname = NULLasset nocount ondeclare @retcode intdeclare @sidlow varbinary(85) ,@sidhigh varbinary(85) ,@sid1 varbinary(85) ,@spidlow int ,@spidhigh intdeclare @charMaxLenLoginName varchar(6) ,@charMaxLenDBName varchar(6) ,@charMaxLenCPUTime varchar(10) ,@charMaxLenDiskIO varchar(10) ,@charMaxLenHostName varchar(10) ,@charMaxLenProgramName varchar(10) ,@charMaxLenLastBatch varchar(10) ,@charMaxLenCommand varchar(10)declare @charsidlow varchar(85) ,@charsidhigh varchar(85) ,@charspidlow varchar(11) ,@charspidhigh varchar(11)-- defaultsselect @retcode = 0 -- 0=good ,1=bad.select @sidlow = convert(varbinary(85), (replicate(char(0), 85)))select @sidhigh = convert(varbinary(85), (replicate(char(1), 85)))select @spidlow = 0 ,@spidhigh = 32767--------------------------------------------------------------IF (@loginame IS NULL) --Simple default to all LoginNames. GOTO LABEL_17PARM1EDITED-- select @sid1 = suser_sid(@loginame)select @sid1 = nullif exists(select * from sys.syslogins where loginname = @loginame)select @sid1 = sid from sys.syslogins where loginname = @loginameIF (@sid1 IS NOT NULL) --Parm is a recognized login name. begin select @sidlow = suser_sid(@loginame) ,@sidhigh = suser_sid(@loginame) GOTO LABEL_17PARM1EDITED end--------IF (lower(@loginame collate Latin1_General_CI_AS) IN ('active')) --Special action, not sleeping. begin select @loginame = lower(@loginame collate Latin1_General_CI_AS) GOTO LABEL_17PARM1EDITED end--------IF (patindex ('%[^0-9]%' , isnull(@loginame,'z')) = 0) --Is a number. begin select @spidlow = convert(int, @loginame) ,@spidhigh = convert(int, @loginame) GOTO LABEL_17PARM1EDITED end--------raiserror(15007,-1,-1,@loginame)select @retcode = 1GOTO LABEL_86RETURNLABEL_17PARM1EDITED:-------------------- Capture consistent sysprocesses. -------------------select spid ,status ,sid ,hostname ,program_name ,cmd ,cpu ,physical_io ,blocked ,dbid ,convert(sysname, rtrim(loginame)) as loginname ,spid as 'spid_sort' , substring( convert(varchar,last_batch,111) ,6 ,5 ) + ' ' + substring( convert(varchar,last_batch,113) ,13 ,8 )--这个地方时间截取有问题 应改成+ substring( convert(varchar,last_batch,113) ,12 ,8 ) as 'last_batch_char' ,request_id into #tb1_sysprocesses from sys.sysprocesses_ex with (nolock)--系统无法找这个表 只能改成sys.sysprocessesif @@error <> 0beginselect @retcode = @@errorGOTO LABEL_86RETURNend--------Screen out any rows?if (@loginame in ('active')) delete #tb1_sysprocesses where lower(status) = 'sleeping' and upper(cmd) in ( 'AWAITING COMMAND' ,'LAZY WRITER' ,'CHECKPOINT SLEEP' ) and blocked = 0--------Prepare to dynamically optimize column widths.select @charsidlow = convert(varchar(85),@sidlow) ,@charsidhigh = convert(varchar(85),@sidhigh) ,@charspidlow = convert(varchar,@spidlow) ,@charspidhigh = convert(varchar,@spidhigh)select @charMaxLenLoginName = convert( varchar ,isnull( max( datalength(loginname)) ,5) ) ,@charMaxLenDBName = convert( varchar ,isnull( max( datalength( rtrim(convert(varchar(128),db_name(dbid))))) ,6) ) ,@charMaxLenCPUTime = convert( varchar ,isnull( max( datalength( rtrim(convert(varchar(128),cpu)))) ,7) ) ,@charMaxLenDiskIO = convert( varchar ,isnull( max( datalength( rtrim(convert(varchar(128),physical_io)))) ,6) ) ,@charMaxLenCommand = convert( varchar ,isnull( max( datalength( rtrim(convert(varchar(128),cmd)))) ,7) ) ,@charMaxLenHostName = convert( varchar ,isnull( max( datalength( rtrim(convert(varchar(128),hostname)))) ,8) ) ,@charMaxLenProgramName = convert( varchar ,isnull( max( datalength( rtrim(convert(varchar(128),program_name)))) ,11) ) ,@charMaxLenLastBatch = convert( varchar ,isnull( max( datalength( rtrim(convert(varchar(128),last_batch_char)))) ,9) ) from #tb1_sysprocesses where spid >= @spidlow and spid <= @spidhigh--------Output the report.EXEC('SET nocount offSELECT SPID = convert(char(5),spid) ,Status = CASE lower(status) When ''sleeping'' Then lower(status) Else upper(status) END ,Login = substring(loginname,1,' + @charMaxLenLoginName + ') ,HostName = CASE hostname When Null Then '' .'' When '' '' Then '' .'' Else substring(hostname,1,' + @charMaxLenHostName + ') END ,BlkBy = CASE isnull(convert(char(5),blocked),''0'') When ''0'' Then '' .'' Else isnull(convert(char(5),blocked),''0'') END ,DBName = substring(case when dbid = 0 then null when dbid <> 0 then db_name(dbid) end,1,' + @charMaxLenDBName + ') ,Command = substring(cmd,1,' + @charMaxLenCommand + ') ,CPUTime = substring(convert(varchar,cpu),1,' + @charMaxLenCPUTime + ') ,DiskIO = substring(convert(varchar,physical_io),1,' + @charMaxLenDiskIO + ') ,LastBatch = substring(last_batch_char,1,' + @charMaxLenLastBatch + ') ,ProgramName = substring(program_name,1,' + @charMaxLenProgramName + ') ,SPID = convert(char(5),spid) --Handy extra for right-scrolling users. ,REQUESTID = convert(char(5),request_id) from #tb1_sysprocesses --Usually DB qualification is needed in exec(). where spid >= ' + @charspidlow + ' and spid <= ' + @charspidhigh + ' -- (Seems always auto sorted.) order by spid_sortSET nocount on')LABEL_86RETURN:if (object_id('tempdb..#tb1_sysprocesses') is not null) drop table #tb1_sysprocessesreturn @retcode -- sp_who2GO因为修改系统存储过程麻烦 故新增一个存储过程代替sp_who2
SET QUOTED_IDENTIFIER ONSET ANSI_NULLS ONGOcreate procedure sys.sp_who4 --- 2013-06-20 @loginame sysname = NULLasset nocount ondeclare @retcode intdeclare @sidlow varbinary(85) ,@sidhigh varbinary(85) ,@sid1 varbinary(85) ,@spidlow int ,@spidhigh intdeclare @charMaxLenLoginName varchar(6) ,@charMaxLenDBName varchar(6) ,@charMaxLenCPUTime varchar(10) ,@charMaxLenDiskIO varchar(10) ,@charMaxLenHostName varchar(10) ,@charMaxLenProgramName varchar(10) ,@charMaxLenLastBatch varchar(10) ,@charMaxLenCommand varchar(10)declare @charsidlow varchar(85) ,@charsidhigh varchar(85) ,@charspidlow varchar(11) ,@charspidhigh varchar(11)-- defaultsselect @retcode = 0 -- 0=good ,1=bad.select @sidlow = convert(varbinary(85), (replicate(char(0), 85)))select @sidhigh = convert(varbinary(85), (replicate(char(1), 85)))select @spidlow = 0 ,@spidhigh = 32767--------------------------------------------------------------IF (@loginame IS NULL) --Simple default to all LoginNames. GOTO LABEL_17PARM1EDITED-- select @sid1 = suser_sid(@loginame)select @sid1 = nullif exists(select * from sys.syslogins where loginname = @loginame)select @sid1 = sid from sys.syslogins where loginname = @loginameIF (@sid1 IS NOT NULL) --Parm is a recognized login name. begin select @sidlow = suser_sid(@loginame) ,@sidhigh = suser_sid(@loginame) GOTO LABEL_17PARM1EDITED end--------IF (lower(@loginame collate Latin1_General_CI_AS) IN ('active')) --Special action, not sleeping. begin select @loginame = lower(@loginame collate Latin1_General_CI_AS) GOTO LABEL_17PARM1EDITED end--------IF (patindex ('%[^0-9]%' , isnull(@loginame,'z')) = 0) --Is a number. begin select @spidlow = convert(int, @loginame) ,@spidhigh = convert(int, @loginame) GOTO LABEL_17PARM1EDITED end--------raiserror(15007,-1,-1,@loginame)select @retcode = 1GOTO LABEL_86RETURNLABEL_17PARM1EDITED:-------------------- Capture consistent sysprocesses. -------------------select spid ,status ,sid ,hostname ,program_name ,cmd ,cpu ,physical_io ,blocked ,dbid ,convert(sysname, rtrim(loginame)) as loginname ,spid as 'spid_sort' , substring( convert(varchar,last_batch,111) ,6 ,5 ) + ' '+ substring( convert(varchar,last_batch,113) ,12 ,8 ) as 'last_batch_char' ,request_id into #tb1_sysprocesses from sys.sysprocesses with (nolock)sys.sysprocessesif @@error <> 0beginselect @retcode = @@errorGOTO LABEL_86RETURNend--------Screen out any rows?if (@loginame in ('active')) delete #tb1_sysprocesses where lower(status) = 'sleeping' and upper(cmd) in ( 'AWAITING COMMAND' ,'LAZY WRITER' ,'CHECKPOINT SLEEP' ) and blocked = 0--------Prepare to dynamically optimize column widths.select @charsidlow = convert(varchar(85),@sidlow) ,@charsidhigh = convert(varchar(85),@sidhigh) ,@charspidlow = convert(varchar,@spidlow) ,@charspidhigh = convert(varchar,@spidhigh)select @charMaxLenLoginName = convert( varchar ,isnull( max( datalength(loginname)) ,5) ) ,@charMaxLenDBName = convert( varchar ,isnull( max( datalength( rtrim(convert(varchar(128),db_name(dbid))))) ,6) ) ,@charMaxLenCPUTime = convert( varchar ,isnull( max( datalength( rtrim(convert(varchar(128),cpu)))) ,7) ) ,@charMaxLenDiskIO = convert( varchar ,isnull( max( datalength( rtrim(convert(varchar(128),physical_io)))) ,6) ) ,@charMaxLenCommand = convert( varchar ,isnull( max( datalength( rtrim(convert(varchar(128),cmd)))) ,7) ) ,@charMaxLenHostName = convert( varchar ,isnull( max( datalength( rtrim(convert(varchar(128),hostname)))) ,8) ) ,@charMaxLenProgramName = convert( varchar ,isnull( max( datalength( rtrim(convert(varchar(128),program_name)))) ,11) ) ,@charMaxLenLastBatch = convert( varchar ,isnull( max( datalength( rtrim(convert(varchar(128),last_batch_char)))) ,9) ) from #tb1_sysprocesses where spid >= @spidlow and spid <= @spidhigh--------Output the report.EXEC('SET nocount offSELECT SPID = convert(char(5),spid) ,Status = CASE lower(status) When ''sleeping'' Then lower(status) Else upper(status) END ,Login = substring(loginname,1,' + @charMaxLenLoginName + ') ,HostName = CASE hostname When Null Then '' .'' When '' '' Then '' .'' Else substring(hostname,1,' + @charMaxLenHostName + ') END ,BlkBy = CASE isnull(convert(char(5),blocked),''0'') When ''0'' Then '' .'' Else isnull(convert(char(5),blocked),''0'') END ,DBName = substring(case when dbid = 0 then null when dbid <> 0 then db_name(dbid) end,1,' + @charMaxLenDBName + ') ,Command = substring(cmd,1,' + @charMaxLenCommand + ') ,CPUTime = substring(convert(varchar,cpu),1,' + @charMaxLenCPUTime + ') ,DiskIO = substring(convert(varchar,physical_io),1,' + @charMaxLenDiskIO + ') ,LastBatch = substring(last_batch_char,1,' + @charMaxLenLastBatch + ') ,ProgramName = substring(program_name,1,' + @charMaxLenProgramName + ') ,SPID = convert(char(5),spid) --Handy extra for right-scrolling users. ,REQUESTID = convert(char(5),request_id) from #tb1_sysprocesses --Usually DB qualification is needed in exec(). where spid >= ' + @charspidlow + ' and spid <= ' + @charspidhigh + ' -- (Seems always auto sorted.) order by spid_sortSET nocount on')LABEL_86RETURN:if (object_id('tempdb..#tb1_sysprocesses') is not null) drop table #tb1_sysprocessesreturn @retcode -- sp_who4GO
- SQLSERVER 执行sp_who2 显示的LastBatch列时间显示不正确
- myelipse 时间显示不正确
- Gentoo时间显示不正确的解决方法
- Ubuntu_系统时间显示不正确的问题
- sqlserver时间显示格式
- PHP——读取时间小时显示不正确的解决方法
- zabbix3.4graph时间显示不正确的解决办法
- jenkins在winxp下时间显示不正确
- ibm sametime client 时间显示不正确
- jsp页面时间格式显示不正确
- 将SQLSERVER中所有表的列信息显示出来
- [Linux]history 显示命令执行的时间
- Mantis 时间显示不正确（与系统时间不一致）的两种处理方法
- 在GridView显示时间列时，设置时间的格式
- 操作模式不正确使得spread列中不能显示按钮
- listbox 显示不正确的位置，scrollbar位置不正确
- Ubuntu 显示分辨率不正确的修正
- ubuntu pdf 中文显示不正确的解决方法
- 一个整形数，如何把某位设置为 0 或者 1
- 理解和使用WPF 验证机制
- 关于二进制1的个数
- linux下mysql命令
- mysql sql语句大全
- SQLSERVER 执行sp_who2 显示的LastBatch列时间显示不正确
- mapabc 报java.util.ConcurrentModificationException 问题
- chart鼠标悬停时显示数据
- android:cocos2d-x No resource found that matches the given name 'icon'
- 像素概念
- 好久没写东西了
- C++Primer笔记 第三章 标准库类型
- 使用 WebDeploymentSetup VS 2010 Web项目部署 网站部署
- 可爱的minicom
