枚举SQL中的Agent代理
程序员文章站
2024-01-12 13:36:40
...
枚举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代理 Agentuse 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中的Agent代理
-
SQL Server数据库按百分比查询出表中的记录数
-
asp下删除Access数词库中的空记录的sql语句
-
存储过程的输出参数为游标,PL/SQL中如何调用 Java代码如何调用
-
织梦{ dede:listsql sql } 标签中如何给sql 语句中的条件传入栏目ID的值,跪求大神指点.
-
在linux下写的mysql无法插入,sql语句在复制中变'脏'了.
-
详解ES6中的代理模式——Proxy
-
SQL读取指定数据表中的所有字段说明
-
这个坑,我裂开了!!!mybatis的mapper.xml文件中sql语句前后有注释掉的sql都会报错,那注释这个功能在这还有毛用???/捂脸/捂脸...
-
SQL Server中删除重复记录的SQL语句