Tuesday, July 23, 2019

Broken Sitecore Analytics: Missing SQL Stored procs for the Marketing Automation DB on XP 9.0.2 Azure

So while troubleshooting some XDB related issues on a  9.0.2 Azure IAAS deployment, I was looking at the logs for the Marketing automation continuous job (the one that runs as a windows service) and I found this error that I'd never seen before: (truncated)

[Error] An error occurred while communicating with the SQL Server
System.Data.SqlClient.SqlException (0x80131904): Arithmetic overflow error for data type smallint, value = 32768.
Arithmetic overflow error for data type smallint, value = 32768.
The statement has been terminated.
   at System.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__180_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.Tasks.Task.Execute()
--- End of stack trace from previous location where exception was thrown ---

On first look It appears that the cause of this could be some corrupted entries in the automation pool table of the marketing automation database.

One (not very attractive or advisable IMHO) approach would be to try removing the non-processed entries from the db doing by something like this:

DELETE FROM [xdb_ma_pool].[AutomationPool]
WHERE Attempts LIKE '32767' 

Hacking stuff out of the database kinda creeps me out though and I tend to consider stuff like this as a last course of action.
The more attractive possibility is that these non-processed records might be related to the following KB article (specific to 9.0 update 1 and 2): https://kb.sitecore.net/articles/065636

From the KB:
"In Sitecore XP 9.0.2, several of the new stored procedures are not present in the DACPAC for Azure for the Marketing Automation database." 

More coming on this issue soon...


  1. Hi Colin,

    Did adding the stored procedures and table fix the "Arithmetic overflow error"?

  2. Mr. Gamble: How To Make The Most Popular Casino - DRM
    Mr. 천안 출장샵 Gamble, the casino game maker, made a name for 김천 출장마사지 himself in the gambling 동해 출장샵 business and for his 전주 출장안마 ability to bet on casino 익산 출장마사지 games.