0

I'm working with a company that has a lovely little Marketing Cloud CloudPage set up at https://cloudpages.example.com/preferencecenter?SenderKey=XYZZY&EmailAddress={{Recipient.Email}} to serve as an "unsubscribe / resubscribe" link.

The unsubscribe button works well on the backend when using this link -- [email protected] gets added into appropriate Salesforce tables that serve as the "subscription preferences source of truth" in a way that's joined to the concept of sender "XYZZY" without issue.

However, the "Thanks for unsubscribing" HTML rendered after the unsubscribe-button link gets clicked has this sort of error showing to the end user in their web browser at the top of the page (it seems to be just a bunch of unformatted text right inside <body> before the first <meta>):

{"message":"The function expression is invalid. See inner exception for detail.\r\n Script: InsertData("ENT.SubscriptionCenter_Log",\r\n "EmailAddress", @EmailAddress,\r\n "Sender", @SenderId,\r\n "CommunicationPreferenceId", @CommunicationPreferenceId,\r\n "SubscriberKey", @SubscriberKey,\r\n "OptedOut", @OptedOutOfSender,\r\n "Language", @Language,\r\n "EmailName", @EmailName,\r\n "MID", @MID,\r\n "JobId", @JobId,\r\n "ListId", @ListId,\r\n "BatchId", @BatchId,\r\n "DateSubmitted", @DateSubmitted,\r\n "BusinessUnit", @BusinessUnitId\r\n )\r\n Index: INDEX_NUMBER_CENSORED\r\n ListID: 0\r\n","description":"ExactTarget.OMM.InvalidScriptException: The function expression is invalid. See inner exception for detail.\r\n Script: InsertData("ENT.SubscriptionCenter_Log",\r\n "EmailAddress", @EmailAddress,\r\n "Sender", @SenderId,\r\n "CommunicationPreferenceId", @CommunicationPreferenceId,\r\n "SubscriberKey", @SubscriberKey,\r\n "OptedOut", @OptedOutOfSender,\r\n "Language", @Language,\r\n "EmailName", @EmailName,\r\n "MID", @MID,\r\n "JobId", @JobId,\r\n "ListId", @ListId,\r\n "BatchId", @BatchId,\r\n "DateSubmitted", @DateSubmitted,\r\n "BusinessUnit", @BusinessUnitId\r\n )\r\n Index: INDEX_NUMBER_CENSORED\r\n ListID: 0\r\n Error Code: OMM_FUNC_EXPR_INVALID\r\n - from Jint --> \r\n\r\n --- inner exception 1---\r\n\r\nExactTarget.OMM.FunctionExecutionException: **An error occurred when attempting to execute an InsertData function call. See inner exception for details.**\r\n Error Code: OMM_FUNC_EXEC_ERROR\r\n - from OMMCommon --> \r\n\r\n --- inner exception 2---\r\n\r\nExactTarget.OMM.OMMException: An error occurred when attempting to execute an update of the database. See inner exception for details.\r\n\r\n StackID: 7\r\n EnterpriseID: ID_NUMBER_FOR_ENTERPRISE_CENSORED\r\n ClientID: ID_NUMBER_FOR_ENTERPRISE_CENSORED\r\n Client Database ID: 718\r\n Account Type: ENTERPRISE_2\r\n JobID: 0\r\n MachineName: MACHINE_NAME_CENSORED\r\n\r\nSql Text:\r\nINSERT INTO [CID_NUMBER_FOR_ENTERPRISE_CENSORED].[SubscriptionCenter_Log] ([EmailAddress],[Sender],[CommunicationPreferenceId],[SubscriberKey],[OptedOut],[Language],[EmailName],[MID],[JobId],[ListId],[BatchId],[DateSubmitted],[BusinessUnit])\r\n VALUES (@p0_0,@p0_1,@p0_2,@p0_3,@p0_4,@p0_5,@p0_6,@p0_7,@p0_8,@p0_9,@p0_10,@p0_11,@p0_12);\r\n\r\nSql Parameters:\r\n 0: [NVarChar] @p0_0 = EMAIL_ADDRESS_ATTEMPTED_TO_UNSUBSCRIBE\r\n 1: [NVarChar] @p0_1 = SALESFORCE_18_DIGIT_ID_FOR_PREF_CENTER_SENDER_CUSTOM_OBJECT_RECORD_MATCHING_XYZZY_THAT_IS_PART_OF_BUSINESS_UNIT_ABCCA\r\n 2: [NVarChar] @p0_2 = SALESFORCE_18_DIGIT_ID_FOR_PREF_CENTER_PREFERENCE_CUSTOM_OBJECT_RECORD_MATCHING_EMAIL_ADDRESS_UNSUBSCRIBING_FROM_XYZZY\r\n 3: [NVarChar] @p0_3 = \r\n 4: [Bit] @p0_4 = True\r\n 5: [NVarChar] @p0_5 = en_US\r\n 6: [NVarChar] @p0_6 = \r\n 7: [NVarChar] @p0_7 = UNSURE_WHAT_NUMBER_THIS_WAS_BUT_CENSORED\r\n 8: [Int] @p0_8 = NULL\r\n 9: [Int] @p0_9 = NULL\r\n 10: [Int] @p0_10 = NULL\r\n 11: [DateTime] @p0_11 = 9/14/2022 3:15:29 AM\r\n 12: [NVarChar] @p0_12 = SALESFORCE_18_DIGIT_ID_FOR_MARKETING_CLOUD_BUSINESS_UNIT_ABCCA\r\n\r\n Error Code: OMM_SQL_UPDATE_ERR\r\n - from OMMCommon --> \r\n\r\n --- inner exception 3---\r\n\r\nSystem.Data.SqlClient.SqlException: \r\n\r\nError[0]: -- Number: 515 Class: 16 State: 2 Source: .Net SqlClient Data Provider Server: SERVER_NAME_CENSORED.XT.LoCaL\I05,10001 Line: 1\r\n Message: Cannot insert the value NULL into column 'JobId', table 'ExactTarget718.CID_NUMBER_FOR_ENTERPRISE_CENSORED.SubscriptionCenter_Log'; column does not allow nulls. INSERT fails. \r\n\r\nError[1]: -- Number: 3621 Class: 0 State: 0 Source: .Net SqlClient Data Provider Server: SERVER_NAME_CENSORED.XT.LoCaL\I05,10001 Line: 1\r\n Message: The statement has been terminated.\r\n\r\n HelpLink.ProdName: Microsoft SQL Server\r\n HelpLink.ProdVer: 13.00.6300\r\n HelpLink.EvtSrc: MSSQLServer\r\n HelpLink.EvtID: 515\r\n HelpLink.BaseHelpUrl: http://go.microsoft.com/fwlink\r\n HelpLink.LinkId: LINK_ID_NUMBER_CENSORED\r\n - from .Net SqlClient Data Provider\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n"}

