none
Receiving a datasource error when running report that uses expression based connection string RRS feed

  • Question

  • Hi,

       When I attempt to run a report that i have created using an expression based connection string. I get the following error:

              An error occurred during local report processing.
                 An error has occured suring report processing
                     InternalStagingSQLServerSource


    InternalStagingSQLServerSource refers to a datasource i created for my project. Initially I was using a shared datasource for my testing and everything was OK. Now I have changed it to become an expression based connection string as we have many databases that our reports can run against so we want to set the database at runtime. My connection string expression is as follows:

                     

    =

    "Data Source=ENVIS-STAGE-SRV\MSSQLSERVER2008;Initial Catalog=" & Parameters!ClientID.Value
    in the credentials tab, i set the authentication details so they are stored safely in the RDL.

    I created a hidden parameter and gave it a default value, which is the name of one of our databases.
    If i change the above expression to include an constant expression like this

    ="Data Source=ENVIS-STAGE-SRV\MSSQLSERVER2008;Initial Catalog=" & "Database1"
    This works, so I know it has something to do with the parameter.

    The extended error information i found in our log files is below if it helps:

    processing!ReportServer_0-3!12a8!07/23/2009-13:44:18:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: InternalStagingSQLServerSource, ;
     Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: InternalStagingSQLServerSource
    processing!ReportServer_0-3!12a8!07/23/2009-13:44:18:: e ERROR: Data source 'InternalStagingSQLServerSource': An error has occurred. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: InternalStagingSQLServerSource
       at Microsoft.ReportingServices.ReportIntermediateFormat.DataSource.EvaluateConnectStringExpression(OnDemandProcessingContext processingContext)
       at Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSource.OpenDataExtensionConnection(DataSource dataSourceObj, OnDemandProcessingContext pc, CreateDataExtensionInstance createDataExtensionInstance)
       at Microsoft.ReportingServices.ReportProcessing.ReportProcessingContext.CreateAndSetupDataExtensionInstance(DataSource dataSource, DataSet dataSet, OnDemandProcessingContext pc)
       at Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSource.OpenConnection(DataSource dataSourceObj, DataSet dataSetObj, OnDemandProcessingContext pc, RunningJobContext jobContext)
       at Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSource.Process()
    processing!ReportServer_0-3!12a8!07/23/2009-13:44:18:: e ERROR: An exception has occurred in data source 'InternalStagingSQLServerSource'. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: InternalStagingSQLServerSource
       at Microsoft.ReportingServices.ReportIntermediateFormat.DataSource.EvaluateConnectStringExpression(OnDemandProcessingContext processingContext)
       at Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSource.OpenDataExtensionConnection(DataSource dataSourceObj, OnDemandProcessingContext pc, CreateDataExtensionInstance createDataExtensionInstance)
       at Microsoft.ReportingServices.ReportProcessing.ReportProcessingContext.CreateAndSetupDataExtensionInstance(DataSource dataSource, DataSet dataSet, OnDemandProcessingContext pc)
       at Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSource.OpenConnection(DataSource dataSourceObj, DataSet dataSetObj, OnDemandProcessingContext pc, RunningJobContext jobContext)
       at Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSource.Process()
       at Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSource.ProcessConcurrent(Object threadSet)
    processing!ReportServer_0-3!12a8!07/23/2009-13:44:18:: i INFO: DataPrefetch abort handler called for Report with ID=. Aborting data sources ...
    processing!ReportServer_0-3!12a8!07/23/2009-13:44:18:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing., ;
     Info: Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: InternalStagingSQLServerSource
       at Microsoft.ReportingServices.ReportIntermediateFormat.DataSource.EvaluateConnectStringExpression(OnDemandProcessingContext processingContext)
       at Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSource.OpenDataExtensionConnection(DataSource dataSourceObj, OnDemandProcessingContext pc, CreateDataExtensionInstance createDataExtensionInstance)
       at Microsoft.ReportingServices.ReportProcessing.ReportProcessingContext.CreateAndSetupDataExtensionInstance(DataSource dataSource, DataSet dataSet, OnDemandProcessingContext pc)
       at Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSource.OpenConnection(DataSource dataSourceObj, DataSet dataSetObj, OnDemandProcessingContext pc, RunningJobContext jobContext)
       at Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSource.Process()
       at Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSource.ProcessConcurrent(Object threadSet)
       --- End of inner exception stack trace ---
    chunks!ReportServer_0-3!12a8!07/23/2009-13:44:18:: w WARN: Rolling back shared chunk transaction for snapshot 'd7b72497-bd73-4c82-b52e-171c2a629069', Permanent=True.


    Hope someone can help with this
    Cheers
    Paul.

    Thursday, July 23, 2009 1:09 PM

Answers

  • Hi,

     I found the problem, and just thought i'd share with you all. I actually opened thye RDL file as text and put the <ReportParameters> section before the <Datasources> section and also put the Parameter that I was using in the connectionstring first in the list of my parameters ahead of other report parameters, and that fixed the problem!!!!!!!

    Paul.
    Thursday, July 23, 2009 1:20 PM
  • Yes, when using expression-based connection strings that reference report parameters, make sure that these parameters are at the beginning of the list of report parameters and don't depend on other report parameter values that may not be evaluated yet (e.g. if there are hierarchical parameters and the parameter used in the connection string depends on data from an earlier parameter with dataset-dependent valid values / default values of the data source it is about to connect to - that won't work).

    Robert Bruckner   http://blogs.msdn.com/robertbruckner
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, July 23, 2009 4:43 PM

All replies

  • Hi,

     I found the problem, and just thought i'd share with you all. I actually opened thye RDL file as text and put the <ReportParameters> section before the <Datasources> section and also put the Parameter that I was using in the connectionstring first in the list of my parameters ahead of other report parameters, and that fixed the problem!!!!!!!

    Paul.
    Thursday, July 23, 2009 1:20 PM
  • Yes, when using expression-based connection strings that reference report parameters, make sure that these parameters are at the beginning of the list of report parameters and don't depend on other report parameter values that may not be evaluated yet (e.g. if there are hierarchical parameters and the parameter used in the connection string depends on data from an earlier parameter with dataset-dependent valid values / default values of the data source it is about to connect to - that won't work).

    Robert Bruckner   http://blogs.msdn.com/robertbruckner
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, July 23, 2009 4:43 PM