SCCM 1606 + SQL 2016: Report subscription failed

By | November 14, 2016

Today my lovely handsome colleague Antti told me that SCCM report subscription doesn't work, when tried to create report subscription send email attach with excel.

Errors

  • Web.Services.Protocols.SoapException: A delivery error has occurred. ---> Microsoft.ReportingServices.Diagnostics.Utilities.DeliveryErrorException: A delivery error has occurred. ---> Microsoft.ReportingServices.Diagnostics.Utilities.InvalidExtensionParameter: One of the extension parameters is not valid for the following reason: The value 'EXCEL' is not valid for setting 'Render Format'. at Microsoft.ReportingServices.Library.ReportingService2005Impl.CreateSubscription(String Report, ExtensionSettings ExtensionSettings, Boolean isDataDriven, DataRetrievalPlan DataRetrievalPlan, String Description, String EventType, String MatchData, ParameterValueOrFieldReference[] Parameters, Guid batchId, String& SubscriptionID)ย ย  at Microsoft.ReportingServices.WebServer.ReportingService2005.CreateSubscription(String Report, ExtensionSettings ExtensionSettings, String Description, String EventType, String MatchData, ParameterValue[] Parameters, String& SubscriptionID)

Problem was we are using SQL 2016 and SCCM 1606.

Same issue has mention in here, and there is already work arround, use web report portal. https://www.windows-noob.com/forums/topic/14479-the-create-subscription-wizard-completed-with-errors/

But I want to use SCCM admin console, and I don't want to recreate all my report subscriptions again. ๐Ÿ™‚

I found a Microsoft Article said that "The Microsoft Excel 2003 rendering extension is deprecated." "The EXCELOPENXML extension defines the Excel renderer for current (.xlsx) Excel files. The EXCEL extension defines the Excel 2003 version. Visible = โ€œfalseโ€ indicates the Excel 2003 renderer is hidden"

https://msdn.microsoft.com/en-us/library/dd255234.aspx?

So seems problem is SCCM 1606 report subscription is trying to use EXCEL render instead of EXCELOPENXML render. Maybe?

Well, I am not an expert of SQL, so I am not going do the guessing game. But I do manage to "fix" it, or AS-IS work arround.

Modify your rsreportserver.config file. Default path will be inย  "C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\ReportServer\rsreportserver.config"

Remember: Backup your rsreportserver.config file

Change these :

To these:

I put Visible="false" in EXCELOPENXML, because if I don't put anything, you will see two "Excel" when try to export reports from web portal. If I put Visible="false" in EXCEL, then subscription will failed again.

Same issue with Word, so I modified same settings for word.

Close SCCM Admin console and open it again, then you can create a new report subscription (send email and attach Excel) to test it.

You can manually kick off your report subscription jobs:

1. Open SQL Server Management Studio
2. Connect to the Report Server.
3. Choose ReportServer for the database.
4. Execute the following Query to determine the SQL Server Agent Job Name (Job ID).

5. Open SQL Server Agent, and run the job.

I hope will have offical solution soon, and I will keep this post updated.

8 thoughts on “SCCM 1606 + SQL 2016: Report subscription failed

  1. John Roca

    The issue still there with SQL 2016 P1 Cumulative update 2 and ConfigMgr 1702

    Reply
    1. John Roca

      After retest, without modification of this file, the subscription work, Windows 2012 R2, SQL 2016 SP CU2, ConfigMgr 1702

      Reply
      1. Zeng Yinghua (Sandy) Post author

        Hello John, thanks for letting us know. I am just wondering is it SQL 2016 SP CU2 fix the issue, or ConfigMgr 1702 fix the issue. ๐Ÿ™‚

        Reply
      2. J

        Wish I could say the same. Server 2012 R2, SQL 2016 SP1 CU5, ConfigMgr 1706 with UR1, and we’re seeing this problem.

        Reply
  2. Anders Rodland

    Now this is a great post that Iโ€™m bookmarking. Weโ€™r about to upgrade our customers from SQL 2012 SP3 to SQL 2016 SP1, all running ConfigMgr 1702.

    The upgrade itsellf is pretty straight forward, but now I know to not only test for this specific issue, but also how to fix it. Thank you!

    Reply
  3. selim atmaca

    Thank you Zeng. It works now. I noticed the issue after upgrading to SQL Server 2016

    Reply
    1. Zeng Yinghua (Sandy) Post author

      You are welcome Selim! ๐Ÿ™‚

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *