Management Reporter reports stuck in the queue.

Well, your Management Reporter reports are stuck and you can't run anything.  That is no fun.  What the hell is going on?

Don't worry (yet).  This is a common issue that we see with Management Reporter.  It will happen in any product that runs MR whether it is AX, GP, NAV, or similar. It can be due to many issues.  The key to this is determining what is causing it if it occurs on a very common basis.

Thankfully, there is an easy way to fix this: restart the MR services.  The tricky part is finding out why its happening since its not a reasonable expectation to have to deal with this when it goes down all the time.

Below is a good guide from Microsoft in debugging this issue.  I would STRONGLY suggest any IT staff dealing with MR read this guide since this issue will inevitably happen.

There are five potential causes for this error:

There are five potential causes for this error: 

Cause 1
If the Management Reporter Process Service is on the same machine as the machine hosting your ManagementReporter SQL database. The Process Service may have errored out by attempting to start before SQL Server was accepting connections. See Resolution 1 in the Resolution section. 

Cause 2
A SQL Server connection error has occurred and the Management Reporter Process Service needs to be restarted. See Resolution 2 in the Resolution section. 

Cause 3
The user running the Management Reporter Process Service does not have sufficient permissions to read from the ManagementReporter SQL Server database. See Resolution 3 in the Resolution section. 

Cause 4
The SQL Service Broker on the ManagementReporter SQL Server database is not enabled. See Resolution 4 in the Resolution section. 

Cause 5
This can happen if the owner of the Management Reporter database is a Windows User while the SQL Server Service is being run by a local user. If you check the Event Viewer, you may see this message:

An exception occurred while enqueueing a message in the target queue. Error 15404, State 19. Could not obtain information about Windows NT group/user 'domain\user', error code 0x5.

See Resolution 5 in the Resolution section.

Resolutions

Resolution 1
If using Windows Server 2008 you can set the Management Reporter Process Service to Automatic (Delayed Start) rather than Automatic.
OR
Restart Process Service manually or with a script similar to the following:

NET STOP MRProcessService
NET START MRProcessService

Resolution 2
Restart Process Service manually or with a script similar to the following:

NET STOP MRProcessService
NET START MRProcessService

Resolution 3
Grant the user running this service the GeneralUser role under the Management Reporter database in SQL Server. This user can be found on the Log On tab under the Services Control panel.

Resolution 4
Run the following statement on the SQL server where your ManagementReporter database resides:

SELECT name, is_broker_enabled FROM sys.databases WHERE name = DB_NAME() AND is_broker_enabled = 1

This statement should return a row for the ManagementReporter SQL Server database. If it does not, run the statement below to re-enable the SQL Service Broker on the ManagementReporter SQL Server database:

ALTER DATABASE [ManagementReporter] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

Resolution5
Change the database owner to sa or change the SQL Server Service user to a domain user.

How to find the SQL Server version from within AX 2009

I am usually asked how to find various information around SQL Server from within AX. This can happen from either a fellow consultant or a client.

Usually from a consultant standpoint, it is in the scenario where they are thrown into a situation where they don’t really know anything about the client and it is one more thing for them to ask people. Another scenario would be when a consultant or client need to submit a ticket to a company for support and the company needs to know some information around SQL Server.

Whatever the case may be, people will need to be able to get information about SQL from AX and those people may not be technologically inclined. AX has a way to do this :-)

To find out information about SQL, navigate to Administration -> Inquiries -> Database -> ‘Database information’ (Figure 1).

Figure 1 - Database Information form location

In figure 2 below it, shows the Database Information form that will pop up to the user. There are three tabs on this form: ‘General’, ‘System variables’, and ‘ODBC’. There is a whole lot of information on these tabs but I doubt a lot of it would apply to most people. The primary information people will want to see:

• First tab (Figure 2)
• Logon database
• The database the current AX environment is using
• Logon server
• The server where the database is located
• Database
• The type of database AX is running against (SQL or Oracle)
• Third tab (Figure 3)
• DBMS_VER
• The version of SQL Server that AX is running (please refer to the next paragraph for translating this number to something that means something)

Feel free to add to the various fields you find helpful in the comments below. I don’t want to detail them all…

When assessing what SQL Server version your current AX environment is on, look at the third tab’s DBMS_VER variable described above. You can take this number and compare it to numbers you can find online. For example, if the database is SQL Server and the DBMS_VER is 10.50.1600, try entering ‘SQL Server 10.50.16000’ in a search engine (Bing!) and you should see the server version and Service Pack version.

From my example above, using the list of versions I’ve included below, indicate that my current AX environment is running SQL Server 2008 RTM.

SQL Server 2000 version numbers:
-RTM - 8.0.194
-SP1 - 8.0.384
-SP2 - 8.0.534
-SP3 - 8.0.760.0
-SP3a - 8.0.760
-SP4 - 8.0.2039

SQL Server 2005 version number examples:
-RTM - 9.0.1399
-SP1 - 9.0.2047
-SP2 - 9.0.3042
-SP3 - 9.0.4035

SQL Server 2008 version number examples:
-RTM - 10.0.1600
-SP1 - 10.0.2531

Figure 2 – Tab one of the Database Information form

Figure 3 – Tab three of the Database Information form

Hope this helps!