Changes to Object IDs in 9.2

While testing my 9.2.1 upgrade, I encountered quite a surprise.  It seems that in their continued effort to increase the performance of the metadata, MicroStrategy changed the way they store Object IDs, at least in SQL Server 2008.  Instead of storing the values as varchar(32), they are now stored as UniqueIdentifier.  Before you panic about your Reports and Documents with hardcoded Object ID links, they seem to still look the same in Desktop/Web, but if you view the metadata tables directly, they’ve changed format.  This causes issues for several of the scripts I’ve posted on this site, and I’ll go back and link to them to this post.  Fortunately though, I’ve managed to create a function that can be used to patch the queries back to working.  I haven’t fully tested it yet, but with a few spot checks, it looks like it’s working.

I only have SQL Server 2008 to test with, but if you notice any differences or want to provide translations for other Metadatas, I’ll post them here with your credit.  Just drop them in the comments!

Just create this function:

create function dbo.mstrobj(@uuid varchar(32))
returns uniqueidentifier
as begin
return cast(
substring(@uuid, 1,8) + '-' +
substring(@uuid, 13,4) + '-' +
substring(@uuid, 9,4) + '-' +
substring(@uuid, 23,2) +  substring(@uuid, 21,2) + '-' +
substring(@uuid, 19,2) + substring(@uuid, 17,2) + substring(@uuid, 31,2) + substring(@uuid, 29,2) + substring(@uuid, 27,2) + substring(@uuid, 25,2)
as uniqueidentifier)

And then you can modify any references to the Object ID by just wrapping this function around it.  Be sure that you include the dbo. or equivalent part in the call:

select * from DSSMDOBJINFO where PROJECT_ID = dbo.mstrobj('0114E056402ECFF8D895DAB005972880')

Before: 0114E056402ECFF8D895DAB005972880
After: 0114E056-CFF8-402E-B0DA-95D880289705

You may also like...