In this webinar, our experts showcase a variety of demo use cases of how different components of the...
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…
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.
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.
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.
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.
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!