Post reply

Possible to update ReportServer.Subscriptions table with a script? Problems when tried

  • November 13, 2012 at 4:54 am

    #266944

    Hi folks

    Hope you can help.

    We have SQL Server 2008R2 but don't have SQL Enterprise so cannot create Data-Driven Subscriptions.

    We have a requirement to set up 170+ subscriptions to the same report going to different e-mail addresses based on Region / Team structure.

    The problem comes in that these e-mail addresses may vary from week to week dependant upon annual leave, job changes etc.

    I have created an UPDATE SP which updates the relevant section of the [ExtensionSettings] field so that the TO ParameterValue e-mail address is updated based on our lookup table.

    The SP works as expected and a following SELECT statement shows that it has updated as expected.

    The problem comes when I go back into ReportManager and edit the subscription, it knows that it's been updated elsewhere and gives me the following error -

    An invalid subscription '187B9643-3232-46B9-81A8-E848D2BA20F7' was found. Subscription must be deleted and recreated. (rsInvalidSubscription) Get Online Help An error occurred while parsing EntityName. Line 1, position 86.

    Is there another field somewhere that I need to update that SQL runs a validation against to see if it's been tampered with?

    Any other suggestions?

    Thanks in advance

    Phil

  • November 13, 2012 at 7:27 am

    #1558486

    Yes, you can update those tables, but they are "fussy".

    Check out this article, it shows how to work around it. I'm fairly certain it works in 2008.

    http://www.sqlservercentral.com/articles/Development/2824/

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg

  • November 13, 2012 at 7:49 am

    #1558503

    Thanks Jason, will investigate further...

    As an update since my post, the subscriptions do still work after a script has updated them, it only complains when I try to go in and amend through the user interface so as long as nobody tries to access them through the front end then all will be well.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply