The other day I was working on setting up an AX 2012 Retail environment when I countered this error when setting up the Retail Store Connect instance:

Error happens when validating database My_Store_Database.

That’s great, what error happened? What’s wrong? Tell me and I’ll fix it for you!

This is another shining example of Microsoft Hiding the problem. In my dealings with Microsoft software / tools, I’ve found that they tend to like to hide the innards of what went wrong whenever they can.

There were no events in the Event Viewer, no log-files to speak of, no additional information just an error. Something went wrong, but we won’t tell you what.

Well after some digging through some ILSpy’ed code, I managed to figure out what it was trying to do.

Ultimately it was trying to execute this SQL against my database:

IF 
    EXISTS 
    (SELECT * FROM sys.tables WHERE name = 'IncomingMessages') 
    AND 
    EXISTS 
    (SELECT * FROM sys.tables WHERE name = 'OutgoingMessages') 

SELECT 1 
ELSE 
SELECT 0;

Code is shown in fair use for the purpose of criticism. Code in the above example is Copyright © Microsoft.

Basically, it is just looking for the existence of certain tables. And that’s cool, there’s even this great check in their code for that:

if ((int) sqlCommand.ExecuteScalar() == 0)
    throw new MessageDBManagerException(
        "This is not a valid Store Connect database.");

Code is shown in fair use for the purpose of criticism. Code in the above example is Copyright © Microsoft.

Great right? Wrong!. The problem is, some Microsoft programmer thought he was so smart, and he put a glorious try/catch around the entire method (presumably to catch any bad SQL errors or strange mishaps that go on).

The problem? This:

catch (Exception ex)
{
    throw new MessageDBManagerException(
        string.Format(
            "Error happens when validating database {0}.", 
            (object) dbName), ex);
}

Code is shown in fair use for the purpose of criticism. Code in the above example is Copyright © Microsoft.

A Blind Catch, catching all Exceptions and treating them the same. This is a valid approach, basically we want to gracefully exit if something unexpected happens. But what about the expected exception above?

The whole problem is they’re throwing a MessageDBManagerException inside the try then wrapping it with another.

This effectively hides the one well defined error they DID plan for behind the catch all for the infinite array they didn’t plan for.

The Fix

Unfortunately, this is a closed off piece of code in a proprietary system, and we cannot change this code. However, we can learn from it.

What they should do is explicitly catch the MessageDBManagerException and re-throw it so THAT exception gets passed along (since that’s the message it would seem they wanted to communicate to the user, “Hey your DB is wrong!”)

catch(MessageDBManagerException)
{
    throw; 
    // Re-throw this exception to the handler below
}

The Workaround

Ok, so this is the part most people hitting this page will be looking for. Ultimately the problem stems from your database missing required tables to be considered a Store Connect Message database.

If you’re like me, and tinkering with this stuff, it’s possible you used the Retail Database Utility to build yourself a database for your store, and now you’re trying to set up a Retail Store Connect session to link to that database.

Well, that’s not what it’s looking for! It’s looking for a Messaging database that it’ll use to store the synchronization state and intermediate rows. This is different from the actual store DB, so give it a unique name. If the database doesn’t exist, it’ll create it for you.

Hopefully this helps someone else out there, I know I wasted a couple hours to this one.