Backup, Restore and Record Link – NAV 2009 R2

Yeah, i know, 2009 is an old one, but it still alive, despite not supported by Microsoft.

Well, we still have a customer, who run NAV 2009 R2, and colleague of mine came across interesting issue: customer reported they lost all records in Record Link table. After some investigation it turned out that they had intention to create a test company – which lead they to the problem.

So, what they did? They took a native NAV backup, then renamed existing company, then restored a backup to bring live company back. Boom! No Record Links anymore!

I am not surprised – Record Link table has ‘data per company = no’ and a primary key ‘Link ID’ .  ‘Company’ field with relation to ‘company.name’ isn’t part of key…

When you made a backup and then rename company ‘A’ to company ‘B’ system will update only ‘company‘ field in the record link table. Then when you restore original company ‘A’ backup if you include data common to all companies – system will not overwrite Record Link table – it will just skip records with same ID (and PK isn’t changed, you see!) .

So, how to solve then? The answer is simple: just a line of code:
RecordLink.Modyfyall(Company, ‘A’) 
assuming there was only one company before.

Yeah, this solve the problem, but what we can do to avoid the issue when we want a test company in old good NAV 2009 R2? My common sense tell me that there is a simple way to avoid that sort of issues.

1. Make a company A backup
2. Restore to clean DB
3. Rename restored to new name (B)
4. Take a backup
5. Restore taken backup to live DB, which will create exact copy of A company with name B.

Of course, test company B will not have a Record Links, as they had same ID, therefore not overwritten, but company still A in Record Link table.

Seems like design flaw in 2009 – but do we care about this? More interesting: it is the same Record Link table structure in latest versions. OK, we can’t copy company in NAV 2013, but can in NAV 2013 R2 and NAV 2015. I’ll investigate and report later how this behave in modern NAV.

Dynamics NAV 2015 and Windows 8.1 (Not PRO)

I’ts turned out that you can’t install Dynamics NAV 2015 web client components on Windows 8.1 not PRO edition. The bloody thing does not  support Windows Active Directory, so can’t enable windows authentication for IIS, which is prerequisite fro WEB client. So, MSFT just made another £100 of me 🙂 Yeah, upgrade to PRO costs just £100.

So, buying a new laptop – check what version of windows is installed on it!

Dynamics NAV and SQL Linked tables

While fiddling with my dozens of linked tables I’ve came across an interesting issue.

I’ve got a table with simple primary key (field 1). 1. This table is connected to SQL View, and the View is pointing to SQL Table. When I run some code against data in my table I’ve identified that there is number of records with same primary key! Well, it is not something you can see in NAV world.

I did some investigation on this, and found, that my SQL table (which wasn’t created by me, but provided by third-party organisation) has no primary key on it, therefore it is fine to have duplicate values in any (or even all fields) for some (or even all) records.

It seems Dynamics NAV Linked tables can’t handle this situation properly. Well, when you define any table in NAV it has to have a primary key, and you will expect it to be unique.  But not for a linked tables. Linked tables inherits the schema defined in the SQL side, so it may have non-unique values in your primary key field.

I’ve tried to reproduce the issue – and have done it quite successfully, please have a look to pictures below:

  1. NAV table structure and Primary Key
    SQL1
  2. SQL table Definition
    SQL2
  3. Data I’ve put to SQL table
    SQL3
  4. Data I can see in NAV system
    SQL4

So, what we can do to resolve this? In ideal world we should always have same primary key in SQL table and NAV linked table. In real life it may be difficult (you may have no right to alter tables provided by third-party organisations) – so if you unable to alter SQL table – then you have to handle this on NAV side by code… Of course, I would prefer to keep same keys on both sides, in Dynamics NAV and in SQL.

RDLC Reports and images in Dynamics NAV2013 / NAV2015

Recently came across an issue – NAV Windows Client crashes when report 206 (Sales Invoice) is run on massive number of records. After some investigation I’ve found that middle-tier and windows client consuming huge amount of RAM. Well, the reason was simple – someone put an 4MB image to company information. By design all standard reports (if has image) send the BASE64 representation of image with each dataset line (see Fig. 1). Of course, if we got say 500 records and 4Mb image – this will come to 2gb, which is memory limit for 32bit NAV Windows Client. So no surprise it crashes… Of course, we can use smaller image, which i did, but even 300kb image multiplied by thousands of dataset lines will create issue. And after all – it is just stupid to send static data with every line – nothing stop us to clean-up dataset. So the solition is as per Fig. 2. And result is Fig. 3. So then dataset will have image populated only for first document dataset lines – which will dramatically reduce dataset size and improve report performance.

