sql-server-2008 - 查询在 SSRS 2008 或 BIDS 2008 中不起作用，但在 SSMS 中起作用

Question

环境

数据库服务器(DWH prod & dev + SSRS prod)

Win XP SP3

SQL Server 2008 Ent

企业局域网

DWH 没有远程查询超时限制，即它们设置为 0

600为 SSRS 数据库服务器设置的远程查询超时秒数

对并发连接没有限制，即它们被设置为 0

TCP/IP KeepAlive SQL Server 的属性 = 30000

开发箱

Win XP SP3

2008年投标

共享数据源(由 .rdl 使用)

使用服务器的机器名，没有别名

权限一切正常

问题

BIDS 在查看报告的“预览”选项卡时返回的错误在大约 1 分钟后出现(SSMS 中的查询需要约 1 分 10 秒 - 编辑:2012-10-16 有现在通过将报告部署到报告管理器并尝试运行报告来复制 SSRS 本身的错误:下面添加了异常堆栈跟踪):

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.)

该查询已经过测试，最终用户帐户具有 db_datareader权限，针对 prod 和 dev db 服务器，并且在 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_2
    ,Group_3
    .
    .
    .
    ,Group_n

注释(不确定这些是否有帮助):

递归 CTE 仅执行 24 次递归。

所有的 key 都是PRIMARY KEY CLUSTERED INDEXES

问题)

从哪里开始诊断问题实际上是什么？

发现问题后如何解决？

到目前为止我所做的...

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, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, 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 DB 服务器和 SSRS DB 服务器级别的 TCP/IP 设置。两者都有 KeepAlive属性设置为 30000 ，即 30 秒 -> 断开连接的可能原因？

找到了类似的问题解决方案(基于堆栈跟踪)here用于针对 Visual Studio 2005 的查询。我取消选择 Tools > Options > Database Tools > Query and View Designers > Cancel long running query (它被设置为 30 秒)。没有变化(我相信这个客户端设置也不会影响部署到 SSRS 服务器的报告，但我想我会试一试)

找到 this声明 OBDC connection attempt when server is not ready to process a new local connection, possibly due to overload -> 当 SSRS 向 DWH 服务器提交查询时，递归 CTE 是否会导致这样的过载(即使我没有进行 ODBC 连接)？？

找到 this说明 SSRS 不喜欢虚拟表(没有提到原因，也没有提供引用)-> 可能需要重写查询？

回复评论:网络诊断

Answer 1

首先，你必须找到问题所在。

当您通过运行 Profiler 打开报告时，您能检查一下发生了什么吗？
使用事件审计登录、审计注销、异常、SQL:Stmt:Starting/Completed 开始跟踪。
在您尝试打开报告后，检查结果并告诉我们。