0600 seconds for remote query timeout set for the SSRS DB server0KeepAlive property for the SQL Servers = 30000The 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.)
db_datareader permissions, against both prod and dev db servers, and works fine in 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
PRIMARY KEY CLUSTERED INDEXES2012-10-15
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 ---
KeepAlive property set to 30000, i.e. 30 seconds -> Possible cause of dropping the connection?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)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)??A new answer prompted me to write this answer to close the question, although this wasn't completely verified, the issue doesn't occur in higher versions of SQL Server tooling to the best of my knowledge (i.e. the same outcomes across tools will now occur).
The reason (from memory) is that SSMS vs SSRS were using different client connections, in 2008 versions I believe SSRS was using the ADO.Net connectors and SSMS was using something different (can't remember what it was now).
There was an old article I came across while researching this issue (5-6 years ago) and it essentially laid out the different client connection types between the SQL Server stack tools (I tried searching again just now for 30 minutes to find it, but given these tools are now 10 years old I was unsuccessful finding it). I also remember coming across an article by Microsoft around the time 2012 or 2014 was released saying the client types would be standardised across all the tools.
Therefore, unless you are using 2008 tools (or lower) you won't come across the issue where it works in SSMS but fails in SSRS, it will likely fail in both, or work in both.
If you're still using SSMS 2008 I believe there was an advanced query execution option you could set, try looking a connection client type option under:
Tools > Options... > Query Execution > SQL Server > GeneralTools > Options... > Query Execution > SQL Server > Advanced