January 15, 2009 at 10:08 am
We created a development SSRS environment by copied the .mdf and .ldf files to a different server and attached them. Since it was from the production environment it already had a lot of subscriptions set up on it. I tried to delete on from the server and got this error message:
System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Data.SqlClient.SqlException: Only members of sysadmin role are allowed to update or delete jobs owned by a different login. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.SubscriptionDB.DeleteSubscription(Guid id) at Microsoft.ReportingServices.Library.SubscriptionManager.DeleteSubscription(Guid id) at Microsoft.ReportingServices.Library.DeleteSubscriptionAction.PerformActionNow() at Microsoft.ReportingServices.Library.DeleteSubscriptionAction.PerformActionInBatch(CallParameters parameters) at Microsoft.ReportingServices.Library.RSService.ExecuteBatch(Guid batchId) at Microsoft.ReportingServices.WebServer.ReportingService2005Impl.ExecuteBatch() at Microsoft.ReportingServices.WebServer.ReportingService2005.ExecuteBatch() --- End of inner exception stack trace ---
So I requested to be added to the sysadmin, and after I was added I still get the same error message. I then decided to find the subscription tables. So I picked a subscription and deleted it. The subscription is gone, but now if you click on the subscriptions tab for the report you get this error message:
An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help For more information about this error navigate to the report server on the local server machine, or enable remote errors
And the subscription is still sent out.
Thoughts, ideas on how to get rid of the subscriptions? For now I have turned off the sql agent on this box so that no more subscriptions are sent out, but long term I would like to be able to test subscriptions.
January 16, 2009 at 3:30 am
You can connect reporting services into management studio and can see all the detail you can see on report server on table of management studio. Please look for table called subscription or something. U can see the detail of all subscription. U can delete one record from there and that subscription will be deleted from your report server.
January 16, 2009 at 3:43 am
DELETE FROM [ReportServer].[dbo].[Subscriptions] -- to delete all the existing Subscriptions
-Vikas Bindra
January 16, 2009 at 6:30 am
Thanks for the ideas, however I tried to do that, delete from subscriptions, and I just picked one subscription to delete and it still sends out the email. I was thinking that maybe there is a table some where that I am missing, or maybe there is something in the msdb database.
January 16, 2009 at 12:07 pm
So I ended up using the stored procedure [DeleteSubscription] and deleting the subscriptions 1 at a time, all 400+ of them. Sql Server Agent had to be running, but it seems to be ok. I did find an article on MSDN which also did not work, but maybe it will work for someone else. http://technet.microsoft.com/en-us/library/ms155832(SQL.90).aspx
January 16, 2009 at 12:12 pm
possible to delete all the SQL Agent jobs as well?
They'll show up with GUID in their names that don't make sense
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply