求教SQLServer2008R2 报表>>备份和还原事件出现异常！

2013-06-19

求教SQLServer2008R2 报表备份和还原事件出现错误！！请教各位大侠SQLServer2008R2数据库，报表备份和还

求教SQLServer2008R2 报表>>备份和还原事件出现错误！！
请教各位大侠SQLServer2008R2数据库，报表>>备份和还原事件：备份操作错误出现下面异常：
无法检索报表此部分的数据。出现以下错误:消息567,级别16状态2
请各位大侠给予指点，小弟不胜感激！报表 SQL?Server 备份数据库
[解决办法]
这个你可以用Profiler Trace看一下是哪里出问题了，因为报表是从数据库抓取数据的。找到报表运行的语句自己执行以下看看。
[解决办法]
看一下服务器跟踪即profilter trace产生的log.trc文件是否存在，它在后台执行如下SQL语句，可以在MSMT中执行下脚本，看能否产生结果

exec sp_executesql @stmt=N'begin try
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''

[解决办法]
dbcc checkdb()检查一下数据库是否有错误..

热点排行

Bad Request.

SQL Server

求教SQLServer2008R2 报表>>备份和还原事件出现异常！