Wednesday, November 30, 2011

CRM 2011 Import Error - Option set name doesn't match

When you try to import a solution you might be seeing an error like this. we came across such an issue after installing roll up 1 to one of our CRM servers. There seems to be an issue with the Boolean field names as after installing the roll up then schema name has been changed. 
 
Luckily there was a sql script which has been given by Microsoft to a guy (John Rogerson) who has faced this issue. He was generous enough to post it in the web. After applying this we were able to import the solutions. but do this at your own risk. Thanks John for this script.
The script is as follows. Don’t have to change anything in this script. Only thing is we have to point it to the CRM database. Before applying this script it is strongly recommended to take both CRM and Config database backups.

-- This script is for all intents and purposes supposed to match the script ApplySchemaNamePrefixToOptionSets.sql

 

declare @DefaultPublisher uniqueidentifier = 'D21AAB71-79E7-11DD-8874-00188B01E34F'

declare @SchemaNamePrefixLowerCase nvarchar(9) -- the prefix is 8 characters plus 1 for the underscore

 

select @SchemaNamePrefixLowerCase = LOWER(CustomizationPrefix collate Latin1_General_CI_AI) + '_' from Publisher where PublisherId = @DefaultPublisher

-- The first thing we will do is change the naming convention of custom picklist and boolean option sets

-- on system entities.  This is to address bug 122849.  In order to be more assured that these option sets

-- are created with a consistent name no matter what organization the option set belongs to and the name

-- has a valid prefix it is easier to start these names with the attribute name rather than the entity name.

-- These names are also guaranteed to be unique since all attribute + entity name combinations must be unique.

-- NOTE:  Because we're dealing with custom option sets on system entities, we can eliminate the State and Status

-- attribute types because if a system entity has State and Status they will be OOB attributes and the names will

-- be set through the metadata XML, and customizers cannot add custom State or Status attributes to a system entity.

update o set o.Name = LOWER(a.LogicalName + '_' + e.LogicalName collate Latin1_General_CI_AI)

from MetadataSchema.OptionSet o

join AttributeView a on a.OptionSetId = o.OptionSetId

join EntityView e on a.EntityId = e.EntityId

where o.IsGlobal = 0

and o.IsCustomOptionSet = 1

and a.IsCustomField = 1

and a.AttributeTypeId in ('00000000-0000-0000-00AA-110000000030', '00000000-0000-0000-00aa-110000000013')

and e.IsCustomEntity = 0

 

-- Now see if after changing these names for custom picklist and boolean option sets on system entities if any

-- do not have a proper prefix - those will need to be corrected

declare @OptionSetId uniqueidentifier

declare @OptionSetNameOld nvarchar(123)

 

-- Retrieve list of Custom Local OptionSets that are used by Custom Attributes of System Entities and don't have Schema Name prefix set already.

-- The check for whether there is a prefix is based solely on whether we find an underscore someplace between characters 3 and 9, inclusive, as that

-- is the main component of what the ValidateSchemaNamePrefix code does in the MetadataService.  If it is a custom attribute with such a prefix

-- we can be confident the other checks (must start with a letter, must not equal "mscrm", etc) were checked when the attribute were created.

DECLARE optionset_cursor CURSOR LOCAL FAST_FORWARD FOR

               select o.OptionSetId, o.Name from OptionSetView o

               join AttributeView a on a.OptionSetId = o.OptionSetId

                join EntityView e on a.EntityId = e.EntityId

                where CHARINDEX(N'_', SUBSTRING(a.LogicalName, 3, 7)) = 0

                and o.IsGlobal = 0

                and o.IsCustomOptionSet = 1

                and a.IsCustomField = 1

                and a.AttributeTypeId in ('00000000-0000-0000-00AA-110000000030', '00000000-0000-0000-00aa-110000000013')

                and e.IsCustomEntity = 0

 

OPEN optionset_cursor

 

-- Loop through each of the OptionSets that must have its name changed

FETCH NEXT FROM optionset_cursor INTO @OptionSetId, @OptionSetNameOld

while (0=@@FETCH_STATUS)

begin

                declare @SuffixAsInt int = -1

                declare @SuffixAsNVarchar nvarchar(123)

 

                -- Make sure the length of the new name does not exceds 123 and it does have enough space for the prefix

                declare @OptionSetNameNewNoSuffix nvarchar(123) = @SchemaNamePrefixLowerCase + SUBSTRING(@OptionSetNameOld, 1, 123 - LEN(@SchemaNamePrefixLowerCase))

                declare @OptionSetNameNewNoSuffixLength int = LEN(@OptionSetNameNewNoSuffix)

                declare @OptionSetNameNew nvarchar(123) = @OptionSetNameNewNoSuffix

 

                -- Make sure there is no existing OptionSet that has this new name already.

                -- If the name exists already then append a numeric prefix to the new name until we get a unique new name

                while (exists (select * from OptionSetView where LOWER(@OptionSetNameNew collate Latin1_General_CI_AI) = LOWER(Name collate Latin1_General_CI_AI)))

                begin

                                set @SuffixAsInt = @SuffixAsInt + 1

                                set @SuffixAsNVarchar = CAST(@SuffixAsInt as nvarchar(123))

 

                                -- Make sure the length of the new name does not exceds 123 and it does have enough space for the suffix

                                set @OptionSetNameNew = SUBSTRING(@OptionSetNameNewNoSuffix, 1, 123 - LEN(@SuffixAsNVarchar)) + @SuffixAsNVarchar

                end

               

                -- Set the new name to the OptionSet

                update MetadataSchema.OptionSet set Name = @OptionSetNameNew where OptionSetId = @OptionSetId

               

                -- Move on to the next OptionSet that must have its name changed

                FETCH NEXT FROM optionset_cursor INTO @OptionSetId, @OptionSetNameOld

end

 

CLOSE optionset_cursor

DEALLOCATE optionset_cursor

 

 

Here are some links related to this.

http://social.microsoft.com/Forums/en/crmdevelopment/thread/7984ec10-0510-4406-a515-0f01077fcb67

6 comments:

  1. Does the script have to be run on the source or destination server?

    ReplyDelete
  2. It should be run on the CRM Database server.
    Does that answer your question?.

    ReplyDelete
  3. Thank you very much for this script.

    We already had rollup 6 installed on both environments.

    We ran it first on our detination (QA environment) without resolution, then we ran it on the source (DEV Server).

    Then the solution imported.

    It is now too late to know whether it would have worked if we ran it only on the source system.

    ReplyDelete
  4. Good to hear it brought you a resolution. I hope you have backed up your databases before applying the patch.

    ReplyDelete
  5. Thanks for the script...
    Had to run it on both production and development environments to resolve the issue. Both were updated to Rollup 8.

    ReplyDelete
  6. Great..
    Hope you have backed up the database, just in case.

    ReplyDelete

Retrieving Calendar of a Bookable Resource in Dynamics

There are occasions where we need to retrieve working days and working times of a resource in Dynamics grammatically. This is quite possible...