How publishing of customization is related to CRM StringMapBase table.

StringMapBase table in CRM database is one of the most interesting table I recently came across when I faced an issue while publishing any kind of customization in my CRM environment. So, finally I decided to shed some light on this table and how we can fix one peculiar issue related this table . This is the table in CRM which stores the related values for picklists for all entities in CRM. Let's dig a little bit into the structure of the table and how you can query some useful info from it.  

I read about this table and found lot of queries that we can use to get useful information about picklists in CRM.  

Note: Modifying data in this table is not recommended and not supported by Microsoft Support.

 

Table Structure

 

Field

Data Type

Comments

ObjectTypeCode

INT

Object  Type Code of the entity for which the attribute belongs.

AttributeName

NVARCHAR(100)

Schema  name of the picklist attribute.

AttributeValue

INT

Integer  value of the picklist option. This is the value that gets stored in the base  tables.

LangId

INT

Language  Code for the CRM deployment. Usually 1033 for English  (United States)

OrganizationId

UNIQUEIDENTIFIER

GUID  of the owning Organization.

Value

NVARCHAR(255)

The  actual value that is displayed in the picklist on a form.

DisplayOrder

INT

Specifies  the order in which the value is in the picklist

VersionNumber

TIMESTAMP

Timestamp  for determining the version of the record (when it was last updated). This is  used by the synchronization process.

StringMapId

UNIQUEIDENTIFIER

Primary  Key (GUID) for the record.

 

 Following  just returns a list of each picklist value:

 

SELECT e.Name, a.AttributeName, a.AttributeValue, a.Value, a.DisplayOrder

FROM Stringmap a INNER JOIN MetadataSchema.Entity e ON a.ObjectTypeCode = e.ObjectTypeCode

ORDER BY a.ObjectTypeCode, a.AttributeName, a.AttributeValue 

 

I came across an issue where we were unable to publish any customization in CRM 4.0 environment. We tried doing "Publish All Customization"  or tried publish on one entity(be it any entity in CRM) every time we failed with following error in UI :-

 

  "An error had occurred."

 

 

 

  

If we collect platform trace we will see following error every time :-  

Stack Trace Info: [SqlException: Cannot insert the value NULL into column 'StringMapId', table 'ABC_MSCRM.dbo.StringMap';  

The statement has been terminated.]  

 

Complete Platform Trace Error:-  

Error: Exception has been thrown by the target of an invocation. 

Error Message: Exception has been thrown by the target of an invocation.

Source File: Not available

Line Number: Not available

Request URL: https://mtlqacrm/ABC/AppWebServices/SystemCustomization.asmx

Stack Trace Info: [SqlException: Cannot insert the value NULL into column 'StringMapId',table 'ABC_MSCRM.dbo.StringMap'; column does not allow nulls. INSERT fails.

The statement has been terminated.]

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) 

  at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) 

   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)

   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)

   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

   at Microsoft.Crm.CrmDbConnection.InternalExecuteNonQuery(IDbCommand command)    at Microsoft.Crm.CrmDbConnection.ExecuteNonQuery(IDbCommand command, Boolean impersonate)

   at Microsoft.Crm.CrmDbConnection.ExecuteNonQuery(IDbCommand command) at Microsoft.Crm.Tools.ImportExportPublish.StringMapPublisher.InsertNewStringMapValue(Int32 objectTypeCode, String attributeName, Int32 attributeValue, Int32 langId, String description, Int32 displayOrder)

   at Microsoft.Crm.Tools.ImportExportPublish.StringMapPublisher.UpdateMaps(EntityMetadata entity, Int32 langId) st Microsoft.Crm.Tools.ImportExportPublish.StringMapPublisher.PublishStringMaps()

   at Microsoft.Crm.Tools.ImportExportPublish.PublishXml.PublishAll(ExecutionContext context) at Microsoft.Crm.WebServices.PublishXmlService.PublishAll(ExecutionContext context)  

 

I wondered what is this StringMapId which was getting a NULL value and hence the failure. After I researched further, I found that it is a unique GUID value assigned to each picklist value in CRM. Then came the next question,why is it inserting a NULL value instead of a GUID and if it is a GUID who
inserts and creates this GUIDs for StringMapID?  

I collected SQL profiler and could see the insert queries being executed and resulted in failures.

 Example:-

INSERT INTO StringMap(ObjectTypeCode,AttributeName,AttributeValue,LangId,OrganizationId,Value,DisplayOrder) VALUES
(4414,'matchingentitytypecode',4414,1033,'17520689-b364-e211-891c-005056b412f9','DuplicateDetection Rule',1)

 ----------------------------------

 Finally, I realized that stringmapid column with stringmapids have GUIDs which are created at runtime with newid() function but were unable to understand why it is not able to generate and insert that and thus fails with the exception.

 

 Checked the table StringMapBase and then expanded the table and we could see following things:- 

  • Columns
  • Keys
  • Constraints
  • Triggers
  • Indexes
  • Statistics

 

 CAUSE OF THIS ISSUE  

This issue occurs when the following constraint in CONSTRAINTS section is missing      

    
DF_StringMap_StringMapId

 

  

 

REASON FOR THE INSERT QUERY FAILURE 

 

-This constraint is responsible to create GUID "StringMapId" in StringMapBase table. Thus if this is missing, NULL values would be passed while publishing any customization and hence it will error out every time.

 -This usually can happen when we follow unsupported ways to deleting or editing CRM database tables.   

 

RESOLUTION

 

 -We used option to create this missing constraint  with query ------------> right click CONSTRAINT in your vanilla org ------------> script as ------------> CREATE TO -----------> New query Window

 Ran following query in affected environment :-

USE [crm_MSCRM]

GO

ALTER TABLE [dbo].[StringMap] ADD  CONSTRAINT [DF_] DEFAULT (newid()) FOR [StringMapId]

GO 

Thus if we see above query it has a function NewID() for [StringMapId] which is responsible for creating runtime GUID while insert query runs on StringMap table which holds all picklist values.

-Hence it is must for us for all picklist values in stringmap table.

  

I hope this post helps.