Because we all know that maintaining two on-premise environments (e.g. dev & production) can be a pain, sometimes checking certain entities before solution import can be a time saver!
To check the data type differences between the attributes of the same entity, on two different organizations, you can use the following SQL statement:
-- USAGE: -- EXEC CheckDiffs 'SourceOrganization_MSCRM', 'LinkedServerPath.TargetOrganization_MSCRM', 'account' -- EXEC CheckDiffs 'SourceOrganization_MSCRM', 'LinkedServerPath.TargetOrganization_MSCRM' -- Important: The @TargetDatabaseName parameter must be passed including the LinkedServer path as prefix (if the target database is on a different SQL Server than the source database) -- Last parameter is optional (you can check attribute type differences on a certain entity, or the whole database) CREATE PROCEDURE CheckDiffs @SourceDatabaseName nvarchar(100), @TargetDatabaseName nvarchar(100), @EntityName nvarchar(100) = NULL AS BEGIN DECLARE @SQL nvarchar(MAX) SET @SQL = 'SELECT srcE.Name Entity, srcA.Name Attribute, srcT.Description SourceType, trgT.Description TargetType FROM ' + @SourceDatabaseName + '.MetadataSchema.Attribute srcA JOIN ' + @TargetDatabaseName + '.MetadataSchema.Attribute trgA on srcA.Name = trgA.Name JOIN ' + @SourceDatabaseName + '.MetadataSchema.Entity srcE ON srcA.EntityId = srcE.EntityId JOIN ' + @TargetDatabaseName + '.MetadataSchema.Entity trgE ON trgA.EntityId = trgE.EntityId JOIN ' + @SourceDatabaseName + '.MetadataSchema.AttributeTypes srcT ON srcA.AttributeTypeId = srcT.AttributeTypeId JOIN ' + @TargetDatabaseName + '.MetadataSchema.AttributeTypes trgT ON trgA.AttributeTypeId = trgT.AttributeTypeId WHERE srcE.Name = trgE.Name and srcA.AttributeTypeId <> trgA.AttributeTypeId' IF @EntityName IS NOT NULL BEGIN SET @SQL = @SQL + ' and srcE.Name = ''' + @EntityName + '''' END --print @sql EXEC(@SQL) END |
By running the second example of the CheckDiffs procedure, the result should be:
Entity | Attribute | SourceType | TargetType |
account | new_field1 | nvarchar | decimal |
account | new_field2 | decimal | int |
contact | new_field1 | nvarchar | int |