A few times we have seen case where changes to NAV objects are not seen in RTC until the NAV Server is restarted. This post explains a likely solution to such problems. And as we are on the topic anyway, also explains a bit about the process that turns C/AL code into Metadata (c#).
How object changes get to RTC
When you save an object in Object Designer, then table 2000000071 “Object Metadata” is updated as well. An easy way to see this part of the process in action is:
Run table 2000000071 and delete Page 1 (or any object). Then compile Page 1 from Object Designer. Then check that it has been re-created in table 2000000071.
If you look at this table in SQL Server Management Studio, you can see that it has SQL triggers which update the table “Object Tracking”. It is the “Object Tracking” table that NAV Server uses to see when an object has changed, so that it knows to send updated Metadata to RTC.
There are two ways that the NAV Server can get updated on activity in the “Object Tracking” table:
SQL Server Broker
Polling
SQL Broker
If SQL Server Broker is enabled, then NAV Server will rely on that to notify it when an object has changed. You can see whether the broker is enabled from SQL Server Management Studio under Database Properties, then under Options look for “Broker Enabled”.
If the broker is enabled, then when NAV Server starts up and on first activity (When first RTC connects), it will create a queue and a service under SQL Service broker. You can see this in SQL Server Management Studio under the NAV Database if you expand Service Broker. Here, under Queues and under Services you will see new objects with names like SqlQueryNotificationService-27b7fb21-74a7-4a63-876a-c96b8eecd583. These are created by NAV Server and removed again when NAV Server stops. Their job is to listen to the “Object Tracking” table and notify NAV Server when there are any changes there.
Polling
If the broker is not enabled, then NAV Server will use polling, i.e. check for changes every now and then. You can see this in two ways. First in the Application Log the NAV Server will log this event shortly after startup:
Service: MicrosoftDynamicsNavServer
SQL Query Notifications are unavailable on SQL Server ‘.’ in Database ‘NAVDatabase’. The Object Change Listener has switched to polling.
Secondly, when NAV Server is in polling mode and you start SQL Profiler you will see this query being run regularly by .New sqlClient Data Provider:
exec sp_execute 1,@lastKnownTimeStamp=463223
This is a pre-prepared query which looks like this:
SELECT [Object Timestamp], [Object Type], [Object ID], [Object Key] FROM [dbo].[Object Tracking] WHERE [Object Timestamp] > @lastKnownTimeStamp’,@lastKnownTimeStamp=463225
In polling mode, this is how NAV Server checks for object changes since last time it checked, so it knows whether to send updated object definitions (metadata) to RTC.
Troubleshooting
So that’s the background. How do we handle the case where object changes are not seen in RTC until we restart NAV Server? If this problem happens, then switch method from SQL Broker to Polling or visa versa. You switch by enabling / disabling the broker like this:
ALTER DATABASE [MyNAVDatabase]
–SET ENABLE_BROKER
SET DISABLE_BROKER
WITH ROLLBACK IMMEDIATE
The “WITH ROLLBACK IMMEDIATE”-part of this query is to avoid what happened for me that the query just hang, or would only run if the database was put in single user mode.
In the cases we have seen, the broker has been enabled but had some kind of problem. A slightly cryptic message was recorded in the SQL Server Log every time an object was changed in Object Designer. This message was not logged in the Application log. So make sure to check in SQL Server Management Studio under Management -> SQL Server Logs -> Current. If this gives enough information to solve the problem then good. If not, then there is the option of switching to polling (disable the broker) until the root of the problem can be resolved.
Enabling the broker is the preferred option since it saves NAV Server for checking ever so often for object changes. But if the broker doesn’t work then at least there is the option to disable it until any problems can be resolved.
Lars Lohndorf-Larsen
Dynamics NAV Support EMEA