Where does Microsoft Dynamics CRM store OptionSet values in SQL Server?

Question

I'm doing a data migration in to Microsoft Dynamics CRM 2011 and need to perform reconciliations against the source to ensure that everything loaded successfully.

To do this I am querying the SQL directly in SQL Server, but I can't seem to find where the OptionSet data is stored. Does anyone know what table(s) it's stored in?

Are you looking for the selected value or the textual representation? — ccellar, Sep 07 '11 at 14:31

score 31 · Accepted Answer · answered Sep 07 '11 at 13:32

31

These are all stored in the StringMapBase table. You'll query via object type code of the entity, attribute name, option set value and language and that'll give you the display value of the attribute.

answered Sep 07 '11 at 13:32

Matt

4,656
1
22
32

Found the data in StringMapBase, but customizations needs to be published before it gets there. – Commander92 Sep 08 '11 at 04:22
1

Nice as I don't have to do any joins. – Nigiri Jul 25 '13 at 04:21
See the answer by sarjanwebdev about using the FilteredStringMap view below to avoid breaking changes by Microsoft. – TheEmirOfGroofunkistan Dec 02 '14 at 21:16
2

`SELECT AttributeValue, Value FROM ORGNAME_MSCRM.dbo.StringMap WHERE AttributeName = 'CUSTOM_COLUMN_NAME'` Replace ORGNAME_MSCRM, and CUSTOM_COLUMN_NAME. – Ivan Chau Feb 18 '16 at 17:09

score 12 · Answer 2 · answered Jan 19 '12 at 05:55

12

Just a reminder! Use FilteredStringMap to continue to be 'supported' by Microsoft!

answered Jan 19 '12 at 05:55

SarjanWebDev

523
3
11

Has the FilteredStringMap table been replaced in newer versions? I am trying to retrieve perform a similar task in Dynamics Nav 2018 – Lindsay Dec 17 '19 at 05:54

score 7 · Answer 3 · answered Feb 19 '13 at 15:54

7

Here is an SQL Server function to query the stringmap

CREATE FUNCTION fn_new_GetStringMapValue 
(
    @AttributeName nvarchar(100),
    @AttributeValue int
)
RETURNS nvarchar(4000)
AS
BEGIN
    DECLARE @Result nvarchar(4000)
    SELECT @Result = Value
    FROM dbo.FilteredStringMap
    WHERE AttributeName = @AttributeName AND AttributeValue = @AttributeValue

    RETURN @Result
END
GO

answered Feb 19 '13 at 15:54

Sandor

1,839
2
18
22

4

Modification of SQL database to add such functions is unsupported. If you are hosting this function that reads the FilteredStringMap from another database then it's supported. In the later case, you would use [ORGNAME_MSCRM].[dbo].[FilteredStringMap] – SarjanWebDev Apr 26 '13 at 06:12

Where does Microsoft Dynamics CRM store OptionSet values in SQL Server?

3 Answers3

Linked