数据库服务器(DWH prod& dev SSRS prod)
>赢得XP SP3
> sql Server 2008 Ent
>企业局域网
> DWH没有远程查询超时限制,即它们设置为0
为SSRS DB服务器设置远程查询超时> 600秒
>对并发连接没有限制,即它们设置为0
> sql Server的TCP / IP KeepAlive属性= 30000
开发盒
>赢得XP SP3
> BIDS 2008
共享数据源(由.rdl使用)
>使用服务器的机器名称,没有别名
>权限都可以
问题
BIDS在查看报告的“预览”选项卡时返回的错误在大约1分钟后出现(SSMS中的查询需要大约1分钟10秒 – 编辑:2012-10-16现在已经在SSRS中复制了错误通过将报告部署到Report Manager并尝试运行报告:下面添加了异常堆栈跟踪):
An error occurred during local report processing.
An error has occurred during report processing.
Query execution Failed for dataset ‘MainData’.
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider,error: 0 – The specified network name is no longer available.)
>已针对prod和dev db服务器测试了具有db_datareader权限的最终用户帐户的查询,并且在SSMS中正常运行.
>此帐户用于最终用户在运行时执行.rdl.
> SSMS用于验证查询在语法上是否正确,并返回完整且完整的结果集,没有错误且没有警告.
查询结构(为简洁起见,为此而列出)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SET NOCOUNT ON SET ANSI_WARNINGS OFF /********************************************************** CTE to be used to clean strings ***********************************************************/ ;WITH CTE_1 AS ( SELECT SomeData ),CTE_2 AS ( SELECT SomeData ),Recursive_CTE AS ( SELECT SomeData ) -- String cleaning performed here,CTE_3 AS ( SELECT SomeData ) /************************** Data set returned here ***************************/ SELECT Column_1,Column_2,Column_3 . . .,Column_n FROM Fact INNER JOIN Dimension_1 AS 1 ON Fact.Key_1 = 1.Key_1 INNER JOIN Dimension_2 AS 2 ON Fact.Key_2 = 2.Key_2 INNER JOIN Dimension_3 AS 3 ON Fact.Key_4 = 3.Key_3 INNER JOIN Dimension_5 AS 4 ON Fact.Key_4 = 4.Key_4 LEFT JOIN CTE_3 AS clean ON 4.Key_4 = clean.Key_4 -- Clean names returned in query and used in GROUP BY clause WHERE Condition_1 = Test_1 AND Condition_2 = Test_2 AND Condition_3 = Test_3 . . . AND Condition_n = Test_n GROUP BY Group_1,Group_2,Group_3 . . .,Group_n ORDER BY Group_1,Group_n
备注(不确定是否有任何帮助):
>递归CTE只执行24次递归.
>所有键都是PRIMARY KEY CLUSTERED INDEXES
问题(S)
>从哪里开始诊断问题究竟是什么?
>一旦识别出问题,如何解决问题?
到目前为止我做了什么……
2012年10月15日
>谷歌机器上的猎人无济于事.
>与已经看到这种情况的其他团队成员进行讨论,他们认为这可能与性能有关,即查询运行时间太长,或者他们认为SSRS可能会执行更严格的sql代码验证/语法检查,或者它使用的连接器.
2012年10月16日
ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution Failed for dataset 'DataSet1'.,; Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution Failed for dataset 'DataSet1'. ---> System.Data.sqlClient.sqlException: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider,error: 0 - The specified network name is no longer available.) at System.Data.sqlClient.sqlConnection.OnError(sqlException exception,Boolean breakConnection) at System.Data.sqlClient.sqlInternalConnection.OnError(sqlException exception,Boolean breakConnection) at System.Data.sqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.sqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj,UInt32 error) at System.Data.sqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult,TdsParserStateObject stateObj) at System.Data.sqlClient.TdsParserStateObject.ReadNetworkPacket() at System.Data.sqlClient.TdsParserStateObject.ReadBuffer() at System.Data.sqlClient.TdsParserStateObject.ReadByte() at System.Data.sqlClient.TdsParser.Run(RunBehavior runBehavior,sqlCommand cmdHandler,sqlDataReader dataStream,BulkCopySimpleResultSet bulkCopyHandler,TdsParserStateObject stateObj) at System.Data.sqlClient.sqlDataReader.ConsumeMetaData() at System.Data.sqlClient.sqlDataReader.get_MetaData() at System.Data.sqlClient.sqlCommand.FinishExecuteReader(sqlDataReader ds,RunBehavior runBehavior,String resetOptionsString) at System.Data.sqlClient.sqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior,Boolean returnStream,Boolean async) at System.Data.sqlClient.sqlCommand.RunExecuteReader(CommandBehavior cmdBehavior,String method,DbAsyncResult result) at System.Data.sqlClient.sqlCommand.RunExecuteReader(CommandBehavior cmdBehavior,String method) at System.Data.sqlClient.sqlCommand.ExecuteReader(CommandBehavior behavior,String method) at System.Data.sqlClient.sqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.ReportingServices.DataExtensions.sqlCommandWrapperExtension.ExecuteReader(CommandBehavior behavior) at Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSet.RunDataSetQuery() --- End of inner exception stack trace ---
>上述异常在同一数据集的日志中多次出现 – >可能的原因可能是CTE中的递归?
>我已经检查了DWH数据库服务器和SSRS数据库服务器级别的TCP / IP设置.两者都将KeepAlive属性设置为30000,即30秒 – >丢失连接的可能原因?
>类似的问题解决方案(基于堆栈跟踪)发现here对Visual Studio 2005的查询.我取消选择工具>选项>数据库工具>查询和查看设计师>取消长时间运行的查询(设置为30秒).没有变化(这个客户端设置也不会影响报告一旦部署到SSRS服务器我相信,但我想我会试一试)
>发现this,当服务器未准备好处理新的本地连接时,表明OBDC连接尝试,可能是由于过载 – >当SSRS将查询提交给DWH服务器时,递归CTE会导致这样的重载(即使我没有进行ODBC连接)吗?
>发现this表示SSRS不喜欢虚拟表(没有提到原因,也没有提供参考) – >可能需要重写查询?
>回复评论re:网络诊断