??? 遇到一個(gè)有意思的Reporting Services報(bào)表的案例，在2015-01-30號(hào)的凌晨20分左右的時(shí)候，有人發(fā)現(xiàn)Reporting Services的速度非常慢，而且最后有拋出異常，當(dāng)時(shí)不知道什么情況。只是記錄了其中一張報(bào)表的名字，以及出錯(cuò)信息截圖。如下所示：

?

???? 第二天，我首先通過下面SQL查看這張報(bào)表在2015-01-30 00:00到2015-01-30 02:00這段時(shí)間的執(zhí)行記錄，如下所示，正常情況下，3~5秒報(bào)表出來，不正常情況下整個(gè)報(bào)表耗費(fèi)了接近60多秒，很奇怪的是TimeDataRetrieval的時(shí)間居然為0，大家都知道TimeDataRetrieval、TimeProcessing、TimeRendering分別表示檢索數(shù)據(jù)、處理報(bào)表以及呈現(xiàn)報(bào)表所用的毫秒數(shù)（SQL里面，我將單位轉(zhuǎn)化為秒）。但是TimeStart, TimeEnd的時(shí)間差（報(bào)表進(jìn)程的持續(xù)時(shí)段的開始時(shí)間和結(jié)束時(shí)間）居然耗費(fèi)了60多秒，讓我很是納悶，不太明白，照理來說，TimeDataRetrieval、TimeProcessing、TimeRendering三者的和應(yīng)該接近于TimeEnd減去TimeStart的差，但是這里的情況明顯不對(duì)頭，那么究竟發(fā)生了什么事情呢，這張報(bào)表不是發(fā)生了異常嗎，于是我找到了日志文件C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\LogFiles\ReportServer__01_30_2015_00_00_46.log

USE [ReportServer];GO?SELECT? C.Name???????????????????????? AS ReportName?????? ,E.ReportID???????????????????? AS ReportID?????? ,E.UserName???????????????????? AS UserName?????? ,E.Format?????????????????????? AS Format?????? ,E.Parameters?????????????????? AS?Parameters?????? ,E.TimeStart??????????????????? AS TimeStart?????? ,E.TimeEnd????????????????????? AS TimeEnd?????? ,E.TimeDataRetrieval*1.0/1000?? AS TimeDataRetrieval?????? ,E.TimeProcessing*1.0/1000????? AS TimeProcessing?????? ,E.TimeRendering*1.0/1000?????? AS TimeRendering?????? ,DATEDIFF(SECOND, TimeStart, TimeEnd)?????????????????????????????????????? AS? CostTimeFROM ReportServer.dbo.ExecutionLog E WITH(NOLOCK)INNER?JOIN ReportServer.dbo.Catalog C WITH(NOLOCK)ON E.ReportID = C.ItemIDWHERE C.Name ='JOReportZH_FTYEMI'?? AND E.TimeStart > CAST('2015-01-30 00:00'?AS DATETIME)?? AND E.TimeStart <= CAST('2015-01-30 02:00'?AS DATETIME)ORDER?BY TimeStart DESC

?

如下所示，因?yàn)楫?dāng)時(shí)Oracle數(shù)據(jù)有異常，導(dǎo)致連接數(shù)據(jù)庫(kù)時(shí)出現(xiàn)”ORA-12602: TNS: 達(dá)到連接共享限制“的錯(cuò)誤。然后分析日志發(fā)現(xiàn)，Reporting Service進(jìn)程在獲取數(shù)據(jù)出現(xiàn)異常的情況下，有個(gè)錯(cuò)誤處理機(jī)制讓它繼續(xù)嘗試訪問數(shù)據(jù)庫(kù)，進(jìn)行了多次嘗試后才最終將異常拋出，這也是為什么TimeDataRetrieval的時(shí)間居然為0。TimeStart, TimeEnd的時(shí)間差（指示報(bào)表進(jìn)程的持續(xù)時(shí)段的開始時(shí)間和結(jié)束時(shí)間）耗費(fèi)了60多秒的緣故。到此謎底終于揭開！困擾我的疑問豁然開朗！

ReportServer__01_30_2015_00_00_46.log文件里面的部分內(nèi)容如下所示：

