Forum Discussion

AHALEGRE87's avatar
AHALEGRE87
Copper Contributor
Mar 02, 2021

SQL SSRS CASE AFTER UPGRADE  to 13.0.5865.1, unable to create new subscriptions

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 ---

 

 

  • Dear

     

    The issue was fixed after upgrading to CU16.

    Apparently there is a an issue with CU15, in y case a Sql2014 Bi edtion was installed side by side:

     

     

  • AHALEGRE87's avatar
    AHALEGRE87
    Copper Contributor

    Dear

     

    The issue was fixed after upgrading to CU16.

    Apparently there is a an issue with CU15, in y case a Sql2014 Bi edtion was installed side by side:

     

     

Resources