select @status=status, @curr_tracefilename=path from sys.traces where is_default = 1 ; set @curr_tracefilename = reverse(@curr_tracefilename) select @indx = patindex(''%\%'', @curr_tracefilename) set @curr_tracefilename = reverse(@curr_tracefilename) set @base_tracefilename = left( @curr_tracefilename,len(@curr_tracefilename) - @indx) + ''\log.trc'';
insert into @temp_trace select Error , StartTime , HostName , ApplicationName , LoginName , Severity , DatabaseName , TextData from ::fn_trace_gettable( @base_tracefilename, default ) where substring(TextData, 20, 15) like ''%Backup%'' and TextData like ''%Error%'' and ServerName = @@servername ;
select (row_number() over(order by StartTime desc))%2 as l1 , right( right( TextData, len(TextData) - patindex(''%BACKUP%'',TextData) ) , len(right( TextData, len(TextData) - patindex(''%BACKUP%'',TextData) )) - patindex(''%BACKUP%'',right( TextData, len(TextData) - patindex(''%BACKUP%'',TextData) )) - 10 ) as ErrorMessage , Error , Severity , StartTime , HostName , ApplicationName , LoginName , DatabaseName from @temp_trace where DatabaseName = ''" & Parameters!DatabaseName.Value & "'' order by StartTime desc end else begin select top 0 1 as ErrorMessage, 1 as Error, 1 as Severity, 1 as StartTime,1 as HostName, 1 as ApplicationName,1 as LoginName, 1 as DatabaseName, 1 as l1 end end try begin catch select ERROR_MESSAGE() as ErrorMessage , ERROR_NUMBER() as Error , ERROR_SEVERITY() as Severity , ERROR_STATE() as StartTime , 1 as HostName, 1 as ApplicationName,1 as LoginName, 1 as DatabaseName , -100 as l1 end catch',@params=N''
use msdb;
declare @enable int;
select @enable = convert(int, value_in_use) from sys.configurations where name = ''default trace enabled''
if @enable = 1
begin
declare @curr_tracefilename varchar(500);
declare @base_tracefilename varchar(500);
declare @status int;
declare @indx int;
declare @temp_trace table (
Error int
, StartTime datetime
, HostName sysname collate database_default null
, ApplicationName sysname collate database_default null
, LoginName sysname collate database_default null
, Severity int
, DatabaseName sysname collate database_default null
, TextData nvarchar(max) collate database_default
);
select @status=status, @curr_tracefilename=path from sys.traces where is_default = 1 ;
set @curr_tracefilename = reverse(@curr_tracefilename)
select @indx = patindex(''%\%'', @curr_tracefilename)
set @curr_tracefilename = reverse(@curr_tracefilename)
set @base_tracefilename = left( @curr_tracefilename,len(@curr_tracefilename) - @indx) + ''\log.trc'';
insert into @temp_trace
select Error
, StartTime
, HostName
, ApplicationName
, LoginName
, Severity
, DatabaseName
, TextData
from ::fn_trace_gettable( @base_tracefilename, default )
where substring(TextData, 20, 15) like ''%Backup%'' and TextData like ''%Error%'' and ServerName = @@servername ;
select (row_number() over(order by StartTime desc))%2 as l1
, right( right( TextData, len(TextData) - patindex(''%BACKUP%'',TextData) ) , len(right( TextData, len(TextData) - patindex(''%BACKUP%'',TextData) )) - patindex(''%BACKUP%'',right( TextData, len(TextData) - patindex(''%BACKUP%'',TextData) )) - 10 ) as ErrorMessage
, Error
, Severity
, StartTime
, HostName
, ApplicationName
, LoginName
, DatabaseName
from @temp_trace
where DatabaseName = ''" & Parameters!DatabaseName.Value & "''
order by StartTime desc
end else
begin
select top 0 1 as ErrorMessage, 1 as Error, 1 as Severity, 1 as StartTime,1 as HostName, 1 as ApplicationName,1 as LoginName, 1 as DatabaseName, 1 as l1
end
end try
begin catch
select ERROR_MESSAGE() as ErrorMessage
, ERROR_NUMBER() as Error
, ERROR_SEVERITY() as Severity
, ERROR_STATE() as StartTime
, 1 as HostName, 1 as ApplicationName,1 as LoginName, 1 as DatabaseName
, -100 as l1
end catch',@params=N''