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.
Object Type Code of the entity for which the attribute belongs.
Schema name of the picklist attribute.
Integer value of the picklist option. This is the value that gets stored in the base tables.
Language Code for the CRM deployment. Usually 1033 for English (United States)
GUID of the owning Organization.
The actual value that is displayed in the picklist on a form.
Specifies the order in which the value is in the picklist
Timestamp for determining the version of the record (when it was last updated). This is used by the synchronization process.
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: http://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 whoinserts and creates this GUIDs for StringMapID?
I collected SQL profiler and could see the insert queries being executed and resulted in failures.
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:-
CAUSE OF THIS ISSUE
This issue occurs when the following constraint in CONSTRAINTS section is missing
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.
-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 :-
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.