Microsoft Dynamics CRM (365) - SQL Query to List Members of a Marketing List*

*for on-premise installations only

Using the Advanced Find tool in Dynamics CRM to pull a quick report of Marketing List members such as Account Names is very simple. Well, have you ever had to do the same thing with a SQL query?...I just had to figure this out and it wasn't as straightforward as some other SQL queries I've written against the CRM database. I didn't find any quick answers upon doing my usual Google searches, so I thought I would post a quick guide on this myself in case others stumble upon this reporting requirement.

You need to know a few things first before you can build your SQL query:

• List member entity type (Accounts, Contacts or Leads)
• Identifiable information about your Marketing List (date/time created and created by user name worked for me)

The first thing you need to do is find the GUID of the Marketing list from the CRM database. To do this, you will query the FilteredListMember view using the identification info you collected above. The query should be structured like this:

SELECT listid -- listid GUID 
FROM FilteredListMember --marketing list table 
WHERE createdon = @dateCreated AND createdbyname = 'User Name'

Once you have the listid GUID for your Marketing List, you can now join to your entity type table to return the list of entity names (or any other data you need from the entity type table)

Perform a join to the entity table that your Marketing List is comprised of to return your list of names. The query should be structured like this:

SELECT account.name
 
FROM FilteredListMember marketingList

	INNER JOIN FilteredAccount account--join to account/contact/lead
	ON account.accountid = marketingList.entityid

WHERE listid = 'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX' --insert listid GUID

Run the above query and...BAM! You have your list of marketing list members by name via a SQL query.

Cheers!

-James Houck, developer/analyst/artist/passioneer