Dataset2

Fig 1.

codesnippet

Fig. 2

Dataset1

Fig. 3

Dynamics NAV and SQL Linked servers

Often there is requirement to get data from external sources. We all know about dataports (now xmlports), webservices, and even FILE.READ function. But sometimes there is more convenient way to get data from an external SQL server.
Since old times we do have ‘LinkedObject’ property on tables. What is it? It is just a property, which allow you to link a NAV table to SQL view in the same database. The field definitions in the view and NAV table should be the same, and NAV table name must be exactly the same as view name (but without company name). Ok, this give us a convenient way to access data on same server. What about external servers?
Quite simple: since sql 2005 we can link SQL server to another SQL server (and not only to MS SQL!).
So when we link sql server A and sql server B then we can access data from server B within server A. So we can create a vien on server A, which will read data from server B and then create an NAV linked table, attached to this view…
How do we do this?
On the server A run the following query:

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver
@server = N’B’,
@srvproduct=N’SQL Server’ ;
GO

Then sort out security

EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N’B’,
@locallogin = NULL ,
@useself = N’True’ ;
GO

Then test connection

SELECT name FROM [A].master.sys.databases ;
GO

Then we can do something like this:

SELECT local.name AS Logins, linked.name AS LinkLogins
FROM master.sys.server_principals AS local
LEFT JOIN [B].master.sys.server_principals AS linked
ON local.name = linked.name ;
GO

If this worked – you are able to read server B data from server A.

NAV and hotkeys

Hit an issue. You all probably know that if you hit <ctrl>+<alt>+<f1> in any nav page (or report) – you’ll got a ‘about this page’ page open and can see record details, table name/number, dataset for reports and so on. This did not worked on my laptop ether, and I found what is in charge for it! The intel display driver, as per attached picture. Despite it uses different key combinations – until I turned hotkeys off – nothing worked. After they off – NAV started to behave properly.

Capture

 

Couple of words about commit. Why should we not put commit in middle of the process?

Quite simple – when we got an error after commit – we will have a garbage data, as system will only rollback changes made after commit before error.

NAV has internal auto commit function, which is called automatically at end of process. In 99.99(9) % of cases it is good enough. If you typed COMMIT inside your code you must have a strong reason for this, and I found in most cases there is no reason to put commit. Most common case is a page.runmodal(page::pageid, record), if this is run in middle of process – system will throw error, saying something like ‘The following C/AL functions can be used only to a limited degree during write transactions (because one or more tables are locked)”. Then it says “Use the COMMIT function to save the changes before this call, or structure the code differently.” which is completely misleading in my opinion. Only the second part of statement – ‘structure the code differently’ – is good, but commit in middle of process it is always risk, and generally bad practice.

So yep, it seems like with this error message MS encouraging us to use COMMIT in middle of process, which is entirely wrong, and again, if you use COMMIT inside a function – be prepared to explain why.

NAV 2013r2 / 2015 and Azure

Azure is quite amazing platform, and I am expecting a growth of its popularity.

Unfortunately at the moment there is no Virtual Machine with NAV2015 on the library available now, so I had to configure an NAV2015 installation myself.  Technically azure virtual machine is just an windows 2012r2 server, so it is pretty straightforward to install NAV2015 on it as long as you use only RDP connecting straight to the VM. In my scenario I wanted to make possible to use a NAV Windows Client from any local machine to connect to the Azure NAV server.

There is brilliant how-to by Saurav Dhyani http://saurav-nav.blogspot.in/2013/11/nav-2013-r2-windows-client-web-client_6.html which explain how to configure NAV server with NavUserPassword credentials. I’ve done everything as per blogpost and everything worked. But, when I’ve tried to connect to the server from outside my Azure networks – no luck.

The key to issue was Endpoints. When you create an Azure VM you set-up the ports which will be open to communicate to the machine. I’ve added an endpoint port for port 7046 (which is default port for NAV windows Client), and a Endpoint for WebClient – 8080. The WebClient started to work immediately with no issues, but still no luck for a Windows Client.

The key was a endpoint for port 7045 – management services for Dynamics NAV. It is turned out that NAV Windows client uses port 7045 for authentication, so  until it is not open – no way to login to the NAV service on Azure.

So I’ve ended up with following endpoint setup which allowed me to use Azure VM just like any other server (see picture below).

Azure-Endpoints

 

 

 

 

 

 

Who am I?

Peter Pavuk

I am Dynamics NAV (formerly Navision) developer with 15+ years of experience.

I’ve  started this blog to share stuff about Dynamics NAV development / implementation.