w3wp!library!18!01/30/2015-00:03:28:: i INFO: Call to RenderFirst( '/JO/EEL/JOReportZH_FTYEMI' )
w3wp!runningjobs!d!30/01/2015-00:04:07:: i INFO: Adding: 1 running jobs to the database
w3wp!library!8!01/30/2015-00:04:09:: i INFO: Call to RenderFirst( '/JO/EEL/JOReportZH_FTYEMI' )
w3wp!processing!18!2015/1/30-00:04:31:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: 對(duì)數(shù)據(jù)集“SELECT_MATRIX_OPT”執(zhí)行查詢失敗。, ;
Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: 對(duì)數(shù)據(jù)集“SELECT_MATRIX_OPT”執(zhí)行查詢失敗。 ---> System.Data.OracleClient.OracleException: ORA-12602: TNS: 達(dá)到連接共享限制

?? at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)
?? at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals)
?? at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, ArrayList& resultParameterOrdinals)
?? at System.Data.OracleClient.OracleCommand.ExecuteReader(CommandBehavior behavior)
?? at System.Data.OracleClient.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
?? at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
?? at Microsoft.ReportingServices.DataExtensions.OracleCommandWrapperExtension.ExecuteReader(CommandBehavior behavior)
?? at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RuntimeDataSetNode.RunDataSetQuery()
?? --- End of inner exception stack trace ---
w3wp!processing!18!2015/1/30-00:04:31:: e ERROR: An exception has occurred in data source 'SELECT_MATRIX_OPT'. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: 對(duì)數(shù)據(jù)集“SELECT_MATRIX_OPT”執(zhí)行查詢失敗。 ---> System.Data.OracleClient.OracleException: ORA-12602: TNS: 達(dá)到連接共享限制

w3wp!processing!18!2015/1/30-00:04:31:: i INFO: Merge abort handler called. Aborting data sources ...
w3wp!dataextension!18!2015/1/30-00:04:31:: w WARN: CommandWrapper.Cancel not called, connection is not valid
w3wp!processing!18!2015/1/30-00:04:31:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: 處理報(bào)表時(shí)出錯(cuò)。, ;
Info: Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: 處理報(bào)表時(shí)出錯(cuò)。 ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: 對(duì)數(shù)據(jù)集“SELECT_MATRIX_OPT”執(zhí)行查詢失敗。 ---> System.Data.OracleClient.OracleException: ORA-12602: TNS: 達(dá)到連接共享限制

?? --- End of inner exception stack trace ---
w3wp!processing!18!2015/1/30-00:04:31:: w WARN: Data source '1.90': Report processing has been aborted.
w3wp!processing!18!2015/1/30-00:04:31:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: 處理報(bào)表時(shí)出錯(cuò)。, ;
Info: Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: 處理報(bào)表時(shí)出錯(cuò)。 ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: 對(duì)數(shù)據(jù)集“SELECT_MATRIX_OPT”執(zhí)行查詢失敗。 ---> System.Data.OracleClient.OracleException: ORA-12602: TNS: 達(dá)到連接共享限制

?? --- End of inner exception stack trace ---
w3wp!webserver!18!01/30/2015-00:04:31:: e ERROR: Reporting Services error Microsoft.ReportingServices.Diagnostics.Utilities.RSException: 處理報(bào)表時(shí)出錯(cuò)。 ---> Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: 處理報(bào)表時(shí)出錯(cuò)。 ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: 對(duì)數(shù)據(jù)集“SELECT_MATRIX_OPT”執(zhí)行查詢失敗。 ---> System.Data.OracleClient.OracleException: ORA-12602: TNS: 達(dá)到連接共享限制

w3wp!library!d!01/30/2015-00:04:53:: i INFO: Call to RenderFirst( '/JO/EEL/JOReportZH_FTYEMI' )
w3wp!runningjobs!1!30/01/2015-00:05:07:: i INFO: RunningJobContext.IsClientConnected; found orphaned request
w3wp!runningjobs!18!30/01/2015-00:05:07:: i INFO: Adding: 1 running jobs to the database
w3wp!runningjobs!1!30/01/2015-00:05:07:: i INFO: RunningJobContext.IsClientConnected; found orphaned request
w3wp!processing!8!2015/1/30-00:05:13:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: 對(duì)數(shù)據(jù)集“SELECT_MATRIX_OPT”執(zhí)行查詢失敗。, ;
Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: 對(duì)數(shù)據(jù)集“SELECT_MATRIX_OPT”執(zhí)行查詢失敗。 ---> System.Data.OracleClient.OracleException: ORA-12602: TNS: 達(dá)到連接共享限制

轉(zhuǎn)載于:https://www.cnblogs.com/kerrycode/p/4269021.html

總結(jié)

以上是生活随笔為你收集整理的Reporting Services 错误案例一则的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。