Using Views and Linked Tables

There are always some cases where you would like to have external data available to Dynamics NAV.  In cases where the data is available to the SQL Server hosting the Dynamics NAV database it is easy to create a view in the NAV database and a linked table in the Dynamics NAV.  The process is;

Create a normal table in Dynamics NAV and save it.  Make sure that you select to use the same table for all companies.

TableProperties1

Next go to SQL Management Studio and build a CREATE TABLE script for this table.  When you do the CREATE VIEW script you must make sure that all fields are identical and with matching collation. I begin by dropping the table and then building the view.

[code lang=”sql”]USE [NAV 2013 R2 Company]
GO

/****** Object: Table [dbo].[National Register] Script Date: 13.9.2013 09:35:01 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

DROP TABLE [dbo].[National Register]
GO

CREATE VIEW [dbo].[National Register] AS SELECT
[No_] COLLATE Icelandic_100_CS_AS AS [No_]
,[Type]
,[Family No_] COLLATE Icelandic_100_CS_AS AS [Family No_]
,[Name] COLLATE Icelandic_100_CS_AS AS [Name]
,[Sorting] COLLATE Icelandic_100_CS_AS AS [Sorting]
,[Community] COLLATE Icelandic_100_CS_AS AS [Community]
,[Gender]
,[Martial Status]
,[Mate No_] COLLATE Icelandic_100_CS_AS AS [Mate No_]
,[Post Code] COLLATE Icelandic_100_CS_AS AS [Post Code]
,[Address] COLLATE Icelandic_100_CS_AS AS [Address]
,[Guardian No_] COLLATE Icelandic_100_CS_AS AS [Guardian No_]
,[Industry Code] COLLATE Icelandic_100_CS_AS AS [Industry Code]
,[Industry] COLLATE Icelandic_100_CS_AS AS [Industry]
,[Address Code] COLLATE Icelandic_100_CS_AS AS [Address Code]
,[identification No_] COLLATE Icelandic_100_CS_AS AS [identification No_]
,[Community Sorting] COLLATE Icelandic_100_CS_AS AS [Community Sorting]
,[Legal Home Code] COLLATE Icelandic_100_CS_AS AS [Legal Home Code]
,[Blocking Code] COLLATE Icelandic_100_CS_AS AS [Blocking Code]
,[Country] COLLATE Icelandic_100_CS_AS AS [Country]
,[Place of Birth] COLLATE Icelandic_100_CS_AS AS [Place of Birth]
,[Date of Birth]
,[Address Code 2] COLLATE Icelandic_100_CS_AS AS [Address Code 2]
,[Last Local Address] COLLATE Icelandic_100_CS_AS AS [Last Local Address]
,[Proxy No_] COLLATE Icelandic_100_CS_AS AS [Proxy No_]
,[Church] COLLATE Icelandic_100_CS_AS AS [Church]
,[Address to] COLLATE Icelandic_100_CS_AS AS [Address to]
,[Status] COLLATE Icelandic_100_CS_AS AS [Status]
,[Deport Date]
,[Post Address] COLLATE Icelandic_100_CS_AS AS [Post Address]
,[c_o Name] COLLATE Icelandic_100_CS_AS AS [c_o Name]
,[c_o Address] COLLATE Icelandic_100_CS_AS AS [c_o Address]
,[c_o Post Code] COLLATE Icelandic_100_CS_AS AS [c_o Post Code]
,[c_o Post Address] COLLATE Icelandic_100_CS_AS AS [c_o Post Address]
,” COLLATE Icelandic_100_CS_AS AS [VAT No_]
,[Registered By] COLLATE Icelandic_100_CS_AS AS [Registered By]
FROM [NAV 2009 R2 Company].[dbo].[NR Data]
[/code]

Next step is to modify the table properties and set this table to be a linked table.
TableProperties2
Now NAV 2013 R2 has access to all the data in the table. The user running the NAV Service must have read access to the original table.  Make sure that all pages that use this table are read-only as the data can’t be modified through a view.

In NAV 2013 R2 there is an issue that we need to be aware of.  The NAV Server caches the data in these tables so we can have different results in NAV 2013 R2 than expected.  My contacts tell me that this issue has been fixed in the upcoming NAV vNext and I created a Microsoft Connect suggestion to have this fixed in NAV 2013 R2.  Until that solution is available the workaround is to use SELECTLATESTVERSION in you code before using the linked data.  Here is an example function that is reading a plain text password from a linked database and comparing that to an incoming MD5 password.  This issue does not seem to affect pages.  In other words the linked data displayed in pages looks up to date.

 

[code] PROCEDURE ValidateLogin@50011(ChurnNo@50000 : Code[10];MD5Password@50001 : Text[80]) : Boolean;
VAR
ChurnNoRegister@50002 : Record 10017450;
MD5@50003 : DotNet "’mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Security.Cryptography.MD5";
Encoding@50004 : DotNet "’mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Text.Encoding";
HashBytes@50005 : DotNet "’mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Array";
Data@50009 : DotNet "’mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Byte";
StringBuilder@50008 : DotNet "’mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Text.StringBuilder";
StringComparer@50006 : DotNet "’mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.StringComparer";
Loop@50007 : Integer;
BEGIN
SELECTLATESTVERSION;
IF NOT ChurnNoRegister.GET(ChurnNo) THEN EXIT(FALSE);
IF ChurnNoRegister."Farmer Web Password" = ” THEN EXIT(FALSE);
MD5 := MD5.Create;
HashBytes := MD5.ComputeHash(Encoding.UTF8.GetBytes(ChurnNoRegister."Farmer Web Password"));
StringBuilder := StringBuilder.StringBuilder;
FOR Loop := 0 TO HashBytes.Length – 1 DO BEGIN
Data := HashBytes.GetValue(Loop);
StringBuilder.Append(Data.ToString(‘x2’));
END;
StringComparer := StringComparer.OrdinalIgnoreCase;
EXIT(StringComparer.Compare(MD5Password,StringBuilder.ToString) = 0);
END;
[/code]

 

Importing and Exporting Data in Microsoft Dynamics NAV 2013 R2 CU 8

Two weeks ago Microsoft released CU8 for Dynamics NAV 2013 R2.  This upgrade included the long-awaited Import and Export that is replacing the old FBK backup in the Classic Client.

There are two ways to do import and export.  One is through the Windows Client

ImportExport

and the other is with PowerShell commands Export-NAVData and Import-NAVData.  You can read all about this on the Microsoft Dynamics NAV Team Blog by clicking here.

Compared to the old Classic Client FBK backup this is very fast.  On the other hand the new import functionality is very strict on the database schema.  It needs to be excactly the same in the database used for the export and import.

I was in the process of upgrading a company from NAV 5.0 SP1 and had planned to add the company to an existing NAV 2013 R2 database wich already included a few companies.  This was not as easy as you might think.  Here are the issues that I encountered and how they were solved.

After I finished the upgrade I made sure that all objects in the upgraded database where identical to the destination database.  This I did by exporting all objects from the destination database to the upgraded database.  I used a merge tool on the exported text object file to make sure.

At this point I was not able to import the data file and asked for help from Microsoft.  Got all the help I needed and the first thing that I needed to make sure was that all the tables must be identical on a SQL level.  This was done with this SQL command.

[code lang=”sql”]select db1.[Object ID] "DB1 Table ID", db1.Name "DB1 Table Name", db1.Hash "DB1 Metadata Hash", db2.[Object ID] "DB2 Table ID", db2.Name "DB2 Table Name", db2.Hash "DB2 Metadata Hash"
from [Destination Database].[dbo].[Object Metadata Snapshot] db1
full outer join [Source Database].[dbo].[Object Metadata Snapshot] db2 on db1.[Object ID] = db2.[Object ID][/code]

I needed to make sure that the “DB1 Metadata Hash” what identical to “DB2 Metadata Hash” for all tables.  This was not true even if all objects where identical.  After recompiling all tables in both databases I saw that I now had identical hashes.

This was not enough.  Since I upgraded the source database from an earlier NAV version the database still held all the old virtual table snapshots.  We needed to remove them with this SQL command.

[code lang=”sql”]delete from [Source Database].[dbo].[Object Metadata Snapshot]
where [Object ID] in
(2000000007, 2000000026, 2000000049, 2000000101, 2000000102, 2000000103, 2000000020, 2000000055, 2000000009, 2000000029, 2000000038, 2000000058, 2000000041, 2000000028, 2000000063, 2000000022, 2000000048, 2000000040, 2000000043, 2000000044, 2000000039, 2000000045)
[/code]

After this I successfully exported and imported the upgraded company into the destination database.  One more thing that I saw is that I needed to restart other service instances in order to see the newly imported company.

This new functionality opens a lot of possibilities for developement and testing.  We can now easily with PowerShell scripts create a backup of our live company data and import them into our test and/or developement environment.

In both the Windows Client and in PowerShell it is possible to copy companies, rename and delete.  I know that the import process takes a longer time than the export process.  I tried the export process on a 500GB database and it took 2 hours and 27 minutes.  In the Classic Client this would have taken days.

I expect to see this functionality improve in future releases and also expect Microsoft or some of the community leaders to publish scripts to automate a data refresh in a testing environment.

Scanning and attachments for Incoming Documents in Microsoft Dynamics NAV 2013 R2

A new feature in NAV 2013 R2 allows companies to store links to incoming documents and reference them in Purchase Invoices and Journal Lines.  There is a video in the How Do I series on Managing Incoming Documents in Microsoft Dynamics NAV 2013 R2.

I wanted to add a functionality to scan and store the files within NAV.  Using the Hardware Hub and the Hardware Hub Twain Client to easily scan documents and store them in NAV.  The objects needed are attached below.  You will need to put the Hardware Hub Proxy Add-in to the NAV 2013 R2 Server Add-in folder.  There is a possibility of doing this without the add-in by manually creating the soap requests.  I did this in the NAV 2009 R2 version for the Classic Client and if needed I should be able to make available a similar NAV 2013 R2 version.

So, how does it work ?

First step is to download and install the Hardware Hub Twain Client on the computer with a twain compatible scanner and the Hardware Hub Proxy Add-in to the Server Add-in folder..  Double click the icon in the notification area to get the program windows in foreground.  Next step is to install the new and modified objects into your NAV 2013 R2 database.

Then go to Incoming Document in the Windows Client.  After an incoming document entry has been created the New Attachment button will be enabled.NewIncomingDocumentEntry

Click New Attachment button.  The first time you open this page you will need to set up the scanner connection.

ScanningSetup

You need to compare the Hardware Hub Path and the Hardware Hub Scanner GUID to the Twain Client window.  It should be enough to copy the GUID from NAV to the Twain Client.

TwainClient

Minimize the Twain Client and go to the ACTIONS menu to select scanner.  You can test the Hardware Hub connection with the About action.

SelectScanner

These settings will be saved with the page data personalization for the current user.

If you select to “Show Settings” the settings dialog will sometimes appear behind the NAV Windows Client – just to let you know.

You can also browse for an existing file in the File Name drill down arrow and that file will be uploaded into NAV.  When you close this window the URL to the Incoming Document Store will be added to the Incoming Document.  You can add multiple files to a single Incoming Document and the URL will open all of them.

This solution will work on a cloud based NAV and also via remote desktop.  I was able to scan with the Web Client but that functionality is not fully tested.  Your feedback is always appreciated.

Objects: IncomingDocumentStore, 2014-07-29 Update

Hardware Hub IIS Service on Objects4NAV.com

Install Client and Server update with PowerShell

It is time to share with you the scripts that I am using to install and update the NAV application in the company network.  In the attached ZIP packages you will find a lot of files.

In the root folder I keep the settings files.  These files must be customized.  The folders are as follows;

  • Edge3-Internet – my internet facing server.  Here I install ClickOnce, WebClient and NAV Service.
  • Fjarvinna-Domain – my remote desktop server.  Here I install KB updates remotely.
  • Localhost-Domain – my intranet server.  Here I install NAV Service for the domain.
  • NAVCmdLets – my collection of PowerShell scripts for NAV.  Some a copied from the NAV DVD.
  • Install-KBLocally – scripts to install a KB package to the local computer.

If we first look at the Edge3-Internet folder, here I have scripts to remotely install NAV on a server.  I usually create a ClickOnce container in the folder ‘c:\inebpub\ClickOnce’ and keep all my ClickOnce subfolders in there.  In the Set-MachineSettings.ps1 file I set the machine name.

Next, in Fjarvinna-Domain folder I only have scripts to upload and install a KB package.  Also in here I have the Set-MachineSettings.ps1 to define the machine name.

The Localhost-Domain folder includes scripts to install NAV Service and NAV Users to the localhost.

Then there are the functions that I use in the above scripts.  In the NAVCmdLets folder I have a collection of scripts to handle ClickOnce, KB install and some SQL administration tasks.

Finally the Install-KBLocally folder.  As the name suggest, the contained scripts are used to install a KB package on the localhost.  I usually create a folder that contains this folder, the NAVCmdLets folder and for example the KB2955941 folder.  The KB2955941 can be downloaded from Microsoft.  I usually delete all sub folders except ADCS, NST, OUTLOOK, RTC and WEB CLIENT after I add this folder to the update package.

Also, in my KB package I have two CMD files.  One to install to a 64bit machine (default) and the other to a 32bit machine.  If you put this package on your network and modify the CMD files to point to the correct file share you should be able to use this for all the computers on the domain.  The KB package without the Microsoft binaries is attached below.  Make sure to right-click on the install CMD file and run as administrator for a succesful install.

If you are running an old operating system, you might need KB2506146 or KB2506143 installed before using the KB package.

With your network administration tools you should be able to use some of these scripts to install a KB package on all your domain computers to make sure that everyone is running the latest version recommended by Microsoft.

NAVPowerShellScripts KB

Change table layout and move data

In older versions of NAV I got used to using a report to move data from one table to another.  Since the last update from Microsoft for NAV 2013 R2 I am using the SQL Management Studio more and more.

For example, yesterday, I needed to change a table layout that included a change in the primary key.

I had modified the table in the developement database but was unable to import the new table because of the existing data.

I solved this by creating a temporary table with the original layout.  I can choose any number for the table that I can use it with my developement license since I will not be using the table in NAV.  I designed the original table and used File-Save As to create the new table.

Then I moved over to the SQL Management Studio.  There I found the original table and scripted a SELECT statement to a new query window.  Next I found the newly created shadow table and scripted an INSERT statement to the clipboard.  Then I combined these two statements to look like this.

[code lang=”sql”]USE [NAV_DATABASE]
GO

INSERT INTO [dbo].[NAV Company$Temp Partner Inbound Mapping]
([IC Partner Code]
,[Responsibility Center]
,[Ship-to Name]
,[Ship-to Address]
,[Ship-to Address 2]
,[Ship-to City]
,[Inbound Mapping Group Code])
SELECT
[IC Partner Code]
,[Responsibility Center]
,[Ship-to Name]
,[Ship-to Address]
,[Ship-to Address 2]
,[Ship-to City]
,[Inbound Mapping Group Code]
FROM [dbo].[NAV Company$IC Partner Inbound Mapping]
GO
DELETE FROM [dbo].[NAV Company$IC Partner Inbound Mapping]
[/code]

At this stage I have deleted all data from the table and was able to import the new modified table into the production NAV database. In this case I have two new fields in the new table. The primary changed to include a line number. That meant that I needed to create a line number for each row. This can be done in with SQL.

[code lang=”sql”]USE [NAV_DATABASE]
GO

INSERT INTO [dbo].[NAV Company$IC Partner Inbound Mapping]
([IC Partner Code]
,[Line No_]
,[Responsibility Center]
,[Ship-to Code]
,[Ship-to Name]
,[Ship-to Address]
,[Ship-to Address 2]
,[Ship-to City]
,[Inbound Mapping Group Code])
SELECT
[IC Partner Code]
,10000 * ROW_NUMBER() OVER(ORDER BY [IC Partner Code]) AS Row
,[Responsibility Center]
,”
,[Ship-to Name]
,[Ship-to Address]
,[Ship-to Address 2]
,[Ship-to City]
,[Inbound Mapping Group Code]
FROM [dbo].[NAV Company$Temp Partner Inbound Mapping]
GO

DELETE FROM [dbo].[NAV Company$Temp Partner Inbound Mapping][/code]

If I would have needed to start the row number with 10000 for every instance of IC Partner Code I would have needed to use a cursor and iterate through every instance of IC Partner Code from another table and execute the statement filtered by the IC Partner Code.  This I used for example in the Azure Backup Script.

I finished this process by deleting the temporary table for the production database.

 

Help Microsoft with the upgrade story

Customer Survey: Microsoft Dynamics NAV upgrade

​With this survey, we would like to solicit your valuable input in regard to the Microsoft Dynamics NAV upgrade story to better understand your current upgrade situation.

​The questionnaire below focuses on upgrading from Microsoft Dynamics NAV 2009 Classic client to the RoleTailored client as well as the upgrade experience in general. The Microsoft Dynamics NAV team is determined to provide you with the best possible service to help you move your business forward and get the most of our new releases.

http://sgiz.mobi/s3/8dd03168613b

Arion Banki currency importer

It is not often that I post a solution that is intended to be used in Iceland only.  Here is one.

Arion Banki is one of the major national banks in Iceland and they want to support Dynamics NAV users.  On their website you will find the currency exchange rates for every working date.  I created an importer that will download the exchange rate from their website and import into NAV.

There are two ways to do this.  First is to go into Currencies in the NAV Client and click on Import

ArionBankiImportCurrency

The other way is to add a Job Queue Entry to make this an automatic task.

NewJobQueueEntry

The process will find the last date imported into your system and import all days from and including that date to the current working date.

CurrencyImportNAV2009 CurrencyImporterObjects2013R2

 

 

The upgrade story – continued

The Client went live on the 6th.  This is the task I told you about in last September.

The database was 567GB in size with just a little less than 400GB used.  It had 1.506.747 sales invoices and 30.438.858 value entries.  Version 4.0Sp3 with a lot of added fields and customization.

When I first ran the upgrade process with the standard upgrade tool it took about ten days.  As you can see there is no way to stop a company for ten days.  The process was rewritten and these are the steps I used.

  • Backup the live database with compression enabled
  • Copy the backup file to the new SQL server
  • Restore the backup to the new server
  • Drop all statistics that was stopping me from modifying tables
  • Run Inventory Adjustment
  • Import and execute 4.0 SP3 -> 2009 R2 Step 1
  • Import 2009 R2 objects
  • Prepare data for Step 2 by importing conversion tables
  • Execute 4.0 SP3 -> 2009 R2 Step 2
  • Import 2009 R2 -> 2013 R2 upgrade objects and execute Step 1
  • Change database compatibility level to 2012
  • Execute SQL commands to drop all statistics that was blocking database conversion
  • Open the database with NAV 2013 Developement Environment and convert database
  • Open the database with NAV 2013 R2 Developement Environment and convert the database
  • Compiled the system tables
  • Changed the SQL Command Timeout for the service to 12:00:000 and started the service
  • Selected “No” in the Developement Environment option “Prevent data loss from table changes”
  • Imported all NAV 2013 R2 objects
  • From PowerShell, executed Sync-NAVTenant
  • Selected “Yes” in the Developement Environment option “Prevent data loss from table changes”
  • Import 2009 R2 -> 2013 R2 upgrade objects and execute Step 2

There where a few minor steps in between and after that was needed for this customer but basically this was it.

The good news is; this was completed in just under 23 hours with an unexpected delay of just over an hour.  This can be done and the client is happy.

How Do I Video Series

In last week I saw a video by Mark on posting routines.  This week I see that Claus has created a video on TransFooters and TransHeaders in RDLC.

Currently we have access to a lot of How-Do-I videos on Microsoft Dynamics NAV 2013 R2 both from Partner Ready Software and from Microsoft.

Watching a video is an easy way to learn.  Go a head and follow the above links and get stuffed with knowledge…

 

Potential data loss in NAV 2013 R2 fixed

Update Rollup 5 for Microsoft Dynamics NAV 2013 R2 has been released.

You can download update rollup 5 from KB 2937999 – Update Rollup 5 for Microsoft Dynamics NAV 2013 R2 (Build 36281).

This hotfix resolves the following problems:

ID Title
358016 <Field A> -= <Field B> – <Variable> statement calculates incorrect results.
357968 The Windows client crashes when you click a column header to change the sort order in a list page.
357487 Data loss can occur when you change an object multiple times or compile all objects in a live database that has active users connected.
357430 Script control add-in that opens a new browser window does not work in the web client.
357397 “Your entry of [decimal value] is not an acceptable value for ‘Duration’. The value [decimal value] can’t be evaluated into type Duration” error when you enter a decimal value into a field of type duration.
357335 Filtering on worksheets is not working in the web client.
357223 The RecordRef.FIELDEXIST(0) function always returns TRUE.
357105 The Windows client is leaking memory.
356809 “The custom numbering ID format should start at 164 and should be listed in consecutive order” error when you export an account schedule to Microsoft Excel.
356632 “A call to System.IO.StreamWriter.Close failed with this message: Cannot access a closed file” error when you close a stream object from code.
356339 “Invalid index 0 for this SqlParameterCollection with Count=0” Event Log error when you run multiple NAV server services on the same computer.
356335 “The communication object, System.ServiceModel.Channels.ServiceChannel, cannot be used for communication because it is in the Faulted state” error when you import or compile a table.
356332 Field disappears after you have done a lookup in the field and customized the lookup page.
356284 A subpage is not automatically editable or noneditable when the main page is set to editable or noneditable.
356167 “The filter “[date]” is not valid for the Document Date field on the Sales Invoice Header table. The value “[date]” can’t be evaluated into type Date” error when you select filter to this value on a date column with non-English regional settings.
356090 You can unexpectedly import a table object that removes a column that contains data in the existing table when the table was created by using Save As from the Import Worksheet.
356044 Sorting option is missing in report request pages.
355957 CR (Carriage Return) and LF (Line Feed) creates indentations in ADCS (Automated Data Capture System).
355917 Performance is slow in list pages that have flowfields.
355804 “A security filter has been applied to table [table name]. You cannot access records that are outside of this filter” error when you change a record in a table with flowfields and security filters.
355484 “The metadata object Page [page ID] was not found” error when you select Customize Ribbon in a report request page.
353961 The Windows client stops responding after a window that is opened by an object that has the Dialog.OPEN statement is closed because an implicit Dialog.CLOSE is missing.

I just finished installing this upgrade at a client site.  It was, however, for me not a painless procedure.

I am using PowerShell scripts that upgrade both client and servers.  On the server it will stop the service tier, upgrade the files and start the services again.  This seems to be the problem.

When the Developement Environment is opened with this version 7.1.36281.0 the database will be upgraded.  For this part a single user access to the database is needed so the service tier must be stopped.  This part was without any problems.  The database was converted and things look fine.

Then I started the service tier again and tried to connect.  The client failed to connect and I got this error

The server “<servername>” was unable to process the request.  Close the application and start again.

So I was stuck.  I tried a lot of things and finally found the solution.  In a database that was working I found that the TenantVersionNo in the $ndo$tenantproperty was 1030 but in faulty databases it was 1020.

TenantError

I stopped all services, modified the TenantVersionNo to 1030 and started again.  Rock and roll…