On May 29, 11:02 am, Kramer <casey.kra...@gmail.com> wrote:
> Okay, the background: I've got an RS 2000 (SP2) server running on what
> should be a patched and updated Windows 2000 Server box (I don't have
> direct access to the server so I can't verify all the patches myself,
> but since it is a production box I'm going to make the assumption that
> critical patches have been installed). This is also the same box that
> is running the SQL Server.
>
> Now, here is the issue....I've got a single report, to which I have
> created a number of linked reports (lets say 28 total, but that is
> just a guess right now). All of these are set to run on a shared
> schedule (about half weekly, and half monthly) and update a report
> snapshot. Each also has an email subscription set up to go when the
> snapshot is updated. What I'm seeing is that all of the report
> snapshots are running as scheduled, but not all of the subscriptions
> are being fired off when those snapshots complete. To make things
> more exciting, there doesn't seem to be much reason behind which work
> and which do not. Looking at the "Subscription" page for the reports
> which did not work, I don't see any indication that the subscriptions
> was even tried (no error messages). I also do not see anything in the
> event log to indicate that the ReportServer service, or the SQL agent
> were down when this was supposed to be happening.
>
> I do not have the actual RS log files yet, so I cannot say what may or
> may not be in there, but I have requested they be sent to me. I will
> be glad to post any relevant information when I receive them.
>
> So has anyone seen this? Do I have too many email subscriptions tied
> to the same shared schedule? Do I have a problem with the SQL agent?
>
> Any suggestions would be greatly appreciated.
>
> Casey Kramer

On May 29, 11:55 am, Kramer <casey.kra...@gmail.com> wrote:
> On May 29, 11:02 am, Kramer <casey.kra...@gmail.com> wrote:
>
>
>
> > Okay, the background: I've got an RS 2000 (SP2) server running on what
> > should be a patched and updated Windows 2000 Server box (I don't have
> > direct access to the server so I can't verify all the patches myself,
> > but since it is a production box I'm going to make the assumption that
> > critical patches have been installed). This is also the same box that
> > is running the SQL Server.
>
> > Now, here is the issue....I've got a single report, to which I have
> > created a number of linked reports (lets say 28 total, but that is
> > just a guess right now). All of these are set to run on a shared
> > schedule (about half weekly, and half monthly) and update a report
> > snapshot. Each also has an email subscription set up to go when the
> > snapshot is updated. What I'm seeing is that all of the report
> > snapshots are running as scheduled, but not all of the subscriptions
> > are being fired off when those snapshots complete. To make things
> > more exciting, there doesn't seem to be much reason behind which work
> > and which do not. Looking at the "Subscription" page for the reports
> > which did not work, I don't see any indication that the subscriptions
> > was even tried (no error messages). I also do not see anything in the
> > event log to indicate that the ReportServer service, or the SQL agent
> > were down when this was supposed to be happening.
>
> > I do not have the actual RS log files yet, so I cannot say what may or
> > may not be in there, but I have requested they be sent to me. I will
> > be glad to post any relevant information when I receive them.
>
> > So has anyone seen this? Do I have too many email subscriptions tied
> > to the same shared schedule? Do I have a problem with the SQL agent?
>
> > Any suggestions would be greatly appreciated.
>
> > Casey Kramer
>
> Okay, here is some additional information, which seems to support my
> theory that there may be too many things going on at once. it looks
> like there was a deadlock problem. Is there any way around this? Do
> I need to limit the number of subscriptions attached to a single
> shared schedule?
>
> ReportingServicesService!schedule!540!5/28/2007-05:02:06:: Error
> processing event 'SnapshotUpdated', data = ad6d16c2-acb2-421b-
> a0b1-75d944691a52, error = System.Data.SqlClient.SqlException:
> Transaction (Process ID 67) was deadlocked on lock resources with
> another process and has been chosen as the deadlock victim. Rerun the
> transaction.
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
> at
> Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery()
> at
> Microsoft.ReportingServices.Library.CatalogQuery.ExecuteQuery(String
> query, Hashtable parameters, CommandType type, Boolean getReader)
> at
> Microsoft.ReportingServices.Library.CatalogQuery.ExecuteNonQuery(String
> query, Hashtable parameters, CommandType type)
> at
> Microsoft.ReportingServices.Library.ReportExecutionSnapshotUpdateEventHandler.HandleEvent(ICatalogQuery
> catalogQuery, String eventType, String eventData)
> at
> Microsoft.ReportingServices.Library.EventQueueWorker.ProcessSingleEvent(IEventHandler
> eventHandler, EventQueueItem item)