枚举SQL中的Agent代理

以下代码由PHP站长网 52php.cn收集自互联网

现在PHP站长网小编把它分享给大家，仅供参考

use msdb
go

if exists (select * from sysobjects where name = N'sp_sqlagent_enum_errorlogs' and type ='P')
    drop proc dbo.sp_sqlagent_enum_errorlogs
go

create proc dbo.sp_sqlagent_enum_errorlogs
as

set nocount on

declare @rc int
declare @version int
declare @pos int
declare @errorlog_file nvarchar(255)
declare @filename nvarchar(255)
declare @filename_no_ext nvarchar(255)
declare @dirname nvarchar(255)
declare @buf nvarchar(255)

-- SQL Server 7.0
if (charindex(N'7.00',@@version,0) > 0)
begin
    exec @rc = master.dbo.xp_regread 
        N'HKEY_LOCAL_MACHINE',N'SOFTWAREMicrosoftMSSQLServerSQLServerAgent',N'ErrorLogFile',@errorlog_file OUTPUT,N'no_output'
end

-- SQL Server 2000 needs to use instance aware Registry read 
if (charindex(N'8.00',0) > 0)
begin
    exec @rc = master.dbo.xp_instance_regread 
        N'HKEY_LOCAL_MACHINE',N'no_output'
end

-- reverse the string to find the last slash ''
select @buf = reverse(@errorlog_file)

-- determine posiktion of last slash,now first slash in reversed string
select @pos = len(@buf) - charindex(char(92),@buf,0) + 1

-- extract the directory only part,part before the last slash
select @dirname = substring(@errorlog_file,@pos)

-- extract the filename,part after the last slash
select @filename = substring(@errorlog_file,@pos + 1,len(@errorlog_file) - @pos)

-- extract the filename with extension,part after dot in @filename
select @filename_no_ext  = substring(@filename,charindex(N'.',@filename,0))

-- since xp_dirtree does not allow any filters,create temp table to store results
create table #t
(
[subdirectory] nvarchar(255) not null,[depth] int not null,[file] int not null
)

-- add all file names to the temp table
insert into #t exec master.dbo.xp_dirtree @dirname,1,1

-- retrieve only files that match @filename_no_ext%
select [name] = subdirectory from #t where subdirectory like @filename_no_ext + '%' order by [name]

drop table #t
go

-- sample usage
-- exec msdb.dbo.sp_sqlagent_enum_errorlogs
-- go

以上内容由PHP站长网【52php.cn】收集整理供大家参考研究

如果以上内容对您有帮助，欢迎收藏、点赞、推荐、分享。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!