In the last weeks, in the wage of R2 RC release, I decided it was time to review all the MPs we developed to prepare an action plan for improvement. I have great expectations for R2.
So I started to revamp our development Lab adding missing MPs and updating obsolete ones. Soon I started to get error 31553 and my state reports started to mess up.
Error 31553 is pretty generic, my own was referring to an arithmetic overflow:
I knew the DW has a staging process so that new data is staged in tiny and fast tables and then inserted in the proper ones, this way the insertion completes quickly and the a background process takes care of the proper consolidation. In this case the table is dbo.ManagedEntityStage. Opening the table with SQL Server Studio returned too much rows for a transit table in a very limited lab environment, but the data seemed ok. The data is queuing up that’s for sure, so something must have gone wrong in the process. I drilled down further in the staging process, the main stored procedure here is dbo.StateProcessStaging so I started to step through the SQL code to check where an arithmetic could occur, while I was in this process I typed this simple query: select * from dbo.ManagedEntityStage. Tada a certain number of rows were returned but the select stopped with an arithmetic overflows (I forgot one of my golden rules, don’t trust GUIs).
So the issue is with one or more rows in ManagedEntityStage. Time to review the table schema:
As it turned out the table has a computed column (InsertReadyInd), the formula is as follows
So here they’re multiplying the ManagedEntityTypeRowID (i.e. the unique identifier inside the DW for object classes) with the host id of the managed entity. These two are integers. SQL as an implicit type conversion for computed columns, this type conversion takes the least restrictive type of the terms involved in the formula as the field type (http://msdn.microsoft.com/en-us/library/ms163363.aspx). In our case InsertReadyInd is an integer for SQL sake. But, int * int casted to an int can lead to an arithmetic overflow, and this was exactly my case. You must consider that in our dev Lab we constantly add and remove MPs, every time an MP is added the ManagedEntityTypeRowId increments, even if that MP has been added in the past (this won’t be the case if we just updated the MPs). So probably this is not a common scenario, btw the same issue is true for other staging tables. Nevertheless it cannot be excluded that this same issue could hit a huge production management group.
Obviously, since this is a lab environment, I didn’t open a PSS incident, but rather simply I fixed the computed field. ** do not do this in production if not directed by PSS **
So I changed the formula for InsertReadyInd to
(abs(isnull([ManagedEntityTypeRowId],(0))*CAST(isnull([TopLevelHostManagedEntityRowId],(0)) AS FLOAT)))
This did the magic and now my stale state data is flowing into the DW.
This posting is provided "AS IS" with no warranties, and confers no rights.