Sometimes various SQLServer functions used in CMSi that merge geometries can result in GeometryCollection problems. Functions that create a site boundary from linked parcels or compartments, features from constituent compartments etc can result in this and then CMSi can fall over if trying to go to the map and that geometry is a Geometry Collection and not a polygon, line or point. SQLServer can handle such mixtures of geometry types but the CMSi Mapper, ArcGIS or QGIS cannot - they must have the geometries split into separate polygon, line or point layers. This can happen when unioning and polygons touch but don't overlap and a line object is created for that "line" instead of a polygon.
The first step is to find if you have any geometry collections and the script "GeometryCollections_NoObsVeg.sql" below will do this.
If this script produces any results, you have a geometry collection that must be fixed. The results will show which layer it is from but also the guid of each record. The next fixes work on record by record.
The next script to use is GeometryCollections_ExtractPolygons (as it is usually polygons you want without any lines or polygons for the above reason). There are THREE things you must check and edit if necesssary in the script before running - the Source table name (in the attached script, it says 'SharedGeom'), the geometry table being used e.g. SitesGeom or SharedGeom and the guid of the record you are fixing.
The script then explodes the geometry collection out into its constituent polygons and removes any line or points in the collection. It puts the results into a table called 'GeomCollection'.
The next step is to union the polygons in this temporary table and use the result to update the original record. Here it is CRITICAL to use the correct script in the '03' series e.g. use 03a for SharedGeom, 03b fo rSitesGeom etc.
If you have a number of fixes in one table, you can keep all three scripts open, copy the record guid to fix from script 1, paste it into script 2 and re-run script 3.