Heim >Datenbank >MySQL-Tutorial >枚举SQL中的Agent代理

枚举SQL中的Agent代理

WBOY
WBOYOriginal
2016-06-07 14:54:45957Durchsuche

枚举SQL中的Agent代理 Agent use msdbgoif exists (select * from sysobjects where name = N'sp_sqlagent_enum_errorlogs' and type ='P') drop proc dbo.sp_sqlagent_enum_errorlogsgocreate proc dbo.sp_sqlagent_enum_errorlogsasset nocount ondeclare @

枚举SQL中的Agent代理 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
Stellungnahme:
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn