- use msdb
- go
- if exists (select * from sysobjects where name = N'sp_sqlagent_read_errorlog' and type ='P')
- drop proc dbo.sp_sqlagent_read_errorlog
- go
- create proc dbo.sp_sqlagent_read_errorlog @fileversion int = null
- 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))
- -- change errorlog file to version X
- if (@fileversion is not null)
- select @errorlog_file = @dirname +N'\\' + @filename_no_ext + N'.' + convert(nchar, @fileversion)
- -- read file
- exec master.dbo.xp_readerrorlog 1, @errorlog_file
- go
- -- sample usage
- -- read currently active log file, SQLAGENT.OUT
- -- exec msdb.dbo.sp_sqlagent_read_errorlog
- -- read version 1 of log, SQLAGENT.1
- -- exec msdb.dbo.sp_sqlagent_read_errorlog 1
- --该片段来自于http://www.codesnippet.cn/detail/30112012750.html
来源: http://www.codesnippet.cn/detail/30112012750.html