How publishing of customization is related to CRM StringMapBase table.

Article
02/05/2013

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]

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

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.

Comments

Anonymous
February 05, 2013
Great !!! Keep it up.Thanks for sharing....
Anonymous
February 06, 2013
good information && thanks for sharing....

Share via

Comments

Additional resources