Looking for PowerObjects? Don’t worry, you’re in the right place! We’ve been part of HCL for several years, and we’ve now taken the final step in our acquisition journey: moving our website to the HCL domain. Nothing else is changing – we are still fanatically focused on Microsoft Business Applications!

PowerObjects Blog 

for Microsoft Business Applications


Fixed: Cannot Add Records to Marketing Lists

Post Author: Joe D365 |

We have identified a fix for a problem that affects upgraded CRM 2016 on-prem environments when using marketing lists and today's blog will tell you all about it! Let's jump right in…

Problem

This issue occurs when you open a marketing list, and select "Add Using Advanced Find" or "Evaluate Using Advanced Find."

Let's say you select "Add Using Advanced Find," and then you do your search. After the list of records is returned, you then select the option to add all members returned by the search to the marketing list.

cannot add records to marketing lists

Then you click the "Add to Marketing List" button. Instead of adding your contacts, accounts, or leads to the marketing list, nothing happens, and CRM just spins forever. Eventually you may get an error, but the list is never updated with the members you are trying to add.

You may also find that this particular error only happens when you have auditing turned on.

Cause

This problem is caused by an issue with a stored procedure called p_InsertMultipleAuditRows on the CRM database that is invoked when the button above is clicked to add to the marketing list. When invoked, it uses a variable called @audituseradditionalinfo that it expects to be inserted into the AuditBase table to the UserAdditionalInfo column. However, it is failing to do so, and results in the following error in the SQL server:

"Procedure or function 'p_InsertMultipleAuditRows' expects parameter '@audituseradditionalinfo', which was not supplied."

This results in the CRM side, it never successfully completes the process of adding to the marketing list.

Important Note

We worked with Microsoft to provide a solution to our issue, and they supplied us with a fix which was to alter this specific stored procedure and comment out 3 lines of code, that refer to the column UserAdditionalInfo and the variable @audituseradditionalinfo. This fix will need to be done from someone with with full access to the CRM database, preferably a SQL admin.

WARNING

Making direct changes in SQL is deemed an unsupported change. That means that future updates and considerations done by the Microsoft product team will not take into consideration any unsupported change, and it should be done at your own risk. However, we have had success with the following query, and have not had a need to revert back any of our changes shown below. And as always, recommend doing a full database backup before doing a direct SQL update.

Fix

Did you read and understand our warning above? Good! Now in order to fix our problem, you will need to run the following query against the CRM database. Please copy it exactly as shown below, and run it, so we can correct the faulty stored procedure. It will complete in a second or less.

DROP PROCEDURE [dbo].[p_InsertMultipleAuditRows]
GO

/****** Object: StoredProcedure [dbo].[p_InsertMultipleAuditRows] ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[p_InsertMultipleAuditRows](@doc ntext, @transactionid uniqueidentifier, @EntityType int,
@auditoperation tinyint, @auditaction tinyint, @audituser uniqueidentifier ,
@callinguser uniqueidentifier, @attributemask nvarchar(max), @changedata nvarchar(max)
--,@audituseradditionalinfo nvarchar(350)
) as

BEGIN
DECLARE @idoc int
DECLARE @datetime DATETIME
set @datetime = dbo.fn_GetUtcDateTrunc()

CREATE TABLE #tempMemberBase(ObjectId UNIQUEIDENTIFIER)

EXEC dbo.sp_xml_preparedocument @idoc OUTPUT, @doc

SELECT @doc

--The format of the xml document @doc is like this
-- 
-- 
-- 
-- 
-- 

--Insert all the ObjectIds into the temp table for later use in inserting into AuditBase
INSERT INTO #tempMemberBase SELECT ObjectId FROM
OPENXML(@idoc, '/Values/Value',1)
WITH (ObjectId UNIQUEIDENTIFIER)

INSERT INTO AuditBase(TransactionId, ObjectTypeCode, Operation, Action,
UserId, CallingUserId, CreatedOn, ObjectId, AttributeMask, ChangeData
--,UserAdditionalInfo
)
SELECT @transactionid, @EntityType, @auditoperation, @auditaction,
@audituser, @callinguser, @datetime, ObjectId, @attributemask, @changedata
--,@audituseradditionalinfo
FROM
#tempMemberBase

DROP TABLE #tempMemberBase

END

GO

As soon as it completes, go back to Dynamics 365, and try the same steps, and you should now be able to successfully add to a marketing list.

Happy Dynamics 365'ing!

Joe CRM
By Joe D365
Joe D365 is a Microsoft Dynamics 365 superhero who runs on pure Dynamics adrenaline. As the face of PowerObjects, Joe D365’s mission is to reveal innovative ways to use Dynamics 365 and bring the application to more businesses and organizations around the world.

PowerObjects Recommends