usp_ag_showdup

Show duplicate references (or store them into the #Duplicates temporary table, which then needs to be created by the caller).

Parameters

Parameter Type Default Value Description
@EntityName NVARCHAR(160)) N/A Base Entity (i.e. 'Contact' or 'Account' or similar
@PurgeResults BIT 1 Whether to invoke or not usp_ag_showdup_purge
@CreateTempTable BIT 1 If this parameter is 0, the procedure will not create the table #Duplicates but assumes it is created by the caller

Return

If this procedure is called with @CreateTempTable = 0 there is no return value. The caller should look at the #Duplicates table to list the results. Otherwise the result will be a dataset in the format

	SELECT
		Id,
		RuleId,
		BaseEntityTypeCode,
		BaseObjectId,
		MatchingEntityTypeCode,
		MatchingObjectId,
		Matchcode
	FROM #Duplicates

The columns are described in the table below:

Column Type Description
Id INT Row Key
RuleId UNIQUEIDENTIFIER Deduplication Rule: Foreign Key to DuplicateRule.DuplicateRuleId
BaseEntityTypeCode INT ObjectTypeCode of the base entity (1 for Account, 2 for Contact, etc.)
BaseObjectId UNIQUEIDENTIFIER Base Object: Foreign Key to Contact.ContactId, or Account.AccountId, etc. - The actual entity depends on BaseEntityTypeCode
MatchingEntityTypeCode INT ObjectTypeCode of the matching entity (1 for Account, 2 for Contact, etc.)
MatchingObjectId UNIQUEIDENTIFIER Matching Object: Foreign Key to Contact.ContactId, or Account.AccountId, etc. - The actual entity depends on MatchingEntityTypeCode
Matchcode NVARCHAR(450) Matchcode used for matching. According to the rule RuleId, BaseObjectId and MatchingObjectId yield the same Matchcode: this.


BaseEntityTypeCode and MatchingEntityTypeCode are the same if you are matching an entity against itself, but they can differe if you have rules that match cross-entity, for example when you have a deduplication rule for Contacts that checks them against Accounts.

Notes

  • Because this procedure accesses low level objects, it must be executed by a system administrator. A normal CRM user will not be able to execute it.
  • If this procedure is called with @CreateTempTable = 0, the caller needs to create the #Duplicates temporary table beforehand with a statement similar to the following:

	CREATE TABLE #Duplicates (
		Id INTEGER IDENTITY(1,1) NOT NULL,	-- Primary Key
		RuleId UNIQUEIDENTIFIER,			-- Rule Id (FK to DuplicateRule)
		BaseEntityTypeCode INTEGER,			-- ObjectTypeCode of the base entity
		BaseObjectId UNIQUEIDENTIFIER,		-- Base Entity Id (FK to the base entity table)
		MatchingEntityTypeCode INTEGER,		-- ObjectTypeCode of the matching entity
		MatchingObjectId UNIQUEIDENTIFIER,	-- Matching Entity Id (FK to the matching entity table)
		Matchcode NVARCHAR(450),			-- Matchcode that was used for duplicate detection
	)

In the above, "base entity" refers to the entity for which duplicates are detected/processed, while "matching entity" is the duplicate. Usually this is the same entity as the base entity, but in CRM 2011 one can deduplicate across different entities, for example contacts againts accounts.

Last edited Jul 31, 2011 at 5:46 PM by gemina, version 3

Comments

No comments yet.