Environment
DB Server (DWH prod & dev + SSRS prod)
- Win XP SP3
- SQL Server 2008 Ent
- Corporate LAN
- No remote query timeout restrictions for DWH, i.e. they are set to
0 600seconds for remote query timeout set for the SSRS DB server- No restrictions on concurrent connections, i.e. they are set to
0 - TCP/IP
KeepAliveproperty for the SQL Servers =30000
Dev Box
- Win XP SP3
- BIDS 2008
Shared Data Source (used by the .rdl)
- Uses the machine name for the server, no aliases
- Permissions are all ok
Problem
The error returned by BIDS when looking at the 'Preview' tab of the report comes up with this after about 1 minute (the query takes ~1 min 10 seconds in SSMS - Edit: 2012-10-16 Have now replicated error in SSRS itself by deploying report to Report Manager and attempting to run the report: exception stack trace added below):
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.)
- The query has been tested, with the end-user account that has
db_datareaderpermissions, against both prod and dev db servers, and works fine in SSMS. - This account is used for execution of the .rdl at runtime by end-users.
- SSMS was used to verify the queries are syntactically correct, and returned a full and complete result set with no errors and no warnings.
The Query Structure (listed as such for brevity)
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
Notes (not sure if any of this will be helpful or not):
- The recursive CTE only carries out 24 recursions.
- All keys are
PRIMARY KEY CLUSTERED INDEXES
Question(s)
- Where to look to start diagnosing what the issue actually is?
- How to fix the issue once it is identified?
What I have done so far...
2012-10-15
- Hunted on the Google machine to no avail.
- Spoke with other team members who have seen this occur, and they think it may be performance related, i.e. query is taking too long to run, or they think there may be stricter SQL code validation/syntax checking carried out by SSRS, or the connectors it uses.
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 ---
- The above exception occurs multiple times in the log for the same dataset -> Possible cause could be the recursion in the CTE??
- I've checked TCP/IP settings at the DWH DB Server and SSRS DB Server levels. Both have the
KeepAliveproperty set to30000, i.e. 30 seconds -> Possible cause of dropping the connection? - Similar issue resolution (based on stack trace) found here for queries against Visual Studio 2005. I de-selected
Tools > Options > Database Tools > Query and View Designers > Cancel long running query(it was set to 30 seconds). No change (nor would this client setting affect the report once deployed to the SSRS server I believe, but thought I'd give it a shot) - Found this that states
OBDC connection attempt when server is not ready to process a new local connection, possibly due to overload-> Could recursive CTE's cause an overload like this when SSRS submits the query to the DWH server (even though I'm not making an ODBC conenction)?? - Found this that states SSRS doesn't like virtual tables (doesn't mention why, and provides no reference) -> Possible re-write of query required?
- Responded to comments re: network diagnostics
