Gaspode is a small terrier-like dog featured in seven of Terry Pratchett's Discworld novels. He possesses human-level intelligence and the ability to speak, as well as an extensive collection of diseases. As a newborn pup, Gaspode was thrown into the River Ankh in a brick-weighted sack. Since it was the Ankh, Gaspode was subsequently able to crawl ashore and find shelter in an alley, though not before forming a rather confused sexual relationship with the brick.
I, on the other hard, am a full time Dynamics NAV consultant. I worked in the UK as an XAL consultant for many years. When Axapta was launched, I worked on that and then finally I moved to NZ to work on Navision. I love technology and guess that deep-down I am a programmer. I spend most of my time analysing business problems and designing solutions.
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.
This blog post is not about Dynamics NAV, so if you are only interested in Dynamics NAV. Stop reading now. Still here? Phew!
I wrote a novel. It took me ages (around two years). You can buy it if you want. I’d really like you to. It’s ridiculously cheap (99c in the amazon.com store).
So here’s the thing. I thought I’d write a blog post about some of the reasons behind why I wrote the novel, like why I think computer programmers aren’t boring geeks. Like how I wanted to create a tough-guy action hero that was also a computer programmer. But I figured I should just show you the cover, let you read the blurb and make up your own mind.
Technology to die for... The most significant improvement to mankind since the invention of the internet, Chris Sanders knows his research will change the world and make him a very rich man. When Chris’s beautiful research assistant is murdered, evidence suggests she and Chris were having an affair and he becomes the prime suspect. Accused of adultery and murder, Chris must turn to his wife for help in proving his innocence, but should she trust him? Will she help? Detective Ben Naylor from Washington DC’s Metro PD finds the evidence against Chris is unequivocal. Either Chris Sanders is a calculating, cold-blooded killer, or he is the unluckiest son-of-a-*** alive. Can Chris win the fight to prove his innocence? Can he win the battle for his life? Coding Isis is a fast-paced techno-thriller that brings a new breed of hero to the genre.
If you don’t have a Kindle, you can still read my novel using free Kindle reading apps, including one for your PC (and I know you have a PC). Even if you don’t want to spend 99c on my novel (and I can understand it if you don’t) you would make my day if you clicked over to the book page and added it to your wish list, because I’m told that this helps in the rankings and that helps more people see my book.
I’ve been blogging on Dynamics NAV for five years so if I’ve ever written anything you found useful, now’s your chance to say thanks by helping me promote my book. But let’s just say you don’t like my NAV blog and you really want me to stop. The best way to do that would be to buy two copies of my book and I would then become a full time novelist and never blog about NAV again.
I won’t blog about novels again, because I understand that you are only following my blog because you believe it to be about Dynamics NAV, but if you are interested in my writing and would like to know about book progress, competitions, give-aways, and other stuff, you can like my author Facebook page at http://Facebook.com/DavidRoysAuthor or follow my writing blog at http://davidroys.blogspot.com.
I came across something that was a bit weird in an implementation where every user in a Citrix deployed RoleTailored client suddenly connected to the TEST system instead of the LIVE system. I thought I’d share this gotcha to save others from making the same mistake.
When you start Dynamics NAV 2009’s RoleTailored client, there are a three ways for the client to determine which server to connect to. This is handy to know because most people want to have a live system and a test system shortcut on their desktop and it’s nice to know where you’re going when you start the client.
The first method is the default method where the system simply reads your settings from the ClientUserSettings.config file which you can find in
%ProgramData%\Microsoft\Microsoft Dynamics NAV\
That’s pretty easy because you don’t need to do anything. The file gets created automatically and if you connect to a different server, the new value gets saved in the file. Not very useful for switching between Live and Test though.
This one’s pretty neat. When you start the client, you can add a command line parameter to specify the location of the config file you want to use. The parameter is settings and here’s an example of the shortcut you would need.
But this is where the gotcha comes in. If you use this settings file to start the client and then connect to a different server, the change gets written back to the config file. That’s not so good when you were trying to use different files to point you to either Live or Test systems. It’s even worse if you are using Citrix to publish the application and every user is updating the same config file. Whilst you don’t get errors, it can be a bit confusing if one user starts up the LIVE system and then jumps to the TEST server which writes the settings back to the config file so the next time a user tries to open the LIVE system they end up in TEST. One solution to this is to make the config files read-only, the other is to use the startup URI.
I quite like this option for a couple of reasons. First of all, it’s really obvious which system you’re connecting to. Secondly, if you’re connecting to a system that is not your default server (remember the ClientUserSettings.config file?) then the RTC puts up a helpful message telling you. This is great for having two shortcuts that will take you into live or test and it’s not going to mess with your ClientUserSettings.config file either.
The syntax for the URI allows you to specify which company you want to start in too. You could use this to start up on a specific page if you like.
The double quotes are needed if your company name contains spaces.
I came across an error today that was a monstrous nightmare to track down. First of all, it worked in the Classic client but not in the RoleTailored client. Sigh. Don’t you just hate that? It means going through the pain of debugging the RoleTailored client.
The error was caused because the FieldRef.Value function for a FieldRef that points to an Option type field returns different values in the Classic client and RoleTailored client.
If you want proof, try running this Codeunit in both the RTC and Classic.
// ServiceTier returns different values for FieldRef.Value for Option // fields compared to Classic client.
lFieldRef := lRecordRef.FIELD(1);
You’ll see the following results. Classic client first…
Then the RoleTailored client…
You see that? One returns the string value and the other returns the numeric value for the option string.
The error we were getting was because the field was being used as an Integer which was throwing a run time error whenever it hit that particular bit of code but only when using the RTC.
Most of the time in finding this error was in tracking down the line of code that was causing the error to be thrown. I was reading the Statement of Direction for NAV the other day and noticed it suggests we’ll be getting an easy-to-use debugger for the RTC in a future version – I can’t wait! Let’s hope in the future I can say “break on error” and just run my RoleTailored client and find out what the issue is straight away.
If you’ve been following my on-going battle against the pigs, you’ll be pleased to know that I now have attained three stars on all of the levels in Angry Birds Seasons, Angry Birds, and Angry Birds Rio, so with no more pigs to kill until the next update, I figured I’d write another NAV blog.
One of the reasons I started blogging was to have a place where I could save little gems so they can be easily found later on. One of the directors at Intergen suggested that I blogged the information and then use Google to find it – so that’s what I did. This week I needed to find something I knew I’d done before, but it took me quite a while to track it down, so I figured it’s time to blog the solution so I need never struggle again – and who knows, it may help some of you out too.
Have you ever had a SQL Backup to restore to a SQL Server that does not allow mixed mode (no database logins, only Windows users) and you need to open the NAV database using NAV but don’t have a login? I created the following SQL code to do this, but you may find it useful to be able quickly add all of the Windows users for your team to a customer’s database. Or how about using the ability to call SQL Stored Procs from NAV (recently blogged about by Waldo) to allow new Windows users to be added to NAV from the RoleTailored client – now that sounds useful!
NAV stores security for Windows users with a SID which is a funny string that starts with “S” and the first thing I need is something that will give me the SID string for a Windows login.
Create the following function in the master database.
USE master GO CREATE FUNCTION fn_SIDToString ( @BinSID AS VARBINARY(100) ) RETURNS VARCHAR(100) AS BEGIN IF LEN(@BinSID)%4<>0 RETURN(NULL) DECLARE @StringSID VARCHAR(100) DECLARE @i AS INT DECLARE @j AS INT SELECT @StringSID='S-'+CONVERT(VARCHAR,CONVERT(INT,CONVERT(VARBINARY,SUBSTRING(@BinSID,1,1)))) SELECT @StringSID=@StringSID+'-'+CONVERT(VARCHAR,CONVERT(INT,CONVERT(VARBINARY,SUBSTRING(@BinSID,3,6)))) SET @j=9 SET @i=LEN(@BinSID) WHILE @j<@i BEGIN DECLARE @val BINARY(4) SELECT @val=SUBSTRING(@BinSID,@j,4) SELECT @StringSID=@StringSID+'-'+CONVERT(VARCHAR,CONVERT(BIGINT,CONVERT(VARBINARY,REVERSE(CONVERT(VARBINARY,@val))))) SET @j=@j+4 END RETURN(@StringSID) END
Once you have this function, the following stored procedure will create a Windows Login, add it as a windows user, and finally make the user a member of the SUPER role. I’ve hard-coded the database name in this sproc, but you should be able to easily make this a parameter to the sproc if you wish.
CREATE PROCEDURE [dbo].[proc_CreateNavUser] @User sysname AS SET NOCOUNT ON DECLARE @SQL VARCHAR(MAX) DECLARE @RetVal INTEGER SET @RetVal=0 BEGIN TRY IF(SELECT COUNT(*) FROM master.sys.server_principals WHERE name=@User)=0 BEGIN SET @SQL='use master CREATE LOGIN ['+@User+'] FROM WINDOWS WITH DEFAULT_DATABASE=[master]' EXECUTE(@SQL); END IF(SELECT COUNT(*) FROM [Demo Database NAV (6-0)].sys.database_principals WHERE name=@User)=0 BEGIN SET @SQL='use [Demo Database NAV (6-0)] CREATE USER ['+@User+'] FOR LOGIN ['+@User+']' EXECUTE(@SQL); END END TRY BEGIN CATCH SELECT Retval=@RetVal RETURN END CATCH BEGIN TRY SET @RetVal=1 INSERT INTO [Demo Database NAV (6-0)].dbo.[Windows Login]([SID]) SELECT master.dbo.fn_SIDToString(sp.sid) FROM master.sys.server_principals sp WHERE sp.name=@User AND master.dbo.fn_SIDToString(sp.sid) NOT IN(SELECT sid FROM [Demo Database NAV (6-0)].dbo.[Windows Login]) --Add Roles (SUPER) INSERT INTO [Demo Database NAV (6-0)].dbo.[Windows Access Control]([Login SID],[Role ID],[Company Name]) SELECT master.dbo.fn_SIDToString(sp.sid),'SUPER','' FROM master.sys.server_principals sp WHERE sp.name=@User AND master.dbo.fn_SIDToString(sp.sid) NOT IN(SELECT [Login SID] FROM [Demo Database NAV (6-0)].dbo.[Windows Access Control] WHERE [Role ID]='BASIC') SELECT Retval=@RetVal END TRY BEGIN CATCH END CATCH
Enjoy the srpoc. If you do write something that uses this – like a tool to create Windows logins in the RoleTailored client, why not share your solution too?
Now it’s time to play Cut the Rope.