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#).
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
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.
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: MicrosoftDynamicsNavServerSQL 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.
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_BROKERWITH 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.
Dynamics NAV Support EMEA
Thank you! This was a fantastic post. I've had occasional issues with changes like this and your article is a big help to understanding why it might happen.
Question about the Troubleshooting step: Do we have to restart the Service Tier after making that change? When I've run into this issue, I'm generally trying to avoid disconnecting the users. If that SQL query can 'fix it live', that would be stellar.
Good to hear!
And yes you do have to restart NAV Server. It's only when it starts up that it checks whether the broker is enabled or not. So a "live fix" is not possible, unfortunately,
thaks for this. What's the reason for this problem?
If it doesn't work while the broker is enabled, the reason is likely something internally in SQL Server. SQL Server error log might give some information. In my case, I had the problem if I logged on to my machine outside of our domain. The good thing is that if the broker is disabled, then there is no reason why it should not work...
Hope that helps,
This is still a big problem with most of our customers on 2009R2. Is there already a hotfix for this?