Comparison Projects Questions

• 
  Sorry to take this off topic, but i thought id let you know ive encountered the problem yoyu are having with system objects that somehow lost their IsMSShipped or System attribute, which leads to them being compared by redgate.
  
  You can fix this by using the sp_MS_marksystemobject stored procedure,m which will set the System and IsMSShipped bits to true.
  
  In my case i found that the offenders are the stored procs that get added to the database by MSSQL the first time someone goes to the Diagram tab, or creates a diagram. In the majority of cases, these things should be marked as system objects - i encountered one database in our organisation where this wasn't the case. I found a way to mark these things as system again, which solves the problem.
  
  Hopefully you have the exact same set of objects misbehaving as i did, and these commands should sort it out for you:
  
  
  exec sp_MS_marksystemobject 'dtproperties'
  exec sp_MS_marksystemobject 'dt_addtosourcecontrol'
  exec sp_MS_marksystemobject 'dt_addtosourcecontrol_u'
  exec sp_MS_marksystemobject 'dt_adduserobject'
  exec sp_MS_marksystemobject 'dt_adduserobject_vcs'
  exec sp_MS_marksystemobject 'dt_checkinobject'
  exec sp_MS_marksystemobject 'dt_checkinobject_u'
  exec sp_MS_marksystemobject 'dt_checkoutobject'
  exec sp_MS_marksystemobject 'dt_checkoutobject_u'
  exec sp_MS_marksystemobject 'dt_displayoaerror'
  exec sp_MS_marksystemobject 'dt_displayoaerror_u'
  exec sp_MS_marksystemobject 'dt_droppropertiesbyid'
  exec sp_MS_marksystemobject 'dt_dropuserobjectbyid'
  exec sp_MS_marksystemobject 'dt_generateansiname'
  exec sp_MS_marksystemobject 'dt_getobjwithprop'
  exec sp_MS_marksystemobject 'dt_getobjwithprop_u'
  exec sp_MS_marksystemobject 'dt_getpropertiesbyid'
  exec sp_MS_marksystemobject 'dt_getpropertiesbyid_u'
  exec sp_MS_marksystemobject 'dt_getpropertiesbyid_vcs'
  exec sp_MS_marksystemobject 'dt_getpropertiesbyid_vcs_u'
  exec sp_MS_marksystemobject 'dt_isundersourcecontrol'
  exec sp_MS_marksystemobject 'dt_isundersourcecontrol_u'
  exec sp_MS_marksystemobject 'dt_removefromsourcecontrol'
  exec sp_MS_marksystemobject 'dt_setpropertybyid'
  exec sp_MS_marksystemobject 'dt_setpropertybyid_u'
  exec sp_MS_marksystemobject 'dt_validateloginparams'
  exec sp_MS_marksystemobject 'dt_validateloginparams_u'
  exec sp_MS_marksystemobject 'dt_vcsenabled'
  exec sp_MS_marksystemobject 'dt_verstamp006'
  exec sp_MS_marksystemobject 'dt_verstamp007'
  exec sp_MS_marksystemobject 'dt_whocheckedout'
  exec sp_MS_marksystemobject 'dt_whocheckedout_u'

  rgribble March 02, 2006 19:36

  - 0 +
• 
  Thanks rgribble, I have been avoiding this issue as the server that has the problem is run by another department...
  
  I got excited about your reply and tried it straight away, but unfortunately don't have sufficient permissions on the server in question. But at least it's not that hard for me to give the DBA these commands and ask him to run them, if I can find a good way of explaining to him what the problem is.
  
  Thanks again!

  thomasswilliams March 02, 2006 22:02

  - 0 +
• 
  Yeah you're really getting deep into the undocumented bits of the 32bit status field of every object in the database
  
  essentially, calling the sp_MS_marksystemobject stored proc will do a bitwise OR of the existing status, with the value 0xC0000000 (which is 11000000000000000000000000000000)
  
  Essentially the 2 bits that it sets to on - one is "IsSystemObject" and the other is "IsMSShipped"
  
  The IsSystemObject is used to determine whether a table is system or user type (what you see in enterprise manager for example)
  
  But the IsMSShipped bit is what redgate appears to use to ignore some objects (like all the guff for dt_whocheckedout and so on, that get added to database when you go to the diagram tab etc).
  
  Basically i found that on my offending dtProperties table, the status was 1610621221 (or 01100000000000000010000100100101) and as you can see, the first bit has not been set to 1.
  
  After running marksystem object on it, it became -536862427 (or 11100000000000000010000100100101)
  
  Essentially you can see that

     01100000000000000010000100100101
  OR 11000000000000000000000000000000
  
  =  11100000000000000010000100100101
  

  It now has both bits set, and no longer shows up in redgate
  
  
  Essentially somehow in odd circumstances, these objects that get created by going to the design tab have gotten added without the IsMSShipped bit turned on
  
  
  If you want to know more about the status field and undocumented bits, this article was very helpful for me, but it seems now you have to pay to acess it whereas i dont recall that i had to pay back in april last year when i encountered this problem
  
  http://www.sqlmag.com/Articles/Index.cf ... leID=22920

  rgribble March 02, 2006 22:38

  - 0 +

Add comment

Please sign in to leave a comment.