locked
Unable to set Reporting Services Parameters through Web Service RRS feed

  • Question

  • I'm generating a reporting services report from an ASP.NET (MVC) based application but am having problems setting the parameters for the report.

    I believe the issue has only occurred since we upgraded SQL Server from 2005 to 2008 R2 (and Reporting Services along with it).

    The original error encountered was from calling rsExec.Render:

    Procedure or function 'pCommunication_ReturnRegistrationLetterDetails' expects parameter '@guid', which was not supplied.

    Debugging the code I noticed that rsExec.SetExecutionParameters is returning the following response:

    Cannot call 'NameOfApp.SQLRSExec.ReportExecutionService.SetExecutionParameters(NameOfApp.SQLRSExec.ParameterValue[], string)' because it is a web method.

    Endpoints being used for the reporting services web services are:

    http://[server]/ReportServer/ReportService2005.asmx?wsdl

    http://[server]/ReportServer/ReportExecution2005.asmx?wsdl

    Here is the function in it's entirety:

    public static bool ProduceReportToFile(string reportname, string filename, string[,] reportparams, 
        string fileformat)
    {
        bool successful = false;
        SQLRS.ReportingService2005 rs = new SQLRS.ReportingService2005();
        SQLRSExec.ReportExecutionService rsExec = new NameOfApp.SQLRSExec.ReportExecutionService();
    
        rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
        rsExec.Credentials = System.Net.CredentialCache.DefaultCredentials;
    
        // Prepare Render arguments
        string historyID = null;
        string deviceInfo = null;
    
        // Prepare format - available options are "PDF","Word","CSV","TIFF","XML","EXCEL" 
        string format = fileformat;
    
        Byte[] results;
        string encoding = String.Empty;
        string mimeType = String.Empty;
        string extension = String.Empty;
    
        SQLRSExec.Warning[] warnings = null;
        string[] streamIDs = null;
    
        // Define variables needed for GetParameters() method
        // Get the report name
        string _reportName = reportname;
        string _historyID = null;
        bool _forRendering = false;
        SQLRS.ParameterValue[] _values = null;
        SQLRS.DataSourceCredentials[] _credentials = null;
        SQLRS.ReportParameter[] _parameters = null;
    
        // Get if any parameters needed.
        _parameters = rs.GetReportParameters(_reportName, _historyID,
                        _forRendering, _values, _credentials);
    
        // Load the selected report.
        SQLRSExec.ExecutionInfo ei =
                rsExec.LoadReport(_reportName, historyID);
    
        // Prepare report parameter.
        // Set the parameters for the report needed.
        SQLRSExec.ParameterValue[] parameters =
                new SQLRSExec.ParameterValue[1];
    
        // Place to include the parameter.
        if (_parameters.Length > 0)
        {
            for (int i = 0; i < _parameters.Length; i++)
            {
                parameters[i] = new SQLRSExec.ParameterValue();
                parameters[i].Label = reportparams[i,0];
                parameters[i].Name = reportparams[i, 0];
                parameters[i].Value = reportparams[i, 1];
            }
        }
        rsExec.SetExecutionParameters(parameters, "en-us");
        results = rsExec.Render(format, deviceInfo,
                    out extension, out encoding,
                    out mimeType, out warnings, out streamIDs);
    
        // Create a file stream and write the report to it
        using (FileStream stream = System.IO.File.OpenWrite(filename))
        {
            stream.Write(results, 0, results.Length);
        }
        successful = true;
    
        return successful;
    }
    

    Any ideas why I'm now unable to set parameters? The report generation works without issue if parameters aren't required.


    www.gavinharriss.com

    Sunday, April 1, 2012 10:29 PM

Answers

  • Looks like it may have been an issue with how reporting services passes parameters through to the stored procedure providing the data. A string guid was being passed through to the report and the stored procedure expected a varchar guid. I suspect reporting services may have been noticing the string followed the guid format pattern and so passed it through as a uniqueidentifier to the stored procedure.

    I changed the data source for the report from "stored procedure" to "text" and set the SQL as "EXEC pMyStoredOProcName @guid".

    Please note the guid being passed in as a string to the stored procedure is probably not best practice... I was simply debugging an issue with another developers code.


    www.gavinharriss.com

    • Marked as answer by gavinharriss Monday, April 2, 2012 10:55 PM
    Monday, April 2, 2012 10:55 PM