14
- November
2016
Posted By : Zeng Yinghua (Sandy)
SCCM 1606 + SQL 2016: Report subscription failed

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 :

<Extension Name="EXCEL" Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Microsoft.ReportingServices.ExcelRendering" Visible="false"/>
<Extension Name="EXCELOPENXML" Type="Microsoft.ReportingServices.Rendering.ExcelOpenXmlRenderer.ExcelOpenXmlRenderer,Microsoft.ReportingServices.ExcelRendering"/>
<Extension Name="WORD" Type="Microsoft.ReportingServices.Rendering.WordRenderer.WordDocumentRenderer,Microsoft.ReportingServices.WordRendering" Visible="false"/>
<Extension Name="WORDOPENXML" Type="Microsoft.ReportingServices.Rendering.WordRenderer.WordOpenXmlRenderer.WordOpenXmlDocumentRenderer,Microsoft.ReportingServices.WordRendering"/>

To these:

<!--Extension Name="EXCEL" Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Microsoft.ReportingServices.ExcelRendering" Visible="false"/-->
<Extension Name="EXCEL" Type="Microsoft.ReportingServices.Rendering.ExcelOpenXmlRenderer.ExcelOpenXmlRenderer,Microsoft.ReportingServices.ExcelRendering"/>
<Extension Name="EXCELOPENXML" Type="Microsoft.ReportingServices.Rendering.ExcelOpenXmlRenderer.ExcelOpenXmlRenderer,Microsoft.ReportingServices.ExcelRendering" Visible="false"/>
<!--Extension Name="WORD" Type="Microsoft.ReportingServices.Rendering.WordRenderer.WordDocumentRenderer,Microsoft.ReportingServices.WordRendering" Visible="false"/-->
<Extension Name="WORD" Type="Microsoft.ReportingServices.Rendering.WordRenderer.WordOpenXmlRenderer.WordOpenXmlDocumentRenderer,Microsoft.ReportingServices.WordRendering"/>
<Extension Name="WORDOPENXML" Type="Microsoft.ReportingServices.Rendering.WordRenderer.WordOpenXmlRenderer.WordOpenXmlDocumentRenderer,Microsoft.ReportingServices.WordRendering" Visible="false"/>

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).

/*Execute SSRS Subscription Manually*/
/*Connect to Database ReportServer*/
SELECT
     S.ScheduleID AS SQLAgent_Job_Name
     ,SUB.Description AS Sub_Desc
     ,SUB.DeliveryExtension AS Sub_Del_Extension
     ,C.Name AS ReportName
     ,C.Path AS ReportPath
FROM ReportSchedule RS
     INNER JOIN Schedule S ON (RS.ScheduleID = S.ScheduleID)
     INNER JOIN Subscriptions SUB ON (RS.SubscriptionID = SUB.SubscriptionID)
     INNER JOIN [Catalog] C ON (RS.ReportID = C.ItemID AND SUB.Report_OID = C.ItemID)

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

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

Comments

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

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

      • Zeng Yinghua (Sandy)

        June 29, 2017 at 12:36

        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. ๐Ÿ™‚

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

  • 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!

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

  • Thanks you Zeng,
    I Had the same issue, After upgrade from sql 2008 to sql 2016 SP2. Start working after following your poste.

  • Worked like Charm. You are a Savior.
    Thanks a ton.

  • Hello,
    I did the update but now the SQL Reporting service stopped and refuse to start again.
    SQL Server Configuration Manager
    Starting service
    The request failed or the service did not respond in a timely fashion.
    Consult the event log or other applicable error logs for details
    ———————————————————————————————————————————————————————–
    The description for Event ID 133 from source Report Server cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.

    If the event originated on another computer, the display information had to be saved with the event.
    The following information was included with the event:
    E:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\ReportServer\RSReportServer.config
    ———————————————————————————————————————————————————————–
    Even I rebooted the server and still not started!!!
    Checking now…
    Thanks,
    Dom

  • Hello,
    When I put back the rsreportserver.config saved previously the service starts immediately.
    What could be wrong?
    Thanks,
    Dom

  • I think I forgot the — at the end of the line excluded/changed…
    Validated the change works with — ADDED
    Thanks
    Dom

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.