Don’t touch that schema!!!
You know how every product that has an underlying database has documentation that says not to modify the schema? Do you always pay attention to that warning?
If your product is Reporting Services, I just ran into a case today which I hope convinces you to keep your hands off!!!
The problem was that the customer could not edit any of his subscriptions. They would run, but he could not modify any of their properties. Every time he would attempt to modify the subscription, he would get an error about being unable to cast a GUID to a string:
System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.InvalidCastException: Unable to cast object of type 'System.Guid' to type 'System.String'. at System.Data.SqlClient.SqlBuffer.get_String() at Microsoft.ReportingServices.Library.InstrumentedSqlDataReader.<>c__DisplayClass3d.<GetString>b__3c() at Microsoft.ReportingServices.Library.SqlBoundaryWithReturn`1.Invoke(Method m) at Microsoft.ReportingServices.Library.SubscriptionImpl..ctor(IDataRecord record, Int32 indexStart) at Microsoft.ReportingServices.Library.SubscriptionDB.GetSubscription(Guid id) at Microsoft.ReportingServices.Library.SubscriptionManager.DeleteSubscription(Guid id) at Microsoft.ReportingServices.Library.DeleteSubscriptionAction.PerformActionNow() at Microsoft.ReportingServices.Library.RSService.ExecuteBatch(Guid batchId) at Microsoft.ReportingServices.WebServer.ReportingService2005Impl.ExecuteBatch() at Microsoft.ReportingServices.WebServer.ReportingService2005.ExecuteBatch()
Based on the source code, I found the stored procedure being run and ran it against a restored backup of the customer’s database. The stored procedure ran fine and the data looked normal and valid. So, I continued looking at the code trying to identify which one of the returned fields returned a GUID and then following the source to see where it was being assigned to a string value (which is impossible and will always fail). However, I couldn’t find any place in the code where that could happen.
I did notice, though, that SSRS always attempts to find a field by index number and not name. While at first glance this seems like a more error prone approach, it is better performing that looking up a field by name. When I saw that, I realized that having the stored procedure out of synch with the code could cause a problem like this. Therefore, I checked the database version of the customer’s database against the known database version for the build of SSRS they were running. They matched, so it wasn’t a failed upgrade-type scenario.
The next thing I checked was the actual syntax of the stored procedure. I exported both the official definition and the one from the customer’s database. Guess what? They didn’t match!! The customer’s stored procedure had an extra field being returned. It was even more obvious when I looked at the definition and noticed that the additional field was in the structure of “alias.field”. SSRS always uses “alias.[Field]”.
The moral of the story? Not only is modifying your SSRS database not supported and may have unintended performance impacts, but it can also break your installation!!!
Evan Basalik | Senior Support Escalation Engineer | Microsoft SQL Server Escalation Services