Have you ever had a situation where you need to restart your NAV Server in a three-tier setup in order to get it to pick up new FOBs after importing? In this blog post, I’ll look at some of the reasons why this can occur and give you some techniques for fixing it.
The other day I came across this situation (actually found it through getting a whole bunch of errors in the application log on the NAV Server) where the database didn’t have a login for the dbo alias. This can happen if you restore a customer’s database where the dbo alias is a login that you have removed from the database. The result I found was that the NAV Server continually failed to listen to the queue. The solution to this was easy – I used exec sp_changedbowner ‘sa’ to allocate the sa user as the database owner. Check the dbo user in the database and ensure there is a login associated before you try this one – I’ve only ever seen it once.
This one happens when you haven’t installed your database properly or you’ve changed the user that is used to run the NAV Server. I see this one all the time because the install routine doesn’t seem to let you specify which account should be used for the NAV Server, or you often need to change the user after the install.
You know you have this problem if you have this information message in the application log:
SQL Query Notifications are unavailable on SQL Server 'SERVERNAME' in Database 'DatabaseName'. The Object Change Listener has switched to polling.
Here’s a series of steps I go through to fix this up.
Let’s say that my domain is AMAZON and the user that runs my NAV Server service is CodingIsis (notice the subliminal advertising?)
You need to check that the $ndo$navlistener schema exists. If it doesn’t execute the following command.
CREATE SCHEMA [$ndo$navlistener] AUTHORIZATION [AMAZON\CodingIsis];
If it does exist, make sure the schema is owned by AMAZON\CodingIsis. I find the easiest way to change this is to drop the schema and recreate is with the previous command.
Next you need to ensure that the default schema for our AMAZON\CodingIsis user is $ndo$navlistener. You can do this with the following command.
ALTER USER [AMAZON\CodingIsis] WITH DEFAULT_SCHEMA = [$ndo$navlistener];
Then you need to grant select permission on the Object Tracking table.
GRANT SELECT ON [Object Tracking] TO [AMAZON\CodingIsis];
Next I want to find out if the Broker is enabled. In order to enable this requires putting the database in single user mode so it’s best to check first whether this is necessary.
SELECT is_broker_enabled FROM sys.databases WHERE name = 'DatabaseName'
Replace DatabaseName with your database name. If this selects 0, you need to execute the following to enable the broker. If it returns 1 then you can skip this step.
ALTER DATABASE [DatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [DatabaseName] SET ENABLE_BROKER;
ALTER DATABASE [DatabaseName] SET MULTI_USER WITH ROLLBACK IMMEDIATE
Finally, I execute the following code that I get from the Microsoft online docs for setting up the SQL User for a NAV Server. The reason I don’t use the Microsoft script in it’s entirety is that the enable broker bit doesn’t work unless you’re in single user mode so it just sits there waiting for everyone else to log out. Well that’s how it was the last time I tried to run it.
GRANT CREATE PROCEDURE TO [AMAZON\CodingIsis];
GRANT CREATE QUEUE TO [AMAZON\CodingIsis];
GRANT CREATE SERVICE TO [AMAZON\CodingIsis];
GRANT VIEW DEFINITION TO [AMAZON\CodingIsis];
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [AMAZON\CodingIsis];
GRANT RECEIVE ON QueryNotificationErrorsQueue TO [AMAZON\CodingIsis];
GRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO [AMAZON\CodingIsis];
After doing all of this, I restart my NAV Server and Bob’s your uncle, the listener is working.
So that’s the end of my post, I hope you enjoyed it and it’s of some use to you. Now you don’t have to restart your NAV Server every time you upload FOBs, you’ll have lots more leisure time that you can use for reading exciting technothrillers from new authors like Coding Isis for only 99c in the amazon store.