SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-2177173%22%20slang%3D%22en-US%22%3ESQL%20SSRS%20CASE%20AFTER%20UPGRADE%26nbsp%3B%20to%2013.0.5865.1%2C%20unable%20to%20create%20new%20subscriptions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2177173%22%20slang%3D%22en-US%22%3E%3CP%3EAfter%20Upgrading%20one%20of%20the%20SQL%202016%20environments%20to%20to%2013.0.5865.1%2C%20we%20have%20issues%20creating%20subscriptions%20on%20SSRS.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEach%20time%20someone%20tries%20to%20create%20a%20new%20subscription%20they%20receive%20the%20error%3A%3C%2FP%3E%3CP%3E%E2%80%9C%3CSTRONG%3EAn%20error%20has%20occurred.%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3EAn%20error%20occurred%20within%20the%20report%20server%20database.%20This%20may%20be%20due%20to%20a%20connection%20failure%2C%20timeout%20or%20low%20disk%20condition%20within%20the%20database.%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%E2%80%9D.%3C%2FP%3E%3CP%3EIn%20de%20SSRS%20log%20i%20can%20see%201%20error%20(with%20stacktrace)%20System.Data.SqlClient.SqlException%3A%20The%20specified%20%40name%20('Report%20Server')%20already%20exists.%3C%2FP%3E%3CP%3EAfter%20rebooting%2C%20restarting%2C%20checking%20config%2C%20user%20settings%20etc%E2%80%A6%3C%2FP%3E%3CP%3EChecking%20all%20db.%E2%80%99s%20and%20tempdb%20of%20the%20SSRS%2C%20System%20DB%E2%80%99s%2C%20file%20size%20limits%2C%20%26nbsp%3Bdisk%20limits%2C%20time-out%20limits%20etc.%20.%3C%2FP%3E%3CP%3EI%20verified%20the%20process%20of%20creating%20the%20subscription%20and%20noted%20that%20the%20problem%20seems%20to%20be%20related%20to%20the%20execution%20of%20exec%20msdb.dbo.sp_add_category%20%40class%3D'JOB'%2C%40type%3D'LOCAL'%2C%40name%3DN'Report%20Server'%3C%2FP%3E%3CP%3EAfter%20investigating%20the%20Stored%20Procedure%2C%20i%20came%20to%20the%20conclusion%20that%20the%20problem%20seems%20to%20come%20from%20the%20msdb.dbo.syscategories.%3C%2FP%3E%3CP%3EIn%20that%20table%20there%20is%20a%20record%20(100%26nbsp%3B%26nbsp%3B%26nbsp%3B%201%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Report%20Server)%2C%20when%20I%20alter%20the%20value%20of%20the%20name%20field%20Report%20Server%20to%20something%20else%20and%20try%20to%20create%20a%20new%20SSRS%20subscription%20on%20a%20report%2C%20the%20subscription%20is%20created%20without%20any%20issue.%3C%2FP%3E%3CP%3EAfter%20this%20process%20is%20noticed%20that%20a%20new%20record%20was%20created%20in%20the%20msdb.dbo.syscategories%20table%20(101%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Report%20Server)%3C%2FP%3E%3CP%3EAgain%2C%20I%20tried%20to%20create%20a%20new%20subscription%2C%20the%20creation%20fails%20with%20the%20same%20error%E2%80%99s.%3C%2FP%3E%3CP%3EOnce%20again%20I%20renamed%20the%20101%20record%20and%20now%20I%20was%20able%20to%20create%20a%20subscription%20onze%20again%2C%20a%20new%20record%20was%20created%20in%20the%20msdb.dbo.syscategories%20(102%201%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Report%20Server%20).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20process%20is%20stuck%20%2C%20i%20have%20some%20kind%20of%20workaround%20but%20can't%20find%20the%20solution%2C%20except%20altering%20a%26nbsp%3B%20Sys%20SP%2C%20something%20i%20don't%20want%20to%20do%20for%20all%20the%20obvious%20reasons.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ESSRS%20log%20%26nbsp%3BError%20%2BTrace%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMicrosoft.ReportingServices.Portal.WebHost!reportserverwebapp!25!02%2F22%2F2021-23%3A19%3A37%3A%3A%20e%20ERROR%3A%20%5Bokx1fx1u%5D%3A%20OData%20exception%20occurred%3A%20System.Web.Services.Protocols.SoapException%3A%20System.Web.Services.Protocols.SoapException%3A%20An%20error%20occurred%20within%20the%20report%20server%20database.%26nbsp%3B%20This%20may%20be%20due%20to%20a%20connection%20failure%2C%20timeout%20or%20low%20disk%20condition%20within%20the%20database.%20---%26gt%3B%20Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException%3A%20An%20error%20occurred%20within%20the%20report%20server%20database.%26nbsp%3B%20This%20may%20be%20due%20to%20a%20connection%20failure%2C%20timeout%20or%20low%20disk%20condition%20within%20the%20database.%20---%26gt%3B%20System.Data.SqlClient.SqlException%3A%20The%20specified%20%40name%20('Report%20Server')%20already%20exists.%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%20at%20Microsoft.ReportingServices.Library.ReportingService2005Impl.CreateSubscription(String%20Report%2C%20ExtensionSettings%20ExtensionSettings%2C%20Boolean%20isDataDriven%2C%20DataRetrievalPlan%20DataRetrievalPlan%2C%20String%20Description%2C%20String%20EventType%2C%20String%20MatchData%2C%20ParameterValueOrFieldReference%5B%5D%20Parameters%2C%20Guid%20batchId%2C%20String%26amp%3B%20SubscriptionID)%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%20at%20Microsoft.ReportingServices.Library.ReportingService2005Impl.CreateSubscription(String%20Report%2C%20ExtensionSettings%20ExtensionSettings%2C%20Boolean%20isDataDriven%2C%20DataRetrievalPlan%20DataRetrievalPlan%2C%20String%20Description%2C%20String%20EventType%2C%20String%20MatchData%2C%20ParameterValueOrFieldReference%5B%5D%20Parameters%2C%20String%26amp%3B%20SubscriptionID)%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%20at%20Microsoft.ReportingServices.WebServer.ReportingService2010.CreateSubscription(String%20ItemPath%2C%20ExtensionSettings%20ExtensionSettings%2C%20String%20Description%2C%20String%20EventType%2C%20String%20MatchData%2C%20ParameterValue%5B%5D%20Parameters%2C%20String%26amp%3B%20SubscriptionID)%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%20at%20System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage%20message%2C%20WebResponse%20response%2C%20Stream%20responseStream%2C%20Boolean%20asyncCall)%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%20at%20System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String%20methodName%2C%20Object%5B%5D%20parameters)%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%20at%20Microsoft.SqlServer.ReportingServices2010.ReportingService2010.CreateSubscription(String%20ItemPath%2C%20ExtensionSettings%20ExtensionSettings%2C%20String%20Description%2C%20String%20EventType%2C%20String%20MatchData%2C%20ParameterValue%5B%5D%20Parameters)%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%20at%20Microsoft.SqlServer.ReportingServices2010.RSConnection2010.%26lt%3B%26gt%3Bc__DisplayClass5a.%3CCREATESUBSCRIPTION%3Eb__59()%3C%2FCREATESUBSCRIPTION%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%20at%20Microsoft.SqlServer.ReportingServices2010.RSConnection2010.SoapMethodWrapper%601.ExecuteMethod(Boolean%20setConnectionProtocol)%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%20at%20Microsoft.SqlServer.ReportingServices2010.RSConnection2010.SoapMethodWrapper%601.ExecuteMethod()%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%20at%20Microsoft.SqlServer.ReportingServices2010.RSConnection2010.CreateSubscription(String%20itemPath%2C%20ExtensionSettings%20extensionSettings%2C%20String%20description%2C%20String%20eventType%2C%20String%20matchData%2C%20ParameterValue%5B%5D%20parameters)%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%20at%20Microsoft.ReportingServices.Portal.Services.SoapProxy.SoapRS2010Proxy.%26lt%3B%26gt%3Bc__DisplayClass1a.%3CCREATESUBSCRIPTION%3Eb__19()%3C%2FCREATESUBSCRIPTION%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%20at%20Microsoft.ReportingServices.Portal.Services.SoapProxy.SoapAuthenticationHelper.ExecuteWithWindowsAuth%5BTReturn%5D(SoapHttpClientProtocol%20soapClient%2C%20IPrincipal%20userPrincipal%2C%20Func%601%20func)%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%20at%20Microsoft.ReportingServices.Portal.Services.SoapProxy.SoapAuthenticationHelper.ExecuteWithCorrespondingAuthMechanism%5BTReturn%5D(SoapHttpClientProtocol%20soapClient%2C%20IPrincipal%20userPrincipal%2C%20Func%601%20func)%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%20at%20Microsoft.ReportingServices.Portal.Services.SoapProxy.SoapRS2010Proxy.CreateSubscription(IPrincipal%20userPrincipal%2C%20String%20itemPath%2C%20ExtensionSettings%20extensionSettings%2C%20String%20description%2C%20String%20eventType%2C%20String%20matchData%2C%20ParameterValue%5B%5D%20parameters)%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%20at%20Microsoft.ReportingServices.Portal.Repositories.SubscriptionService.CreateSubscription(IPrincipal%20userPrincipal%2C%20Subscription%20subscription)%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%20at%20Microsoft.ReportingServices.Portal.ODataWebApi.V1.Controllers.SubscriptionsController.AddEntity(Subscription%20entity)%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%20at%20Microsoft.ReportingServices.Portal.ODataWebApi.V1.Controllers.Reflection.EntitySetReflectionODataController%601.AddEntity(T%20entity%2C%20T%26amp%3B%20createdEntity)%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%20at%20Microsoft.ReportingServices.Portal.ODataWebApi.V1.Controllers.Reflection.EntitySetReflectionODataController%601.Post(ODataPath%20oDataPath%2C%20T%20value)%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%20at%20lambda_method(Closure%20%2C%20Object%20%2C%20Object%5B%5D%20)%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%20at%20System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.%26lt%3B%26gt%3Bc__DisplayClass10.%3CGETEXECUTOR%3Eb__9(Object%20instance%2C%20Object%5B%5D%20methodParameters)%3C%2FGETEXECUTOR%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%20at%20System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ExecuteAsync(HttpControllerContext%20controllerContext%2C%20IDictionary%602%20arguments%2C%20CancellationToken%20cancellationToken)%3C%2FP%3E%3CP%3E---%20End%20of%20stack%20trace%20from%20previous%20location%20where%20exception%20was%20thrown%20---%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2186244%22%20slang%3D%22en-US%22%3ERe%3A%20SQL%20SSRS%20CASE%20AFTER%20UPGRADE%26nbsp%3B%20to%2013.0.5865.1%2C%20unable%20to%20create%20new%20subscriptions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2186244%22%20slang%3D%22en-US%22%3E%3CP%3EDear%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20issue%20was%20fixed%20after%20upgrading%20to%20CU16.%3C%2FP%3E%3CP%3EApparently%20there%20is%20a%20an%20issue%20with%20CU15%2C%20in%20y%20case%20a%20Sql2014%20Bi%20edtion%20was%20installed%20side%20by%20side%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22AHALEGRE87_0-1614895239320.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F261018iF25B09A2FC21F14D%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22AHALEGRE87_0-1614895239320.png%22%20alt%3D%22AHALEGRE87_0-1614895239320.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

 

 

1 Reply
best response confirmed by AHALEGRE87 (New Contributor)
Solution

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_0-1614895239320.png