Normally, such things aren't noticeable, because when e-mails go out from Marketing Cloud, the link to the Cloud Page is a fully-qualified https://cloudpages.example.com/preferencecenter?SenderKey=ABCDEFG&[email protected]&JobID=98765&ListID=2323232&BatchID=6789&EmailName=Some_Email_Name_String.

However, the intent of only specifying SenderKey and EmailAddress in the URL is to allow other systems, like Pardot, to also have people unsubscribe through the company's centralized subscription preference center. In that case, there wouldn't be such Marketing-Cloud-specific details available to put into the URL, and they're really not necessary to unsubscribe someone, anyway.

I'll be meeting with the Marketing Cloud administrator for this company next week, but they didn't write this Cloud Page (they inherited it), and I haven't worked with this setup since it was hosted in Cloud Pages for an older Marketing Cloud instances that has since been decommissioned and also was built/retired by someone else.

Seeing as the page works fine, but is simply being "ugly" about logging inside of Marketing Cloud not going well (again, the real DML that needs to happen in Salesforce is doing just fine), what are some things I might have us look into as we try to get the error message to go away?

I'm open both to actually fixing the Cloud Page and to tricking it (e.g. maybe we just need to make a big old "ignore-these" job in Marketing Cloud so it has a job number? I don't know how to figure out what the minimum data entry standards would be to make the logging process happy).

