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`
* `600` seconds for remote query timeout set for the SSRS DB server
* No restrictions on concurrent connections, i.e. they are set to `0`
* TCP/IP `KeepAlive` property 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_datareader` permissions, 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)
================================================

---

```sql
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)
===========

---

1. Where to look to start diagnosing what the issue actually is?
2. 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 `KeepAlive` property set to `30000`, i.e. 30 seconds -> Possible cause of dropping the connection?
* Similar issue resolution (based on stack trace) found [here](http://support.microsoft.com/kb/555938) 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](http://blogs.msdn.com/b/sql_protocols/archive/2005/09/28/474698.aspx) 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](http://www.sqlservercentral.com/Forums/Topic652500-147-1.aspx#bm653337) 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