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...