Also, I should mention that a URL with just SenderKey and EmailAddress parameters worked fine in the Cloud Page hosted in the old Marketing Cloud instance. I don't know enough about Marketing Cloud to guess whether this one being "pickier" about its parameters has to do with something that changed under the covers in Marketing Cloud as a product, or if whoever reimplemented the Cloud Page in our new Marketing Cloud instance perhaps got a little more thorough with logging than the old Cloud Page had been.

Improve this question

1 Answer 1

Reset to default
3

The error you are seeing means that an insert to a Data Extension in Marketing Cloud is failing. The data extension is called “SubscriptionCenter_Log” and the script seems to be failing to insert a null JobId into one of the columns. If you have a developer at hand, you can look into the reason why the script is not correctly passing the JobId values to the Data Extension (we won’t be able to help you with that without actually seeing the script and how the JobId variable is set). One of the correct ways to set the JobId in AMPscript, given it’s passed in the URL, would be:

set @JobId = QueryParameter("JobID")

If there’s no developer to help you, you could change the settings of the “SubscriptionCenter_Log” Data Extension and make the JobId field nullable, but of course it would be much better to fix the problem at the source.

As to how to stop displaying this or any other error messages - I would assume the script on the CloudPage is wrapped on a try/catch SSJS statement and currently it writes the error message in case an error is encountered. This is actually a good thing, because if it weren’t there, you’d be seeing a 500 server error message and nothing else on that page would be working. So to make it more user friendly, instead of getting rid of the try/catch from the CloudPage, I would suggest to make a small change to what it’s displaying and instead of displaying the full error message, I’d put a hardcoded message for an end used that says something more friendly, like “We have encountered an error”.

Here’s how to find the try/catch statement in your script:

<script runat="server">
Platform.Load("Core","1.1.1");
try{
</script>

%%[ your AMPscript block goes here  ]%%

<script runat="server">
}catch(e){
 Write(Stringify(e));
}
</script>

And to change it to a more user-friendly message, change the content of the Write function like below:

<script runat="server">
Platform.Load("Core","1.1.1");
try{
</script>

%%[ your AMPscript block goes here  ]%%

<script runat="server">
}catch(e){
 Write("Something went wrong");
}
</script>
Improve this answer
2
  • (Insert "I understood that reference" Captain America meme -- thank you, Salesforce, for handing out packtpub.com/product/automating-salesforce-marketing-cloud/… like candy.) Helpful! You explaining that "JobID" is a column on a Data Extension that we can control the nullability of makes me suspect that this is a self-foot-shoot, where old-feature-breaking "logging thoroughness" was added to the new Cloud Page and only caught now. (Rather than something having changed over time in Marketing Cloud at large.) Good to know, and thanks for the thorough response!
    – k..
    Sep 19, 2022 at 19:13
  • 1
    Since I'm not of the opinion that anything has gone "wrong" from a UX perspective (data goes to the real prefctr), if all else fails, maybe we can even throw an "if" statement into that "catch(e)" block and simply no-op if "e" is complaining about JobID and such. (The company can't afford to NOT let the external system use this preference center w/ non-MC email tools, so that means 1) fake non-nullable column data, 2) don't write external uses to the log and let them error because of null columns but hide the error from users, 3) external uses write to the log with null data in some columns.)
    – k..
    Sep 19, 2022 at 19:22

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .