Calling SQl2008 Reporting Server WebService Programmatically


Note: I have moved to blog at my own domain, so kindly visit this post over there for recent updates or comments. http://www.imranbalouch.com/blog/index.php/2010/04/calling-sql2008-reporting-server-webservice-programmaticall/

In our web application we have a lot of reports and many of them take hell lot of time, so we decided to make a page from where user can select report and provide parameters and save the request, later on our windows service can get report from sql reporting server’s web service and email the report in desired format to the user.

While doing a test, I created a windows service and used following code to get the report:

ReportExecutionService.ReportExecutionServiceSoapClient rsExec = new ReportingServiceTesting.ReportExecutionService.ReportExecutionServiceSoapClient();

      System.Net.NetworkCredential clientCredentials = new System.Net.NetworkCredential("me", "me", "me");
      rsExec.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation;
      rsExec.ClientCredentials.Windows.ClientCredential = clientCredentials;

      string historyID = "";
      string reportPath = "Reports/MyOrders";

      ReportExecutionService.ExecutionInfo execInfo = new ReportExecutionService.ExecutionInfo();
      ReportExecutionService.TrustedUserHeader trusteduserHeader = new ReportingServiceTesting.ReportExecutionService.TrustedUserHeader();

      ReportExecutionService.ExecutionHeader execHeader = new ReportExecutionService.ExecutionHeader();
      ReportExecutionService.ServerInfoHeader serviceInfo = new ReportingServiceTesting.ReportExecutionService.ServerInfoHeader();
      rsExec.LoadReport(trusteduserHeader, reportPath, historyID, out serviceInfo, out execInfo);

      ReportExecutionService.ParameterValue[] executionParams;
      executionParams = new ReportExecutionService.ParameterValue[5];
      executionParams[0] = new ReportExecutionService.ParameterValue();
      executionParams[0].Name = "@pCompanyId";
      executionParams[0].Value = "1";
      executionParams[1] = new ReportExecutionService.ParameterValue();
      executionParams[1].Name = "@pUserId";
      executionParams[1].Value = "1";
      executionParams[2] = new ReportExecutionService.ParameterValue();
      executionParams[2].Name = "@pLanguageId";
      executionParams[2].Value = "1";
      executionParams[3] = new ReportExecutionService.ParameterValue();
      executionParams[3].Name = "@pOrganizationId";
      executionParams[3].Value = "1";
      executionParams[4] = new ReportExecutionService.ParameterValue();
      executionParams[4].Name = "@pThemeId";
      executionParams[4].Value = "1";

      rsExec.SetExecutionParameters(execHeader,trusteduserHeader, executionParams, "en-us",out execInfo);

      //string historyID = null;
      string deviceInfo = null;
      string extension;
      string encoding;
      string mimeType;
      ReportExecutionService.Warning[] warnings = new ReportingServiceTesting.ReportExecutionService.Warning[1];
      warnings[0] = new ReportingServiceTesting.ReportExecutionService.Warning();
      string[] streamIDs = null;
      string format = "EXCEL";
      Byte[] result;
      rsExec.Render(execHeader,trusteduserHeader, format,
        deviceInfo,out result, out extension,
        out mimeType, out encoding,
        out warnings, out streamIDs);
      String fileName = "me.xls";
      FileStream stream = File.OpenWrite(fileName);
      stream.Write(result, 0, result.Length);
      stream.Close();

and our configuration file has settings like:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <system.serviceModel>
    <bindings>
      <basicHttpBinding>
        <binding name="ReportExecutionServiceSoap" closeTimeout="00:01:00"
          openTimeout="00:01:00" receiveTimeout="00:10:00" sendTimeout="00:01:00"
          allowCookies="false" bypassProxyOnLocal="false" hostNameComparisonMode="StrongWildcard"
          maxBufferSize="65536" maxBufferPoolSize="524288" maxReceivedMessageSize="65536"
          messageEncoding="Text" textEncoding="utf-8" transferMode="Buffered"
          useDefaultWebProxy="true">
          <readerQuotas maxDepth="32" maxStringContentLength="8192" maxArrayLength="16384"
            maxBytesPerRead="4096" maxNameTableCharCount="16384" />
          <security mode="TransportCredentialOnly">
            <transport clientCredentialType="Ntlm" proxyCredentialType="None"
              realm="" />
            <message clientCredentialType="UserName" algorithmSuite="Default" />
          </security>
        </binding>
      </basicHttpBinding>
      <customBinding>
        <binding name="ReportingService2005Soap12">
          <textMessageEncoding maxReadPoolSize="64" maxWritePoolSize="16"
            messageVersion="Soap12" writeEncoding="utf-8">
            <readerQuotas maxDepth="32" maxStringContentLength="8192" maxArrayLength="16384"
              maxBytesPerRead="4096" maxNameTableCharCount="16384" />
          </textMessageEncoding>
          <httpTransport manualAddressing="false" maxBufferPoolSize="524288"
            maxReceivedMessageSize="65536" allowCookies="false" authenticationScheme="Anonymous"
            bypassProxyOnLocal="false" hostNameComparisonMode="StrongWildcard"
            keepAliveEnabled="true" maxBufferSize="65536" proxyAuthenticationScheme="Anonymous"
            realm="" transferMode="Buffered" unsafeConnectionNtlmAuthentication="false"
            useDefaultWebProxy="true" />
        </binding>
      </customBinding>
    </bindings>
    <client>
      <endpoint address="http://myServer/ReportServerSQL2008/ReportExecution2005.asmx"
        binding="basicHttpBinding" bindingConfiguration="ReportExecutionServiceSoap"
        contract="ReportExecutionService.ReportExecutionServiceSoap"
        name="ReportExecutionServiceSoap" />
    </client>
  </system.serviceModel>
</configuration>

When I run my windows service I came across folloing error:

System.ServiceModel.FaultException: System.Web.Services.Protocols.SoapException: An internal error occurred on the report server. See the error log for more details. —> Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details. —> System.ArgumentNullException: Value cannot be null.
Parameter name: value
at Microsoft.ReportingServices.WebServer.ReportExecutionService.Initialize(TrustedUserHeader userHeader, String item)
at Microsoft.ReportingServices.WebServer.ReportExecutionService.LoadReport(String Report, String HistoryID, ExecutionInfo& executionInfo)

Server stack trace:
at System.ServiceModel.Channels.ServiceChannel.HandleReply(ProxyOperationRuntime operation, ProxyRpc& rpc)
at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout)

I checked the log reports but couldn’t find any clue, I did googling but couldn’t find any solid solution and after getting tips and tricks from different posts I found following things I needed to do in:

historyId = null;

If you are getting some error regarding SnapshotId, be sure that you have passed historyId as null.

After load report method:
execHeader.ExecutionID = execInfo.ExecutionID;

assign executionId of your current execution to header so that it can be used in other steps.

and while rendering the report through Render method pass TrustedUserHeader parameter as null.

This was the most hectic thing to find because System was throwing null value exception and it got fixed when I passed TrustedUserHeader as null. 😀

After doing these tweaks the windows service got the report from the web service.

ReportExecutionService.ReportExecutionServiceSoapClient rsExec = new ReportingServiceTesting.ReportExecutionService.ReportExecutionServiceSoapClient();

      System.Net.NetworkCredential clientCredentials = new System.Net.NetworkCredential("me", "me", "me");
      rsExec.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation;
      rsExec.ClientCredentials.Windows.ClientCredential = clientCredentials;

      string historyID = "";
      string reportPath = "/V3/Operations/OrderssByCustomers";

      ReportExecutionService.ExecutionInfo execInfo = new ReportExecutionService.ExecutionInfo();
      ReportExecutionService.TrustedUserHeader trusteduserHeader = new ReportingServiceTesting.ReportExecutionService.TrustedUserHeader();

      ReportExecutionService.ExecutionHeader execHeader = new ReportExecutionService.ExecutionHeader();
      ReportExecutionService.ServerInfoHeader serviceInfo = new ReportingServiceTesting.ReportExecutionService.ServerInfoHeader();
      rsExec.LoadReport(trusteduserHeader, reportPath, historyID, out serviceInfo, out execInfo);

      ReportExecutionService.ParameterValue[] executionParams;
      executionParams = new ReportExecutionService.ParameterValue[5];
      executionParams[0] = new ReportExecutionService.ParameterValue();
      executionParams[0].Name = "@pCompanyId";
      executionParams[0].Value = "1";
      executionParams[1] = new ReportExecutionService.ParameterValue();
      executionParams[1].Name = "@pUserId";
      executionParams[1].Value = "1";
      executionParams[2] = new ReportExecutionService.ParameterValue();
      executionParams[2].Name = "@pLanguageId";
      executionParams[2].Value = "1";
      executionParams[3] = new ReportExecutionService.ParameterValue();
      executionParams[3].Name = "@pOrganizationId";
      executionParams[3].Value = "1";
      executionParams[4] = new ReportExecutionService.ParameterValue();
      executionParams[4].Name = "@pThemeId";
      executionParams[4].Value = "1";

      rsExec.SetExecutionParameters(execHeader,trusteduserHeader, executionParams, "en-us",out execInfo);

      //string historyID = null;
      string deviceInfo = null;
      string extension;
      string encoding;
      string mimeType;
      ReportExecutionService.Warning[] warnings = new ReportingServiceTesting.ReportExecutionService.Warning[1];
      warnings[0] = new ReportingServiceTesting.ReportExecutionService.Warning();
      string[] streamIDs = null;
      string format = "EXCEL";
      Byte[] result;
      rsExec.Render(execHeader,trusteduserHeader, format,
        deviceInfo,out result, out extension,
        out mimeType, out encoding,
        out warnings, out streamIDs);
      String fileName = "me.xls";
      FileStream stream = File.OpenWrite(fileName);
      stream.Write(result, 0, result.Length);
      stream.Close();
