返回顶部

收藏

枚举SQL中的Agent代理

更多
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'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
        N'ErrorLogFile',
         @errorlog_file OUTPUT,
        N'no_output'
end

-- SQL Server 2000 needs to use instance aware Registry read 
if (charindex(N'8.00', @@version, 0) > 0)
begin
    exec @rc = master.dbo.xp_instance_regread 
        N'HKEY_LOCAL_MACHINE',
        N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
        N'ErrorLogFile',
         @errorlog_file OUTPUT,
        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, 0,  @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, 0, 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

标签:sql

收藏

0人收藏

支持

0

反对

0

相关聚客文章
  1. 博主 发表 2018-07-12 00:00:00 T-SQL游标
  2. 博主 发表 2018-07-12 00:00:00 T-SQL笔记
  3. techug 发表 2018-07-11 06:22:56 译 | 如何更优雅的写出你的SQL语句
  4. darida 发表 2018-06-19 11:24:44 Vert.x-generator 2.0.0 发布,Vert.x 代码生成器
  5. darida 发表 2018-06-19 11:24:44 Vert.x-generator 2.0.0 发布,Vert.x 代码生成器
  6. yangtingkun 发表 2018-06-04 10:09:22 SQL解决刑侦推理问题
  7. kamus 发表 2018-05-24 17:15:37 All Secondary die, Primary hang? – SQL Server 2017
  8. admin 发表 2018-05-07 09:47:38 查看SQL执行计划的方法及优劣
  9. kamus 发表 2018-05-06 07:13:01 Configuring Always On Availability Groups in SQL S
  10. 博主 发表 2012-08-24 00:00:00 [转]MySQL数据库性能优化之SQL优化
  11. jqka2 发表 2018-04-13 00:56:09 PL/SQL Developer查询中文数据乱码问号?
  12. jqka2 发表 2018-04-12 09:23:00 PL/SQL Developer配置Oracle远程连接

发表评论