Partilhar via

Don’t touch that schema!!!

  • Artigo

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

Comments

  • Anonymous
    May 30, 2010
    One piece of feedback this post got that wasn't persisted (due to the MSDN Blogs upgrade) was someone questioning why SSRS would use the non-standard approach of specifying a field by index instead of by name. Referencing a field by name requires a lot of extra effort to figure out which index matches a given field name.  If you want to see all the complexity, check out the definition for Item in SqlClient.  It calls GetValue on the result of GetOrdinal.  The GetOrdinal call eventually (after several layers of depth) results in looping through all of the fields to see which one matches the desired field.   This extra effort is definitely something to be avoided in a service application where performance is key.

  • Anonymous
    May 30, 2010
    >This extra effort is definitely something to be avoided in a service application where performance is key. An excellent point. But shouldn't that be documented? Ideally, i would think the performance degradation should be quantified. Is it really that much less? When i used to code similar things, i always used byname, so as to avoid this very issue. Thanx for the reply. I really enjoy the postings on this blog.

  • Anonymous
    June 06, 2010
    Watch for a post in the near future that explains the performance benefit.

  • Anonymous
    June 06, 2010
    >Watch for a post in the near future that explains the performance benefit. You betcha!