Marketing Automation stored procedures and tables missing

Seen this message in the exception logs of your shiny new Azure app service instance of Sitecore 9?

Could not find stored procedure ‘xdb_ma_pool.AutomationPool_Stall’

ARRRRRGH.  Missing Stored Procedures?!?! Who’s been dropping stuff in my DB!  It’s newly deployed…how can this be! It worked on my dev machine! Never fear, there’s an explanation and easy fix.

No…you haven’t been dropping DB objects in your sleep.  This just appears to be an oversight in the Marketing Automation SQL dacpac in the Sitecore 9 Azure App Service web deploy packages.  In fact there are a few other objects missing too:

Stored Procedures
AutomationPool_Stall
ContactWorkerAffinity_ReleaseLease
ContactWorkerAffinity_TakeLease

Table
StalledAutomationPool

This has been confirmed by Sitecore support and they’ve published a KB  https://kb.sitecore.net/articles/065636 . Also the KB now contains a SQL script to create the missing objects.  Running this script on the Marketing Automation DB in your Azure environment should create the missing objects and resolve the issue.   This is confirmed as an issue on 9.0.1 and 9.0.2.

We’ll be adding this as a post-install script to all our ARM template deployments to avoid any manual steps in future deployments.

 

Advertisements