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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Reporting Services » Reporting Services » Possible to update ReportServer.Subscriptions...

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

Rate Topic

Display Mode

Topic Options

Author

Message

philip_egan

Posted Tuesday, November 13, 2012 4:54 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, February 18, 2013 6:35 AM
Points: 2, Visits: 66

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

Post #1384032

Jason Selburg

Posted Tuesday, November 13, 2012 7:27 AM

SSCrazy

Group: General Forum Members
Last Login: Friday, June 14, 2013 1:01 PM
Points: 2,582, Visits: 3,555

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 you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Post #1384088

philip_egan

Posted Tuesday, November 13, 2012 7:49 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, February 18, 2013 6:35 AM
Points: 2, Visits: 66

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.

Post #1384107

« Prev Topic | Next Topic »

Permissions