After Upgrading one of the SQL 2016 environments to to 13.0.5865.1, we have issues creating subscriptions on SSRS.
Each time someone tries to create a new subscription they receive the error:
“An error has occurred.
An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database.
”.
In de SSRS log i can see 1 error (with stacktrace) System.Data.SqlClient.SqlException: The specified @name ('Report Server') already exists.
After rebooting, restarting, checking config, user settings etc…
Checking all db.’s and tempdb of the SSRS, System DB’s, file size limits, disk limits, time-out limits etc. .
I verified the process of creating the subscription and noted that the problem seems to be related to the execution of exec msdb.dbo.sp_add_category @class='JOB',@type='LOCAL',@name=N'Report Server'
After investigating the Stored Procedure, i came to the conclusion that the problem seems to come from the msdb.dbo.syscategories.
In that table there is a record (100 1 1 Report Server), when I alter the value of the name field Report Server to something else and try to create a new SSRS subscription on a report, the subscription is created without any issue.
After this process is noticed that a new record was created in the msdb.dbo.syscategories table (101 1 1 Report Server)
Again, I tried to create a new subscription, the creation fails with the same error’s.
Once again I renamed the 101 record and now I was able to create a subscription onze again, a new record was created in the msdb.dbo.syscategories (102 1 1 Report Server ).
The process is stuck , i have some kind of workaround but can't find the solution, except altering a Sys SP, something i don't want to do for all the obvious reasons.
SSRS log Error +Trace
Microsoft.ReportingServices.Portal.WebHost!reportserverwebapp!25!02/22/2021-23:19:37:: e ERROR: [okx1fx1u]: OData exception occurred: System.Web.Services.Protocols.SoapException: System.Web.Services.Protocols.SoapException: An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database. ---> Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException: An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database. ---> System.Data.SqlClient.SqlException: The specified @name ('Report Server') already exists.
at Microsoft.ReportingServices.Library.ReportingService2005Impl.CreateSubscription(String Report, ExtensionSettings ExtensionSettings, Boolean isDataDriven, DataRetrievalPlan DataRetrievalPlan, String Description, String EventType, String MatchData, ParameterValueOrFieldReference[] Parameters, Guid batchId, String& SubscriptionID)
at Microsoft.ReportingServices.Library.ReportingService2005Impl.CreateSubscription(String Report, ExtensionSettings ExtensionSettings, Boolean isDataDriven, DataRetrievalPlan DataRetrievalPlan, String Description, String EventType, String MatchData, ParameterValueOrFieldReference[] Parameters, String& SubscriptionID)
at Microsoft.ReportingServices.WebServer.ReportingService2010.CreateSubscription(String ItemPath, ExtensionSettings ExtensionSettings, String Description, String EventType, String MatchData, ParameterValue[] Parameters, String& SubscriptionID)
at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at Microsoft.SqlServer.ReportingServices2010.ReportingService2010.CreateSubscription(String ItemPath, ExtensionSettings ExtensionSettings, String Description, String EventType, String MatchData, ParameterValue[] Parameters)
at Microsoft.SqlServer.ReportingServices2010.RSConnection2010.<>c__DisplayClass5a.<CreateSubscription>b__59()
at Microsoft.SqlServer.ReportingServices2010.RSConnection2010.SoapMethodWrapper`1.ExecuteMethod(Boolean setConnectionProtocol)
at Microsoft.SqlServer.ReportingServices2010.RSConnection2010.SoapMethodWrapper`1.ExecuteMethod()
at Microsoft.SqlServer.ReportingServices2010.RSConnection2010.CreateSubscription(String itemPath, ExtensionSettings extensionSettings, String description, String eventType, String matchData, ParameterValue[] parameters)
at Microsoft.ReportingServices.Portal.Services.SoapProxy.SoapRS2010Proxy.<>c__DisplayClass1a.<CreateSubscription>b__19()
at Microsoft.ReportingServices.Portal.Services.SoapProxy.SoapAuthenticationHelper.ExecuteWithWindowsAuth[TReturn](SoapHttpClientProtocol soapClient, IPrincipal userPrincipal, Func`1 func)
at Microsoft.ReportingServices.Portal.Services.SoapProxy.SoapAuthenticationHelper.ExecuteWithCorrespondingAuthMechanism[TReturn](SoapHttpClientProtocol soapClient, IPrincipal userPrincipal, Func`1 func)
at Microsoft.ReportingServices.Portal.Services.SoapProxy.SoapRS2010Proxy.CreateSubscription(IPrincipal userPrincipal, String itemPath, ExtensionSettings extensionSettings, String description, String eventType, String matchData, ParameterValue[] parameters)
at Microsoft.ReportingServices.Portal.Repositories.SubscriptionService.CreateSubscription(IPrincipal userPrincipal, Subscription subscription)
at Microsoft.ReportingServices.Portal.ODataWebApi.V1.Controllers.SubscriptionsController.AddEntity(Subscription entity)
at Microsoft.ReportingServices.Portal.ODataWebApi.V1.Controllers.Reflection.EntitySetReflectionODataController`1.AddEntity(T entity, T& createdEntity)
at Microsoft.ReportingServices.Portal.ODataWebApi.V1.Controllers.Reflection.EntitySetReflectionODataController`1.Post(ODataPath oDataPath, T value)
at lambda_method(Closure , Object , Object[] )
at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass10.<GetExecutor>b__9(Object instance, Object[] methodParameters)
at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ExecuteAsync(HttpControllerContext controllerContext, IDictionary`2 arguments, CancellationToken cancellationToken)
--- End of stack trace from previous location where exception was thrown ---
