Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SSRS Subscriptions failure - should we always listen to our DBA's?

 During the configuration of one of the SSRS instances I have stumbled upon a problem with SSRS Subscription. Subscriptions were scheduled but were never executed. "Last run ..." stayed unchanged showing that a subscription execution was never even attempted.

I searched everywhere - at the computer clock, at the SSRS log files, and Event logs but could not find the problem. Googling for the answer didn't help too - since there was no error message by which I could narrow down my search...

Looking desperately everywhere, I peeped in the SQL Services Job Activity Monitor and there I noticed that some jobs with ugly uniqueid names were failing. And to my astonishment the jobs that were failing were the SSRS jobs for the subscriptions!

The problem was - SSRS instance did not like the name of the SSRS database, because it started with the digit, ex: 123_ReportServer. When I tried to execute the SSRS job manually I got the error "Incorrect syntax near '123'" - and when I placed [] around the 123_ReportServer - the job successfully worked and subscription was executed. It took a complete reinstall of the SSRS instance with the new name for the SSRS database for the issue to be resolved.

By the way, you cannot just rename the SSRS database and expect your SSRS instance to work with the new database name. When the ReportServer DB name changes - the complete reinstall is required for SSRS instance to work correctly.

Lesson Learned: never name the database starting with the digit or symbol, even if that was a standard enforced by your local DBA team.

Comments

Posted by Anonymous on 4 August 2010

Pingback from  Twitter Trackbacks for                 SQL Server Central, SSRS Subscriptions failure - should we always listen to our DBA's? - aedna@SQL         [sqlservercentral.com]        on Topsy.com

Leave a Comment

Please register or log in to leave a comment.