Advertisements

15 Responses to Calling SQl2008 Reporting Server WebService Programmatically

  1. Lash says:

    Thanks a lot pal!
    You saved me 🙂
    Cheers

  2. Lash says:

    Hi there

    I am facing an issue, when I use SetExecutionCredentials method to specify credentials for report, it gives me an error saying that the credentials for report must be set to prompt for credentials.

    Any idea how to resolve it?
    Thanks in advance

    • ikbalouch says:

      Thanks Lash, Rnd on this topic also saved me 🙂

      For SetExecutionCredentials, if data source of your reports have setting as prompt for credentials than you need to set these values else you need not to call SetExecutionCredentials method.

      I hope I answered your query…

  3. Rita Lindahl says:

    I have been previously searching around in your articles upon heading about them a pal and was pleased after i thought it was after seeking some time. I just desired to comment to signify my appreciation in your posts as it’s actually very encouraging, and many writers are rarely getting the acknowledgment they deserve.

    • ikbalouch says:

      Thanks a lot for your appreciation and encouragement, I really appreciate it. Yeh you are right, people come and get what they want and leave without telling the writer that either it was good or bad. Once again thanks a lot dear for the appreciation.
      Regards

  4. Amit Mohod says:

    Wow.. this is what i was looking for. It helped me to render a report using web service.
    now, when the

    rsExec.Render(execHeader,trusteduserHeader, format,
    deviceInfo,out result, out extension,
    out mimeType, out encoding,
    out warnings, out streamIDs);

    statement is executing, is it possible to judge how much time ist will take to render the report.
    In case of huge reports, I want to show some progress status showing the remaining pages to be rendered, so user will not keep staring to blank web page or to some static gif image.

    Basically my question is, is it somehow possible to read the current status of the execution? like how much more time it will take to complete the rendering..

    Can anyone give me some pointers?

    • Amit!
      Thanks a lot for your comments and appreciating my effort.
      As far as I know there is no mechanism to figure out the current status of report execution and also you can’t know how much time it is going to take.
      However I will look into it and if I find any solution will post it over here.

      A quick solution that hit my mind is to execute a report and calculate the time it took to render, keep that time in database or saved anywhere. When that report is called bring up that time and before rsExec.Render method is called, start a progress bar according to the saved time and after rsExec.Render method close the progress bar.
      Its a rough idea, I will look more into it but you can also try it at your end.

      Regards

      • Amit Mohod says:

        Well, I think precalculated time can be used as benchmark….it will vary server to server..
        Anyways, will google sm more.. or if u come across, let me know.

      • Amit Mohod says:

        sorry.. i meant “precalculated time cant be used as benchmark” be..

      • Yeh I agree that precalculated time can’t be used as benchmark but can be used as a round about value. I didn’t understand that time will vary server to server. A report is deployed on a server and every user will get it from same server. Yeh load on server can have impact on that time.

        Anyhow I will start looking into it in next few hours and get back to you if i find anything of interest.

        Regards

  5. Mary Spencer says:

    That is just how i feel about it.

  6. kauffman walmart tires says:

    Outstanding post, you have pointed out some good points , I also think this s a very superb website.

  7. Mike says:

    That thing about passing in null to get rid of the ‘value cannot be null’ error… brilliant. It saved me. I would have never tried that had I not read it here.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: