InvalidSecurity error when connecting to a NAV Server

For a few weeks I have been running an Internet facing NAV Server.  This server is using “NAVUserPassword” authentication and a SSL certificate.

Everything was working as planned until a few days ago when I was using my PowerShell scripts to install the newest knowledge base package, Update Rollup 3 for Microsoft Dynamics NAV 2013 R2 (Build 36035).

When I opened the client after the update a new version was downloaded and started.  However, I got an error.

FaultCode = „InvalidSecurity“
Reason = „An error occurred when verifying security for the message.“

I rolled back the upgrade and tried again with the same error.  This was the strangest thing.

The Web Client was working perfectly but not the Windows Client.

As I was walking and wandering the solution dropped in.  I recognized that a secure connection is established with by using the date and time of both the client and the server.  I sat back down and checked – the clock on my server was 6 minutes late!

I set the clock and retried the connection – successfully !

I then configured my server to synchronize the clock with a time source like is shown here or here.

Test and fix dimensions before upgrading to NAV 2013

A coworker in Advania is working on a Business Intelligence solution called Advania Insight.  He is working on the installation for a company that is running NAV.  This company has data from Navigator since 1994.

One of the problem we saw was that some of the dimension codes used where not a standard dimension but for example a heading or a footer.  This did not go to well through the analysis and we needed to fix this.

Manually this is a lot of work so I did some SQL queries to find these errors. For example, this query will find all entries in the G/L Entry table that has incorrect value Global Dimension 1 Code.

[code lang=”sql”]USE [Demo Database NAV (7-1)]
GO

SELECT
[Entry No_]
FROM [dbo].[CRONUS Ísland hf_$G_L Entry],
[dbo].[CRONUS Ísland hf_$Dimension Value]
WHERE [Global Dimension 1 Code] = [Code] AND
[Global Dimension No_] = 1 AND
[Dimension Value Type] > 0
GO
[/code]

This query can be used to find entries with missing entries in the Ledger Entry Dimension table.

[code lang=”sql”]SELECT [Entry No_]
FROM [dbo].[CRONUS Ísland hf_$G_L Entry] V
WHERE (SELECT COUNT(D.[Dimension Code]) FROM [dbo].[CRONUS Ísland hf_$Ledger Entry Dimension] D
WHERE [Table ID] = 17 AND V.[Entry No_] = D.[Entry No_] AND D.[Dimension Code] = ‘DEILD’) = 0
AND [Global Dimension 1 Code] <> ”
GO
[/code]

This query can be used to find entries with Global Dimension 1 Code that are missing from the Dimension Value table.

[code lang=”sql”]USE [Demo Database NAV (7-1)]
GO

SELECT [Entry No_]
FROM [dbo].[CRONUS Ísland hf_$G_L Entry] V
WHERE (SELECT COUNT(D.[Dimension Code]) FROM [dbo].[CRONUS Ísland hf_$Dimension Value] D
WHERE [Global Dimension 1 Code] = D.[Code] AND D.[Global Dimension No_] = 1) = 0
AND [Global Dimension 1 Code] <> ”
GO[/code]

Looking forward I saw that it would be a lot of work to manually check all these possibilities so I decided to write a SQL script. First a script that will do the dimension type test on the Ledger Entry Dimension table.

[code lang=”sql”]USE [KS Dynamics NAV]
GO

DECLARE @invalididentifierscars varchar(10)
DECLARE @replacestring varchar(10)
SET @invalididentifierscars = (SELECT [invalididentifierchars] FROM [dbo].[$ndo$dbproperty])

DECLARE @varSQL varchar(max)
DECLARE @DimTableName varchar(256)
DECLARE @DimValueTableName varchar(256)
DECLARE @getCompanyNames CURSOR
DECLARE @CompanyName varchar(50)
DECLARE @loop int
CREATE TABLE #TmpLedgerEntryDimCombinations (CompanyName varchar(50), TableID int, TableName varchar(50), GlobalDimCode varchar(20), GlobalDimNo int)
CREATE TABLE #TmpIncorrectDimEntries (CompanyName varchar(50), TableID int, TableName varchar(50), GlobalDimCode varchar(20), GlobalDimNo int, EntryNo int)
SET @getCompanyNames = CURSOR FOR SELECT [Name] FROM [dbo].[Company]
OPEN @getCompanyNames
FETCH NEXT FROM @getCompanyNames INTO @CompanyName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @DimTableName = @CompanyName + ‘$Ledger Entry Dimension’
SET @DimValueTableName = @CompanyName + ‘$Dimension Value’
SET @loop = 0
WHILE @loop < LEN(@invalididentifierscars)
BEGIN
SET @loop = @loop + 1
SET @DimTableName = REPLACE(@DimTableName,SUBSTRING (@invalididentifierscars,@loop,1),’_’)
SET @DimValueTableName = REPLACE(@DimValueTableName,SUBSTRING (@invalididentifierscars,@loop,1),’_’)
END
SET @varSQL = ‘USE [‘ + DB_NAME() + ‘]; INSERT INTO #TmpLedgerEntryDimCombinations SELECT DISTINCT ”’ + @CompanyName + ”’,[Table ID],O.[Name],L.[Dimension Code],[Global Dimension No_]
FROM [dbo].[‘ + @DimTableName + ‘] L,[dbo].[‘ + @DimValueTableName + ‘] D, [dbo].[Object] O
WHERE D.[Dimension Code] = L.[Dimension Code] AND [Global Dimension No_] > 0 AND O.[Type] = 0 AND O.[ID] = [Table ID]’
EXEC (@varSQL)
FETCH NEXT FROM @getCompanyNames INTO @CompanyName
END
CLOSE @getCompanyNames
DEALLOCATE @getCompanyNames

DECLARE @TableID int
DECLARE @TableName varchar(50)
DECLARE @GlobalDimCode varchar(20)
DECLARE @GlobalDimNo int
DECLARE @DimFieldName varchar(50)
DECLARE @DimFixes CURSOR
SET @DimFixes = CURSOR FOR SELECT CompanyName, TableID, TableName, GlobalDimCode, GlobalDimNo FROM #TmpLedgerEntryDimCombinations
OPEN @DimFixes
FETCH NEXT FROM @DimFixes INTO @CompanyName, @TableID, @TableName, @GlobalDimCode, @GlobalDimNo
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @OrigCompanyName varchar(50)
DECLARE @OrigTableName varchar(50)
SET @OrigCompanyName = @CompanyName
SET @OrigTableName = @TableName
SET @loop = 0
WHILE @loop < LEN(@invalididentifierscars)
BEGIN
SET @loop = @loop + 1
SET @CompanyName = REPLACE(@CompanyName,SUBSTRING (@invalididentifierscars,@loop,1),’_’)
SET @TableName = REPLACE(@TableName,SUBSTRING (@invalididentifierscars,@loop,1),’_’)
END
SET @DimFieldName = ‘[Global Dimension ‘ + CAST(@GlobalDimNo as varchar(1)) + ‘ Code]’
SET @varSQL = ‘INSERT INTO #TmpIncorrectDimEntries (CompanyName, TableID, TableName, GlobalDimCode, GlobalDimNo, EntryNo)
SELECT ”’ + @OrigCompanyName + ”’, ‘ + CAST(@TableID as varchar(20)) + ‘,”’ + @OrigTableName + ”’,”’ + @GlobalDimCode + ”’,’ + CAST(@GlobalDimNo as varchar(1)) + ‘, [Entry No_]
FROM [dbo].[‘ + @CompanyName + ‘$’ + @TableName + ‘], [dbo].[‘ + @CompanyName + ‘$Dimension Value]
WHERE ‘ + @DimFieldName + ‘ = [Code] AND [Global Dimension No_] = ‘ + CAST(@GlobalDimNo as varchar(1)) + ‘ AND [Dimension Value Type] > 0’
EXEC(@varSQL)
FETCH NEXT FROM @DimFixes INTO @CompanyName, @TableID, @TableName, @GlobalDimCode, @GlobalDimNo
END
CLOSE @DimFixes;
DEALLOCATE @DimFixes;

SELECT * FROM #TmpLedgerEntryDimCombinations
DROP TABLE #TmpLedgerEntryDimCombinations
SELECT * FROM #TmpIncorrectDimEntries
DROP TABLE #TmpIncorrectDimEntries
GO

[/code]

Then a similar script that will update the Ledger Entry Dimension table according to the values in Global Dimension 1 Code and Global Dimension 2 Code.

[code lang=”sql”]USE [KS Dynamics NAV]
GO

DECLARE @invalididentifierscars varchar(10)
DECLARE @replacestring varchar(10)
SET @invalididentifierscars = (SELECT [invalididentifierchars] FROM [dbo].[$ndo$dbproperty])

DECLARE @varSQL varchar(max)
DECLARE @DimTableName varchar(256)
DECLARE @DimValueTableName varchar(256)
DECLARE @getCompanyNames CURSOR
DECLARE @CompanyName varchar(50)
DECLARE @loop int
CREATE TABLE #TmpLedgerEntryDimCombinations (CompanyName varchar(50), TableID int, TableName varchar(50), GlobalDimCode varchar(20), GlobalDimNo int)
SET @getCompanyNames = CURSOR FOR SELECT [Name] FROM [dbo].[Company]
OPEN @getCompanyNames
FETCH NEXT FROM @getCompanyNames INTO @CompanyName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @DimTableName = @CompanyName + ‘$Ledger Entry Dimension’
SET @DimValueTableName = @CompanyName + ‘$Dimension Value’
SET @loop = 0
WHILE @loop < LEN(@invalididentifierscars)
BEGIN
SET @loop = @loop + 1
SET @DimTableName = REPLACE(@DimTableName,SUBSTRING (@invalididentifierscars,@loop,1),’_’)
SET @DimValueTableName = REPLACE(@DimValueTableName,SUBSTRING (@invalididentifierscars,@loop,1),’_’)
END
SET @varSQL = ‘USE [‘ + DB_NAME() + ‘]; INSERT INTO #TmpLedgerEntryDimCombinations SELECT DISTINCT ”’ + @CompanyName + ”’,[Table ID],O.[Name],L.[Dimension Code],[Global Dimension No_]
FROM [dbo].[‘ + @DimTableName + ‘] L,[dbo].[‘ + @DimValueTableName + ‘] D, [dbo].[Object] O
WHERE D.[Dimension Code] = L.[Dimension Code] AND [Global Dimension No_] > 0 AND O.[Type] = 0 AND O.[ID] = [Table ID]’
EXEC (@varSQL)
FETCH NEXT FROM @getCompanyNames INTO @CompanyName
END
CLOSE @getCompanyNames
DEALLOCATE @getCompanyNames

DECLARE @TableID int
DECLARE @TableName varchar(50)
DECLARE @GlobalDimCode varchar(20)
DECLARE @GlobalDimNo int
DECLARE @DimFieldName varchar(50)
DECLARE @DimFixes CURSOR
SET @DimFixes = CURSOR FOR SELECT CompanyName, TableID, TableName, GlobalDimCode, GlobalDimNo FROM #TmpLedgerEntryDimCombinations
OPEN @DimFixes
FETCH NEXT FROM @DimFixes INTO @CompanyName, @TableID, @TableName, @GlobalDimCode, @GlobalDimNo
WHILE @@FETCH_STATUS = 0
BEGIN
SET @loop = 0
WHILE @loop < LEN(@invalididentifierscars)
BEGIN
SET @loop = @loop + 1
SET @CompanyName = REPLACE(@CompanyName,SUBSTRING (@invalididentifierscars,@loop,1),’_’)
SET @TableName = REPLACE(@TableName,SUBSTRING (@invalididentifierscars,@loop,1),’_’)
END
SET @DimFieldName = ‘[Global Dimension ‘ + CAST(@GlobalDimNo as varchar(1)) + ‘ Code]’
SET @varSQL = ‘DELETE FROM [‘ + @CompanyName + ‘$Ledger Entry Dimension]
WHERE [Table ID] = ‘ + CAST(@TableID as varchar(20)) + ‘ AND [Dimension Code] = ”’ + @GlobalDimCode + ””
EXEC(@varSQL)
SET @varSQL = ‘INSERT INTO [‘ + @CompanyName + ‘$Ledger Entry Dimension] ([Table ID],[Entry No_],[Dimension Code],[Dimension Value Code])
SELECT ‘ + CAST(@TableID as varchar(20)) + ‘,[Entry No_],”’ + @GlobalDimCode + ”’,’ + @DimFieldName + ‘
FROM [dbo].[‘ + @CompanyName + ‘$’ + @TableName + ‘] E
WHERE ‘ + @DimFieldName + ‘ <> ””’
EXEC(@varSQL)
FETCH NEXT FROM @DimFixes INTO @CompanyName, @TableID, @TableName, @GlobalDimCode, @GlobalDimNo
END
CLOSE @DimFixes;
DEALLOCATE @DimFixes;

DROP TABLE #TmpLedgerEntryDimCombinations
GO

[/code]

Reading through this you should see that the basic part of the two scripts are similar and could be used to build further testing and for other dimension tables.  It is important that all the testing should be done before upgrading to NAV 2013 or NAv 2013 R2.

There are a lot of tables you will need to consider and do a data check for.

DimensionTables

Some of you have most likely written some kind of dimension test.  Perhaps you can also share them here ?

NAV Service Startup Type in PowerShell scripting

I have spent some time creating PowerShell scripts to solve all my installation and upgrade tasks.  I am now able to install Dynamics NAV on both local and remote computers.  I can install databases, services, clickonce distribution and web clients.  And my final task last week was to be able to install knowledge base package in a few minutes that upgrades services, client, webclient and clickonce.  Likely to create a blog about that later.

As a part of this process I have created several PowerShell scripts and functions.  I will find a time to share this with you over the next weeks.

For now I would like to share one function.

When you install a NAV Service Instance from the DVD the service startup is set to “Automatic (Delayed Start)”.

NAVServiceStartupType

There is a reason for this.  I have seen servers unable to finish the startup process if the service startup type is set to “Automatic” only.  There is a period of time needed before the service can start after the computer startup.

The problem I am facing with my scripts is that the command New-NAVServerInstance will leave the service in “Automatic” startup mode.  I wanted to change this so I created a function.

[code lang=”powershell”]# Set Service Startup Mode to Automatic (delayed)
function Set-ServiceStartupModeRemotely
{
[CmdletBinding()]
param (
[parameter(Mandatory=$true)]
[string] $ServiceInstance,

[parameter(Mandatory=$true)]
[System.Management.Automation.Runspaces.PSSession] $Session
)
PROCESS {

Invoke-Command -Session $Session -ScriptBlock {
param([string] $ServiceInstance, [string] $verbosePreference)
$VerbosePreference = $verbosePreference
Set-ExecutionPolicy -ExecutionPolicy RemoteSigned
$Service = ‘MicrosoftDynamicsNavServer$’ + $ServiceInstance
$Computer = ‘LOCALHOST’
$command = ‘sc.exe \\$Computer config "$Service" start= delayed-auto’
$Output = Invoke-Expression -Command $Command -ErrorAction Stop
if($LASTEXITCODE -ne 0){
Write-Host "$Computer : Failed to set $Service to delayed start.
More details: $Output" -foregroundcolor red
} else {
Write-Host "$Computer : Successfully changed $Service service
to delayed start" -foregroundcolor green
}

} -ArgumentList
$ServiceInstance,

$VerbosePreference

return $ManifestList
}
}
Export-ModuleMember -Function Set-ServiceStartupModeRemotely[/code]

I added this function to my New-Instance script and call the function after the service has started.

WARNING: Waiting for service ‘Microsoft Dynamics NAV Server [NAV71_KAPPI] (MicrosoftDynamicsNavServer$NAV71_KAPPI)’ to start…

LOCALHOST : Successfully changed MicrosoftDynamicsNavServer$NAV71_KAPPI service to delayed start

Add-ins Downloaded and Installed with NAV C/AL

I was watching Vjeko’s lecture in NAVTechDays 2013.  He gave me an idea on the add-in installation.  Thank you Vjeko.

It is now finished and time to share this with you all.  The basic function Codeunit is used to handle the downloading and installing of the add-in.  In the Add-on Setup Page I put an action to trigger the Add-ins validation.

CheckAddIns

This function will call the CheckAddins trigger on the Setup Table.  There I have a simple code

[code]
IF NOT CANLOADTYPE(HardwareHubProxy) THEN
AddinMgt.DownloadAnInstallServerAddIn(
‘HardwareHubProxy.dll’,
‘https://dl.dropboxusercontent.com/u/33900811/NAV/Add-Ins/Server/Advania/HardwareHubProxy.dll’,
Text002);[/code]

That will check if the add-in is installed and if not call a trigger in the add-ins function Codeunit to install the add-in.  This works both for the client and server.

In this example I am installing the HardwareHubProxy.dll that is required on the server to use the Hardware Hub.  Text002 is ‘ENU=Connection to the Advania Hardware Hub;ISL=Tenging við vélbúnaðargátt Advania’.

When installing the add-in I begin by downloading it into a Base64 string.  This is always done on the server.  The add-in is then saved to a temporary file, on the server for a server add-in and on the client for a client add-in.

Next I create a command file that is used to copy the temporary file to the add-ins folder.  This command file is then executed.  Before execution there is a check to see if the current user is able to write to the add-in directly.  If so, then the add-in is copied to the add-ins folder and the process finishes.

If the service user is not able to write to the server add-ins folder the user will get a save-as dialog and is asked to copy the file to the server add-ins folder.  In all my tests the server has been able to save the add-in without problems.

By default the running client will not be able to write the add-in to the client add-ins folder.  If this is the case then a confirmation dialog will be prompted as the copy function is running in an elevated access mode.

AddInFunctions

Could not load type “. error in DotNet object

As often a solution to a strange error is so simple when you know it.  When I copied my add-ins to a  new computer I was not able compile the objects that included DotNet add-ins.

CouldNotLoadType

As this happened when I was installing NAV 2013 R2 for the first time my first thought was that this was something that had changed between versions and I sent a support request to Microsoft.

Mohamad Vajid and Duilio Tacconi took the case and tried to reproduce the problem without success.  Yesterday we took a look at the problem together and the solution came to our attention.

The Add-in was copied from a web site and because of that the file was blocked by the server operating system.

UnblockDLL

Then today as I was creating a new website with files that I downloaded from the web I had the same problem.  That will be the last time.

After you unblock the file, recheck to see if that worked.  If the file is in a protected folder you might need to drag it to your desktop to unblock it and back again.

Thank you Mohamad and Duilio for the assistance.

Delete out-of-license table data

In an email exchange between a few NAV developers, we where discussing how to delete or modify data from read-only tables or tables that are not included in the customer license.

In the Classic Client the consultant was able to change to a partner license to make data changes.  Since NAV 2013 tables are opened through the server instance and the only way to have tables opened with the partner license is to upload the partner license to the database and restart the server instance.  That is not a best practice method but a possible way if you have a dedicated developement server instance where no user is connected.

If that is the case you can use PowerShell to upload the license so it will only work on this service instance.

[code lang=”powershell”]Import-Module ‘C:\Program Files\Microsoft Dynamics NAV\71\Service\NavAdminTool.ps1’
Import-NAVServerLicense -ServerInstance DynamicsNAV71 -LicenseFile MyLicenseFile.flf[/code]

If a user server instance is restarted the customer will be running the partner license and that is a dangerous thing. Make sure that you have the correct customer license at hand before doing this and make sure that you upload that license back with the same PowerShell commands as soon as your data changes have been done.

Another way to solve this is with programming.  You either create a Page, Report or a Codeunit to modify the data.  If the field is read only a Report or a Codeunit is needed to modify the data with code.  Other fields can be edited with a Page.  To enable modification with the customer license the object needs to have special permissions.

ObjectPermission

Then one of the group mentioned another problem.  If a customer is started with the CRONUS data then you will have data in tables that are not licensed.  In that case you can’t change this with the customer license.  Here the first method of change to a partner license is available but I suggest another method.

DeleteOutOfLicenseData

Here is a Report that will loop through Table Information.  Running on the customer license it will find all tables that are out of license containing data and give you a SQL script to execute that will delete all data from these tables.

[code lang=”sql”]USE [CRONUS International Ltd]
GO
DELETE FROM dbo.[CRONUS International Ltd$Payroll Cue]
GO
DELETE FROM dbo.[CRONUS International Ltd$Banking Card Process Setup]
GO
DELETE FROM dbo.[CRONUS International Ltd$Banking Card Type]
GO
DELETE FROM dbo.[CRONUS International Ltd$Banking Collection Agent]
GO
[/code]

I hope this will answer these questions and give you a simpler way to start a new company with existing data, or just check if you have any data that is not included in the customer license.

The Report – Create SQL Delete Script – is attached.  This version is for NAV 2013 R2 but the text version should also work in NAV 2013.

DeleteScriptNAV2013R2

Creating a desktop shortcut for Dynamics NAV 2013 (R2)

If you are a user with multiple companies in your NAV database, or even with multiple databases you might be used to switching between companies or servers.

I  have been asked to create desktop shortcuts for the users.  What if the user is acting as a sales person in one company and as an accountant in another.  Then there is this confirmation dialog that always requires us to approve the server connection.  This is a process that takes time.

So, why don’t we let the user do this by him self ?

I wanted to so I created the code to make this happen.  First I needed to add a function to Codeunit 9500, Debugger Management.

[code] PROCEDURE GetConfigDetails@50000(VAR ServerComputerName@50002 : Text;VAR ServerInstance@50001 : Text;VAR ServerPort@50000 : Text;VAR ConfigFileName@50003 : Text);
BEGIN
IF ISNULL(Config) THEN
Config := Config.Instance;
ServerComputerName := Config.GetStringSetting(‘Server’);
ServerInstance := Config.GetStringSetting(‘ServerInstance’);
ServerPort := Config.GetStringSetting(‘ClientServicesPort’);
IF Config.Configuration.HasFile THEN
ConfigFileName := Config.Configuration.FilePath;
END;

[/code]

and then a small Codeunit to do the magic.  And guess what, no confirmation dialogs.

[code]OBJECT Codeunit 50001 Create Desktop Shortcut
{
OBJECT-PROPERTIES
{
Date=10.10.13;
Time=15:23:43;
Modified=Yes;
Version List=Dynamics.is;
}
PROPERTIES
{
OnRun=VAR
SpecialFolder@50006 : DotNet "’mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Environment+SpecialFolder" RUNONCLIENT;
Environment@50007 : DotNet "’mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Environment" RUNONCLIENT;
httpUtility@50001 : DotNet "’System.Web, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a’.System.Web.HttpUtility";
UrlString@50005 : Text;
ServerComputerName@50004 : Text;
ServerInstance@50003 : Text;
ServerPort@50002 : Text;
ConfigFileName@50000 : Text;
Arguments@50008 : Text;
LinkName@50009 : Text;
BEGIN
DebugMgt.GetConfigDetails(ServerComputerName,ServerInstance,ServerPort,ConfigFileName);
UrlString := STRSUBSTNO(‘DynamicsNAV://%1:%2/%3/%4/’,ServerComputerName,ServerPort,ServerInstance,httpUtility.UrlPathEncode(COMPANYNAME));

CREATE(WshShell,TRUE,TRUE);
LinkName := Environment.GetFolderPath(SpecialFolder.DesktopDirectory) + ‘\’ + COMPANYNAME;
IF ConfigFileName <> ” THEN
Arguments := STRSUBSTNO(‘ -settings:"%1" %2’,ConfigFileName,UrlString)
ELSE
Arguments := STRSUBSTNO(‘ -protocolhandler "%1"’,UrlString);

ActiveSession.SETRANGE("Session ID",SESSIONID);
ActiveSession.FINDFIRST;
Personalization.GET(ActiveSession."User SID");
IF Personalization."Language ID" <> 0 THEN
Arguments := STRSUBSTNO(‘ -language:%1 ‘,Personalization."Language ID") + Arguments;
IF Personalization."Profile ID" <> ” THEN BEGIN
Profile.GET(Personalization."Profile ID");
Arguments := STRSUBSTNO(‘ -profile:"%1" ‘,Personalization."Profile ID") + Arguments;
LinkName := LinkName + ‘ ‘ + Profile.Description;
END;

WshShortcut := WshShell.CreateShortcut(LinkName + ‘.lnk’);
WshShortcut.TargetPath := GetClientFileName;
WshShortcut.Arguments := Arguments;
WshShortcut.Description := COMPANYNAME;
WshShortcut.WorkingDirectory := Path.GetDirectoryName(GetClientFileName);
WshShortcut.Save;
CLEAR(WshShortcut);
CLEAR(WshShell);
END;

}
CODE
{
VAR
WshShortcut@50005 : Automation "{F935DC20-1CF0-11D0-ADB9-00C04FD58A0B} 1.0:{A548B8E4-51D5-4661-8824-DAA1D893DFB2}:’Windows Script Host Object Model’.WshShortcut";
WshShell@50000 : Automation "{F935DC20-1CF0-11D0-ADB9-00C04FD58A0B} 1.0:{72C24DD5-D70A-438B-8A42-98424B88AFB8}:’Windows Script Host Object Model’.WshShell";
Path@50001 : DotNet "’mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.IO.Path" RUNONCLIENT;
Personalization@50003 : Record 2000000073;
ActiveSession@50004 : Record 2000000110;
Profile@50006 : Record 2000000072;
DebugMgt@50002 : Codeunit 9500;

PROCEDURE GetClientFileName@50018() ClientFileName : Text;
VAR
Assembly@50006 : DotNet "’mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Reflection.Assembly" RUNONCLIENT;
BEGIN
Assembly := Assembly.GetCallingAssembly;
ClientFileName := Path.GetDirectoryName(Assembly.Location) + ‘\Microsoft.Dynamics.Nav.Client.exe’;
END;

BEGIN
END.
}
}

[/code]

By running this Codeunit a shortcut will be created on the user desktop. The shortcut name will be the company name and the profile description. I guess it is not to hard to code the making of shortcuts for the developement environment but I leave that to you.

Create Desktop Shortcut

Performance in data upgrade – lets speed things up

I am working on an interesting issue.  How to execute data upgrade from NAV 4.0SP3 to NAV 2013 on a 300GB database in just one weekend.  My first go at this task was during the summer and my measures tell me that I would not make it in a week.

The database is heavily modified and I need to move a lot of data to a temporary tables and then back in the end of the data upgrade having reached NAV 2013.  Some fields need to be cleared and in some cases a whole table needs to be emptied and even removed.

The standard code in NAV 4.0SP3 Step 1 is to loop through all entries in the table and initialize the fields, like here

[code]
WITH PurchLineArchive DO
IF StateIndicator.UpdateTable(TABLENAME) THEN BEGIN
TimeLog.TimeLogInsert(TimeLog,TABLENAME,TRUE);
IF FINDSET(TRUE) THEN
REPEAT
StateIndicator.Update;
"Phase Code" := ”;
"Task Code" := ”;
"Step Code" := ”;
MODIFY;
UNTIL NEXT = 0;
TimeLog.TimeLogInsert(TimeLog,TABLENAME,FALSE);
StateIndicator.EndUpdateTable(TABLENAME);
END;[/code]

A faster way is to use MODIFYALL, like here

[code]WITH ProdOrderComponent DO
IF StateIndicator.UpdateTable(TABLENAME) THEN BEGIN
TimeLog.TimeLogInsert(TimeLog,TABLENAME,TRUE);
MODIFYALL("Due Date-Time",0);
TimeLog.TimeLogInsert(TimeLog,TABLENAME,FALSE);
StateIndicator.EndUpdateTable(TABLENAME);
END;[/code]

But I guess it depends on how many fields needs to be initialized.  In the first example the whole table is read record by record, handled in NAV and modified.  In the second example the SQL server does all the job and nothing is handled in NAV.  However, in the second example the SQL server needs to read and modify the whole table for each field that has to be initialized.  So there is a threshold where it is better to handle every record and modify instead of using MODIFYALL for all fields.

In other cases data has to me copied to a temporary table to use later in the upgrade.  This happens if the table structure is changed, data is used in another tables or even if the field has new number or new data type.  Here is an example for the Customer.

[code]WITH Customer DO
IF StateIndicator.UpdateTable(TABLENAME) THEN BEGIN
TimeLog.TimeLogInsert(TimeLog,TABLENAME,TRUE);
IF FINDSET(TRUE) THEN
REPEAT
StateIndicator.Update;
TempCustomer.INIT;
TempCustomer.TRANSFERFIELDS(Customer);
TempCustomer.INSERT;

"Our Account No." := ”;
"Mobile Phone" := ”;
"EAN-Id.No." := ”;
"X-400 address" := ”;
"Reports Delivery" := 0;
"Handling fee" := FALSE;
"Shipment request" := ”;
"Distribution Phone No." := ”;
"Distribution GSM No." := ”;
"Distribution E-mail" := ”;
"Distribution Contact" := ”;
"Billing Blocking Code" := ”;
"Responsable To Customer" := ”;
"Order Change Notification" := FALSE;
"Statement Address" := ”;
"Statement Post Code" := ”;
"Statement City" := ”;
"Address of Consignee 1" := ”;
"Address of Consignee 2" := ”;
"Address of Consignee 3" := ”;
"Address of Consignee 4" := ”;
"Pooled Accounts Collection" := ”;
"Block Order Join" := FALSE;
"Missing Order Notification SMS" := ”;
"Missing Order Notif. SMS 2" := ”;
"Social Security No." := ”;
"Electronic Partner – Old" := FALSE;
"Electronic Partner" := FALSE;
"Outstanding POS Sales" := 0;
"Homestead No." := ”;
"Churn No." := 0;
"Notification Process Code" := ”;
"Queue Priority" := "Queue Priority"::"Very Low";
MODIFY;
UNTIL NEXT = 0;
TimeLog.TimeLogInsert(TimeLog,TABLENAME,FALSE);
StateIndicator.EndUpdateTable(TABLENAME);
END;
[/code]

The first thing I did to speed in my work was to build an Excel document. With the help of Excel I was able to open the table design in NAV, copy all fields that needed to be clear into Excel and have Excel give me the code to initialize the fields.  This saves me some work when customizing the upgrade batch.  A copy of the Excel document will be in the attachment in the bottom of this post.

With the change in dimensions Microsoft faced the fact that the traditional way of data upgrade would not work for the clients.  It would simply be to time consuming.  Their solution was to change parts of the code to build SQL statements and execute them directly on the SQL server.  With this method they where able to reduce the time in their example from 24 hours down to 1.5 hours.

With this in mind I decided to do the same for the data upgrade.  I created an upgrade SQL helper CODEUNIT based on ADO automation to use in the earlier versions.  I will attach it to the post but here is the code

[code]OBJECT Codeunit 99980 Upgrade SQL Helper
{
OBJECT-PROPERTIES
{
Date=07.09.13;
Time=18:02:42;
Modified=Yes;
Version List=UPGW16.00.10,MS;
}
PROPERTIES
{
OnRun=BEGIN
END;

}
CODE
{
VAR
ADOConnection@1100408000 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000514-0000-0010-8000-00AA006D2EA4}:’Microsoft ActiveX Data Objects 2.8 Library’.Connection";
ADOStream@1100408004 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000566-0000-0010-8000-00AA006D2EA4}:’Microsoft ActiveX Data Objects 2.8 Library’.Stream";
WShell@1100408007 : Automation "{F935DC20-1CF0-11D0-ADB9-00C04FD58A0B} 1.0:{72C24DD5-D70A-438B-8A42-98424B88AFB8}:’Windows Script Host Object Model’.WshShell";
SystemFilesSystem@1100408014 : Automation "{F935DC20-1CF0-11D0-ADB9-00C04FD58A0B} 1.0:{0D43FE01-F093-11CF-8940-00A0C9054228}:’Windows Script Host Object Model’.FileSystemObject";
SystemFile@1100408013 : Automation "{F935DC20-1CF0-11D0-ADB9-00C04FD58A0B} 1.0:{C7C3F5B5-88A3-11D0-ABCB-00A0C90FFFC0}:’Windows Script Host Object Model’.File";
SystemTextStream@1100408012 : Automation "{F935DC20-1CF0-11D0-ADB9-00C04FD58A0B} 1.0:{0BB02EC0-EF49-11CF-8940-00A0C9054228}:’Windows Script Host Object Model’.TextStream";
MyDatabase@1100408011 : Record 2000000048;
MyServer@1100408010 : Record 2000000047;
dbpropFromStr@1100408003 : Text[30];
dbpropToStr@1100408002 : Text[30];
CommandFile@1100408008 : Text[1024];
ResultFile@1100408009 : Text[1024];
CrLf@1100408015 : Text[2];
dbpropertyInitialized@1100408001 : Boolean;
Connected@1100408005 : Boolean;
Text007@1100408006 : TextConst ‘ENU=Microsoft ADO not found.;ISL=Microsoft ADO finnst ekki.’;

PROCEDURE CreateSQL@1100408007(Qry@1100408000 : Text[1024]);
BEGIN
CLEAR(ADOStream);
CREATE(ADOStream,TRUE,TRUE);
ADOStream.Type := 2;
ADOStream.Open;
ADOStream.WriteText(‘SET ANSI_NULLS ON;’ + CrLf);
ADOStream.WriteText(‘SET QUOTED_IDENTIFIER ON;’ + CrLf);
ADOStream.WriteText(Qry + CrLf);
END;

PROCEDURE AddToSQL@1100408012(Qry@1100408000 : Text[1024]);
BEGIN
ADOStream.WriteText(Qry + CrLf);
END;

PROCEDURE ExecuteSQL@1100408015();
VAR
FileContent@1100408003 : Text[1024];
WinStyle@1100408002 : Integer;
FileSize@1100408004 : Integer;
Wait@1100408001 : Boolean;
Success@1100408000 : Boolean;
BEGIN
Connect;
ADOStream.WriteText(‘;’);
ADOStream.SaveToFile(CommandFile,2);
ADOStream.Close;
Wait := TRUE;
WShell.Run(
STRSUBSTNO(
‘sqlcmd -E -S %1 -d %2 -i %3 -o %4′,
MyServer."Server Name",
MyDatabase."Database Name",
CommandFile,
ResultFile),WinStyle,Wait);
SystemFile := SystemFilesSystem.GetFile(ResultFile);
FileSize := SystemFile.Size;
IF FileSize > 0 THEN BEGIN
SystemTextStream := SystemFile.OpenAsTextStream;
FileContent := SystemTextStream.ReadAll;
IF STRPOS(FileContent,’affected’) = 0 THEN
ERROR(FileContent);
END;
END;

PROCEDURE StringConvert@2(NAVName@1000 : Text[30]) : Text[30];
VAR
i@1001 : Integer;
BEGIN
IF NOT dbpropertyInitialized THEN BEGIN
dbpropertyInitialized := TRUE;
IF GetDBPropertyField(‘convertidentifiers’) = ‘1’ THEN BEGIN
dbpropFromStr := GetDBPropertyField(‘invalididentifierchars’);
FOR i := 1 TO STRLEN(dbpropFromStr) DO
dbpropToStr += ‘_’;
END;
END;

EXIT(CONVERTSTR(NAVName,dbpropFromStr,dbpropToStr));
END;

PROCEDURE GetFullTableNameSQL@8(TableName@1000 : Text[30]) : Text[1024];
BEGIN
EXIT(STRSUBSTNO(‘[%1$%2]’,GetCompanyNameSQL,GetTableNameSQL(TableName)));
END;

PROCEDURE GetCompanyNameSQL@10() : Text[1024];
BEGIN
EXIT(StringConvert(COMPANYNAME));
END;

PROCEDURE GetTableNameSQL@7(TableName@1000 : Text[30]) : Text[1024];
BEGIN
EXIT(StringConvert(TableName));
END;

PROCEDURE GetFieldNameSQL@1100408001(FieldNameNAV@1100408000 : Text[30]) FieldNameSQL : Text[1024];
BEGIN
FieldNameSQL := ‘[‘ + StringConvert(FieldNameNAV) + ‘]’;
END;

PROCEDURE GetFieldClearSQL@1100408008(FieldNameNAV@1100408000 : Text[30];NewValue@1100408001 : Text[250];LastValue@1100408002 : Boolean) : Text[1024];
BEGIN
IF LastValue THEN
EXIT(GetFieldNameSQL(FieldNameNAV) + ‘ = ‘ + NewValue + ‘ ‘)
ELSE
EXIT(GetFieldNameSQL(FieldNameNAV) + ‘ = ‘ + NewValue + ‘,’)
END;

LOCAL PROCEDURE GetDBPropertyField@11(FieldName@1000 : Text[30]) : Text[30];
VAR
ADORecordset@1100408002 : Automation "{00000300-0000-0010-8000-00AA006D2EA4} 2.8:{00000535-0000-0010-8000-00AA006D2EA4}:’Microsoft ActiveX Data Objects Recordset 2.8 Library’.Recordset";
ADOFields@1100408001 : Automation "{00000300-0000-0010-8000-00AA006D2EA4} 2.8:{00000564-0000-0010-8000-00AA006D2EA4}:’Microsoft ActiveX Data Objects Recordset 2.8 Library’.Fields";
ADOField@1100408000 : Automation "{00000300-0000-0010-8000-00AA006D2EA4} 2.8:{00000569-0000-0010-8000-00AA006D2EA4}:’Microsoft ActiveX Data Objects Recordset 2.8 Library’.Field";
Qry@1004 : Text[1024];
i@1001 : Integer;
FieldValue@1005 : Text[1024];
BEGIN
IF FieldName = ” THEN
EXIT(”);

Qry := STRSUBSTNO(‘select top 1 [%1] from [$ndo$dbproperty]’,FieldName);

Connect;
CREATE(ADORecordset,TRUE,TRUE);
ADORecordset.Open(Qry,ADOConnection);
ADORecordset.MoveFirst;
ADOFields := ADORecordset.Fields;
FieldValue := FORMAT(ADOFields.Item(FieldName).Value);
EXIT(FieldValue);
END;

PROCEDURE GetNullDate@1100408002() : Text[1024];
BEGIN
EXIT(”’1753-01-01 00:00:00.000”’);
END;

PROCEDURE GetNullTime@1100408003() : Text[1024];
BEGIN
EXIT(”’1753-01-01 00:00:00.000”’);
END;

PROCEDURE GetNullDateTime@1100408006() : Text[1024];
BEGIN
EXIT(”’1753-01-01 00:00:00.000”’);
END;

PROCEDURE GetNullString@1100408004() : Text[1024];
BEGIN
EXIT(”””);
END;

PROCEDURE GetNullGuid@1100408014() : Text[1024];
BEGIN
EXIT(”’00000000-0000-0000-0000-000000000000”’);
END;

PROCEDURE GetNullInt@1100408005() : Text[1024];
BEGIN
EXIT(‘0’);
END;

PROCEDURE GetNull@1100408013() : Text[1024];
BEGIN
EXIT(‘NULL’);
END;

LOCAL PROCEDURE Connect@1100408000();
BEGIN
IF Connected THEN EXIT;

MyServer.SETRANGE("My Server",TRUE);
MyServer.FINDFIRST;
MyDatabase.SETRANGE("My Database",TRUE);
MyDatabase.FINDFIRST;

CLEAR(ADOConnection);
IF NOT CREATE(ADOConnection,TRUE,TRUE) THEN
ERROR(Text007);

IF ADOConnection.State = 1 THEN
ADOConnection.Close;

ADOConnection.Open(
STRSUBSTNO(
‘Provider=SQLOLEDB.1;Initial Catalog=%2;Data Source=%1;Trusted_Connection=yes;’,
MyServer."Server Name",
MyDatabase."Database Name"));

Connected := ADOConnection.State = 1;
CommandFile := ENVIRON(‘TEMP’) + ‘\Cmd.sql’;
ResultFile := ENVIRON(‘TEMP’) + ‘\Result.txt’;
CrLf[1] := 13;
CrLf[2] := 10;

CLEAR(WShell);
CREATE(WShell,TRUE,TRUE);

CLEAR(SystemFilesSystem);
CREATE(SystemFilesSystem,TRUE,TRUE);
END;

EVENT ADOConnection@1100408000::InfoMessage@0(pError@1100408002 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000500-0000-0010-8000-00AA006D2EA4}:’Microsoft ActiveX Data Objects 2.8 Library’.Error";adStatus@1100408001 : Integer;pConnection@1100408000 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000550-0000-0010-8000-00AA006D2EA4}:’Microsoft ActiveX Data Objects 2.8 Library’._Connection");
BEGIN
END;

EVENT ADOConnection@1100408000::BeginTransComplete@1(TransactionLevel@1100408003 : Integer;pError@1100408002 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000500-0000-0010-8000-00AA006D2EA4}:’Microsoft ActiveX Data Objects 2.8 Library’.Error";adStatus@1100408001 : Integer;pConnection@1100408000 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000550-0000-0010-8000-00AA006D2EA4}:’Microsoft ActiveX Data Objects 2.8 Library’._Connection");
BEGIN
END;

EVENT ADOConnection@1100408000::CommitTransComplete@3(pError@1100408002 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000500-0000-0010-8000-00AA006D2EA4}:’Microsoft ActiveX Data Objects 2.8 Library’.Error";adStatus@1100408001 : Integer;pConnection@1100408000 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000550-0000-0010-8000-00AA006D2EA4}:’Microsoft ActiveX Data Objects 2.8 Library’._Connection");
BEGIN
END;

EVENT ADOConnection@1100408000::RollbackTransComplete@2(pError@1100408002 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000500-0000-0010-8000-00AA006D2EA4}:’Microsoft ActiveX Data Objects 2.8 Library’.Error";adStatus@1100408001 : Integer;pConnection@1100408000 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000550-0000-0010-8000-00AA006D2EA4}:’Microsoft ActiveX Data Objects 2.8 Library’._Connection");
BEGIN
END;

EVENT ADOConnection@1100408000::WillExecute@4(VAR Source@1100408007 : Text[1024];CursorType@1100408006 : Integer;LockType@1100408005 : Integer;VAR Options@1100408004 : Integer;adStatus@1100408003 : Integer;pCommand@1100408002 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{B08400BD-F9D1-4D02-B856-71D5DBA123E9}:’Microsoft ActiveX Data Objects 2.8 Library’._Command";pRecordset@1100408001 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000556-0000-0010-8000-00AA006D2EA4}:’Microsoft ActiveX Data Objects 2.8 Library’._Recordset";pConnection@1100408000 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000550-0000-0010-8000-00AA006D2EA4}:’Microsoft ActiveX Data Objects 2.8 Library’._Connection");
BEGIN
END;

EVENT ADOConnection@1100408000::ExecuteComplete@5(RecordsAffected@1100408005 : Integer;pError@1100408004 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000500-0000-0010-8000-00AA006D2EA4}:’Microsoft ActiveX Data Objects 2.8 Library’.Error";adStatus@1100408003 : Integer;pCommand@1100408002 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{B08400BD-F9D1-4D02-B856-71D5DBA123E9}:’Microsoft ActiveX Data Objects 2.8 Library’._Command";pRecordset@1100408001 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000556-0000-0010-8000-00AA006D2EA4}:’Microsoft ActiveX Data Objects 2.8 Library’._Recordset";pConnection@1100408000 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000550-0000-0010-8000-00AA006D2EA4}:’Microsoft ActiveX Data Objects 2.8 Library’._Connection");
BEGIN
END;

EVENT ADOConnection@1100408000::WillConnect@6(VAR ConnectionString@1100408005 : Text[1024];VAR UserID@1100408004 : Text[1024];VAR Password@1100408003 : Text[1024];VAR Options@1100408002 : Integer;adStatus@1100408001 : Integer;pConnection@1100408000 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000550-0000-0010-8000-00AA006D2EA4}:’Microsoft ActiveX Data Objects 2.8 Library’._Connection");
BEGIN
END;

EVENT ADOConnection@1100408000::ConnectComplete@7(pError@1100408002 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000500-0000-0010-8000-00AA006D2EA4}:’Microsoft ActiveX Data Objects 2.8 Library’.Error";adStatus@1100408001 : Integer;pConnection@1100408000 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000550-0000-0010-8000-00AA006D2EA4}:’Microsoft ActiveX Data Objects 2.8 Library’._Connection");
BEGIN
END;

EVENT ADOConnection@1100408000::Disconnect@8(adStatus@1100408001 : Integer;pConnection@1100408000 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000550-0000-0010-8000-00AA006D2EA4}:’Microsoft ActiveX Data Objects 2.8 Library’._Connection");
BEGIN
END;

BEGIN
END.
}
}

[/code]

I of course used some of the code Microsoft made from NAV 2013, that is the normal way to do things, but I had a problem to solve that did not exist in NAV 2013.  Some of my statements exceeded 1024 characters in length and I was unable to use ADO to execute the statement.  I had to move to sqlcmd to execute the statements.  I use ADO Stream when adding to the SQL command and then saved the content of the stream to a temporary file that is then executed by sqlcmd.  I then look at the result file to catch the errors.

Again, I used Excel to help me to build the code needed.  So, what is gained by this ?

When I need to clear fields in a table a single SQL statement clears all files in one read-modify statement.  Previously this had to be done field by field or by looping through every entry in the table.

[code]
//Delete data from obsolete fields
DBMgt.CreateSQL(STRSUBSTNO(‘UPDATE %1 SET ‘,DBMgt.GetFullTableNameSQL(TABLENAME)));
DBMgt.AddToSQL(DBMgt.GetFieldClearSQL(FIELDNAME("Pantað magn"),DBMgt.GetNullInt,FALSE));
DBMgt.AddToSQL(DBMgt.GetFieldClearSQL(FIELDNAME("Pantað í mælieiningu"),DBMgt.GetNullString,FALSE));
DBMgt.AddToSQL(DBMgt.GetFieldClearSQL(FIELDNAME(EDI),DBMgt.GetNullInt,FALSE));
DBMgt.AddToSQL(DBMgt.GetFieldClearSQL(FIELDNAME("Afgreitt magn"),DBMgt.GetNullInt,FALSE));
DBMgt.AddToSQL(DBMgt.GetFieldClearSQL(FIELDNAME("Afgreitt í mælieiningu"),DBMgt.GetNullString,FALSE));
DBMgt.AddToSQL(DBMgt.GetFieldClearSQL(FIELDNAME("Pick No."),DBMgt.GetNullString,FALSE));
DBMgt.AddToSQL(DBMgt.GetFieldClearSQL(FIELDNAME("Item Pick Group Code"),DBMgt.GetNullString,FALSE));
DBMgt.AddToSQL(DBMgt.GetFieldClearSQL(FIELDNAME("Original Order No."),DBMgt.GetNullString,FALSE));
DBMgt.AddToSQL(DBMgt.GetFieldClearSQL(FIELDNAME("Best fyrir"),DBMgt.GetNullDate,FALSE));
DBMgt.AddToSQL(DBMgt.GetFieldClearSQL(FIELDNAME("Síðasti söludagur"),DBMgt.GetNullDate,FALSE));
DBMgt.AddToSQL(DBMgt.GetFieldClearSQL(FIELDNAME("Appl.-to Job Entry"),DBMgt.GetNullInt,FALSE));
DBMgt.AddToSQL(DBMgt.GetFieldClearSQL(FIELDNAME("Phase Code"),DBMgt.GetNullString,FALSE));
DBMgt.AddToSQL(DBMgt.GetFieldClearSQL(FIELDNAME("Task Code"),DBMgt.GetNullString,FALSE));
DBMgt.AddToSQL(DBMgt.GetFieldClearSQL(FIELDNAME("Step Code"),DBMgt.GetNullString,FALSE));
DBMgt.AddToSQL(DBMgt.GetFieldClearSQL(FIELDNAME("Job Applies-to ID"),DBMgt.GetNullString,FALSE));
DBMgt.AddToSQL(DBMgt.GetFieldClearSQL(FIELDNAME("Apply and Close (Job)"),DBMgt.GetNullInt,TRUE));
DBMgt.ExecuteSQL;[/code]

Similarly I use a single SQL statement to copy all needed fields from the source table to the temporary table.

[code]
// Copy Data to Temporary Tables
DBMgt.CreateSQL(STRSUBSTNO(‘INSERT INTO %1 (‘,DBMgt.GetFullTableNameSQL(TempSalesShipmentLine.TABLENAME)));
DBMgt.AddToSQL(DBMgt.GetFieldNameSQL(FIELDNAME("Document No.")) + ‘ , ‘ );
DBMgt.AddToSQL(DBMgt.GetFieldNameSQL(FIELDNAME("Line No.")) + ‘ , ‘ );
DBMgt.AddToSQL(DBMgt.GetFieldNameSQL(FIELDNAME("Pantað magn")) + ‘ , ‘ );
DBMgt.AddToSQL(DBMgt.GetFieldNameSQL(FIELDNAME("Pantað í mælieiningu")) + ‘ , ‘ );
DBMgt.AddToSQL(DBMgt.GetFieldNameSQL(FIELDNAME(EDI)) + ‘ , ‘ );
DBMgt.AddToSQL(DBMgt.GetFieldNameSQL(FIELDNAME("Afgreitt magn")) + ‘ , ‘ );
DBMgt.AddToSQL(DBMgt.GetFieldNameSQL(FIELDNAME("Afgreitt í mælieiningu")) + ‘ , ‘ );
DBMgt.AddToSQL(DBMgt.GetFieldNameSQL(FIELDNAME("Pick No.")) + ‘ , ‘ );
DBMgt.AddToSQL(DBMgt.GetFieldNameSQL(FIELDNAME("Item Pick Group Code")) + ‘ , ‘ );
DBMgt.AddToSQL(DBMgt.GetFieldNameSQL(FIELDNAME("Original Order No.")) + ‘ , ‘ );
DBMgt.AddToSQL(DBMgt.GetFieldNameSQL(FIELDNAME("Best fyrir")) + ‘ , ‘ );
DBMgt.AddToSQL(DBMgt.GetFieldNameSQL(FIELDNAME("Síðasti söludagur")) + ‘ ‘ );
DBMgt.AddToSQL(‘) SELECT ‘);
DBMgt.AddToSQL(DBMgt.GetFieldNameSQL(FIELDNAME("Document No.")) + ‘ , ‘ );
DBMgt.AddToSQL(DBMgt.GetFieldNameSQL(FIELDNAME("Line No.")) + ‘ , ‘ );
DBMgt.AddToSQL(DBMgt.GetFieldNameSQL(FIELDNAME("Pantað magn")) + ‘ , ‘ );
DBMgt.AddToSQL(DBMgt.GetFieldNameSQL(FIELDNAME("Pantað í mælieiningu")) + ‘ , ‘ );
DBMgt.AddToSQL(DBMgt.GetFieldNameSQL(FIELDNAME(EDI)) + ‘ , ‘ );
DBMgt.AddToSQL(DBMgt.GetFieldNameSQL(FIELDNAME("Afgreitt magn")) + ‘ , ‘ );
DBMgt.AddToSQL(DBMgt.GetFieldNameSQL(FIELDNAME("Afgreitt í mælieiningu")) + ‘ , ‘ );
DBMgt.AddToSQL(DBMgt.GetFieldNameSQL(FIELDNAME("Pick No.")) + ‘ , ‘ );
DBMgt.AddToSQL(DBMgt.GetFieldNameSQL(FIELDNAME("Item Pick Group Code")) + ‘ , ‘ );
DBMgt.AddToSQL(DBMgt.GetFieldNameSQL(FIELDNAME("Original Order No.")) + ‘ , ‘ );
DBMgt.AddToSQL(DBMgt.GetFieldNameSQL(FIELDNAME("Best fyrir")) + ‘ , ‘ );
DBMgt.AddToSQL(DBMgt.GetFieldNameSQL(FIELDNAME("Síðasti söludagur")) + ‘ ‘ );
DBMgt.AddToSQL(STRSUBSTNO(‘FROM %1 ‘,DBMgt.GetFullTableNameSQL(TABLENAME)));
DBMgt.ExecuteSQL;[/code]

In this step I also need to delete large peaces of data. I for example am clearing big log tables in the first step of the upgrade to save time later in the process. There is a TRUNCATE TABLE SQL command that clears all data in a table and is wery quick. The TRUNCATE TABLE command works differently than the DELETE command.  If the table has SumIndexFields in any of the keys the TRUNCATE TABLE command will fail.  One option is to first disable all SumIndexFields and then use TRUNCATE TABLE and the other one is to allow NAV to delete the data from tables with SumIndexFields.  If wanting to disable all SumIndexFields an isolated command with COMMIT in the end is needed.  I found out that none of my large tables had SumIndexFields so I used this code.

[code]

TruncateTable(TableID : Integer)
IF OldObject.GET(OldObject.Type::Table,”,TableID) THEN BEGIN
Company.FINDSET;
DBMgt.CreateSQL(”);
REPEAT
RecRef.OPEN(TableID,FALSE,Company.Name);
TableKey.SETRANGE(TableNo,TableID);
TableKey.SETFILTER(SumIndexFields,'<>%1′,”);
TableKey.SETRANGE(MaintainSIFTIndex,TRUE);
TableInformation.SETRANGE("Company Name",Company.Name);
TableInformation.SETRANGE("Table No.",TableID);
IF TableInformation.ISEMPTY OR NOT TableKey.ISEMPTY THEN
RecRef.DELETEALL
ELSE
DBMgt.AddToSQL(
‘TRUNCATE TABLE [‘ +
DBMgt.StringConvert(Company.Name) +
‘$’ +
DBMgt.GetTableNameSQL(RecRef.NAME) +
‘];’);
RecRef.CLOSE;
UNTIL Company.NEXT = 0;
DBMgt.ExecuteSQL;
OldObject.DELETE;
END;[/code]

On the other end of the upgrade batch, where I want to use the data from the temporary tables I also move the code to SQL. Here I use the NAV 2013 Upgrade – SQL Mgt. CODEUNIT unchanged to execute a MERGE SQL statement.

[code]
WITH VendPaymInfo DO
IF StateIndicator.UpdateTable(TABLENAME) THEN BEGIN
TimeLog.TimeLogInsert(TimeLog,TABLENAME,TRUE);
IF NOT ISEMPTY THEN
BEGIN
SQLMgt.ExecuteSQLCommand(
STRSUBSTNO(
‘MERGE INTO [%1] T ‘ +
‘ USING [%2] S ‘ +
‘ ON T.[%3] = S.[%3] ‘ +
‘WHEN MATCHED THEN ‘ +
‘ UPDATE ‘,
SQLMgt.GetFullTableNameSQL(DATABASE::"Banking Vend. Payment Info"),
SQLMgt.GetFullTableNameSQL(DATABASE::"Temp Vend. Payment Info"),
SQLMgt.StringConvert(FIELDNAME("Entry No."))
) +
STRSUBSTNO(
‘ SET [%1] = S.[%1], ‘ +
‘ [%2] = S.[%2]; ‘,
SQLMgt.StringConvert(FIELDNAME("Bank Branch No.")),
SQLMgt.StringConvert(FIELDNAME("Bank Account No."))
)
);
SQLMgt.ExecuteSQLCommand(
STRSUBSTNO(
‘TRUNCATE TABLE [%1];’,
SQLMgt.GetFullTableNameSQL(DATABASE::"Temp Vend. Payment Info")
)
);
END;
TempVendPaymInfo.DELETEALL;
TimeLog.TimeLogInsert(TimeLog,TABLENAME,FALSE);
StateIndicator.EndUpdateTable(TABLENAME);
END;[/code]

Believe me, this is so much faster the the classic method of looping through the records, and even much faster then using the UPDATE SQL statement.  As an example, I am watching the SQL server use 3.260.000 B/sec in total disk activity with the loop-and-update method where as I saw this number go up to 230.000.000 B/sec with direct SQL statement.

Prepare to see you SQL server finally working his socks off.

Upgrade Tools

Gathering permission for users and groups

I have a solution that stores scanned and linked documents in an external database.  A URL to the document is stored in the Record Link table in NAV and attached to the relevant record.  The URL points to a web page that delivers the attached document given that the user asking for the document has permission to read the table the document belongs to.

In my latest installation I faced a new challenge; we are now using Active Directory Groups for access control.  Still I need to store the permissions in my external database for each user.  Of course it would be possible to change the permission handling in the external database and modify the ASPX web page that delivers the document, but I am a NAV programmer and if the solution is possible in NAV it will be written in NAV.

The task was to find all users that belong to a group.  The method I used is through dotnet and through the service tier.  If the service tier is not running on a domain account the dotnet execution needs to be on the client side.

The first step is to create the connection string to Active Directory.  I use “System.Net.NetworkInformation.IPGlobalProperties” dotnet object to the the current dns domain name and change the domain name from example.com to the connection string “DC=example,DC=com”.

Next step is to load all the group information from Active Directory into a temporary table.  I use table 367 as ADBuffer and load all combinations of group and user into that table.

For every group used in the access control I filter the buffer table and read all the users within that group.

This is a part of the codeunit I use in my solution and should give you a hint on how to do this in your own solution.

[code] PROCEDURE CreatePermissionBuf@1200050012(VAR PermissionBuf@1200050001 : Record 2000000005;VAR TempObject@1100408001 : Record 2000000001) : Boolean;
VAR
PermissonSet@1100408000 : Record 2000000004;
User@1005 : Record 2000000120;
AccessControl@1006 : Record 2000000053;
Permission@1008 : Record 2000000005;
TempUser@50000 : TEMPORARY Record 91;
HasPermission@1003 : Boolean;
BEGIN
PermissionBuf.DELETEALL;

WITH User DO
IF FINDSET THEN REPEAT
IF CreateTempUserIDs(User,TempUser) THEN BEGIN
PermissonSet.SETFILTER("Role ID",’SUPER’ + ‘*’);
PermissonSet.FINDSET;
REPEAT
HasPermission := AccessControl.GET("User Security ID",PermissonSet."Role ID",COMPANYNAME);
IF NOT HasPermission THEN
HasPermission := AccessControl.GET("User Security ID",PermissonSet."Role ID",”);
UNTIL (PermissonSet.NEXT = 0) OR HasPermission;

IF HasPermission THEN BEGIN
IF TempUser.FIND(‘-‘) THEN
REPEAT
TempObject.FIND(‘-‘);
REPEAT
PermissionBuf."Role ID" := TempUser."User ID";
PermissionBuf."Object Type" := PermissionBuf."Object Type"::"Table Data";
PermissionBuf."Object ID" := TempObject.ID;
IF PermissionBuf.INSERT THEN;
UNTIL TempObject.NEXT = 0;
UNTIL TempUser.NEXT = 0;
END ELSE BEGIN
TempObject.FIND(‘-‘);
REPEAT
Permission.SETRANGE("Object Type",Permission."Object Type"::"Table Data");
Permission.SETRANGE("Object ID",TempObject.ID);
Permission.SETRANGE("Read Permission",Permission."Read Permission"::Yes);
IF Permission.FINDSET THEN
REPEAT
HasPermission := AccessControl.GET("User Security ID",Permission."Role ID",COMPANYNAME);
IF NOT HasPermission THEN
HasPermission := AccessControl.GET("User Security ID",Permission."Role ID",”);
UNTIL HasPermission OR (Permission.NEXT = 0);
IF HasPermission THEN BEGIN
IF TempUser.FIND(‘-‘) THEN
REPEAT
PermissionBuf."Role ID" := TempUser."User ID";
PermissionBuf."Object Type" := PermissionBuf."Object Type"::"Table Data";
PermissionBuf."Object ID" := TempObject.ID;
IF PermissionBuf.INSERT THEN;
UNTIL TempUser.NEXT = 0;
END;
UNTIL TempObject.NEXT = 0;
END;
END;
UNTIL NEXT = 0;

TempObject.FIND(‘-‘);
REPEAT
PermissionBuf."Role ID" := SigningSetup."Database Login Name";
PermissionBuf."Object Type" := PermissionBuf."Object Type"::"Table Data";
PermissionBuf."Object ID" := TempObject.ID;
IF PermissionBuf.INSERT THEN;
UNTIL TempObject.NEXT = 0;
END;

LOCAL PROCEDURE CreateTempUserIDs@50000(FromUser@50001 : Record 2000000120;VAR TempUser@50000 : Record 91) : Boolean;
VAR
DomainName@50003 : Text;
GroupName@50004 : Text;
BEGIN
TempUser.DELETEALL;

WITH FromUser DO BEGIN
IF "Windows Security ID" = ” THEN EXIT(FALSE);
CASE "License Type" OF
"License Type"::"Windows Group":
BEGIN
DomainName := SigningTools.GetDomainName("User Name");
GroupName := SigningTools.RemoveDomainName("User Name");
GetListOfAdUsersByGroup(DomainName,GroupName,TempUser);
END;
ELSE BEGIN
TempUser.INIT;
TempUser."User ID" := SigningTools.RemoveDomainName(UPPERCASE("User Name"));
TempUser.INSERT;
END;
END;
EXIT(TRUE);
END;
END;

LOCAL PROCEDURE GetListOfAdUsersByGroup@50033(DomainName@50001 : Text;GroupName@50002 : Text;VAR TempUser@50000 : Record 91);
VAR
IPProperties@50003 : DotNet "’System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Net.NetworkInformation.IPGlobalProperties";
TextMgt@50005 : Codeunit 10000204;
ConnectionString@50014 : Text;
IPDomainName@50013 : Text;
BEGIN
ADBuffer.RESET;
IF ADBuffer.ISEMPTY THEN BEGIN
IPDomainName := IPProperties.GetIPGlobalProperties.DomainName;
WHILE STRPOS(IPDomainName,’.’) > 0 DO BEGIN
IF ConnectionString = ” THEN
ConnectionString := ‘DC=’ + TextMgt.ExtractFirstPartFromString(IPDomainName,’.’,”)
ELSE
ConnectionString += ‘,DC=’ + TextMgt.ExtractFirstPartFromString(IPDomainName,’.’,”);
END;

IF ConnectionString = ” THEN
ConnectionString := ‘DC=’ + IPDomainName
ELSE
ConnectionString += ‘,DC=’ + IPDomainName;
LoadADBuffer(ConnectionString);
END;

ADBuffer.SETRANGE(Totaling,GroupName);
IF ADBuffer.FIND(‘-‘) THEN REPEAT
TempUser.INIT;
TempUser."User ID" := ADBuffer.Name;
TempUser.INSERT;
UNTIL ADBuffer.NEXT = 0;
END;

LOCAL PROCEDURE LoadADBuffer@50003(ConnectionString@50015 : Text);
VAR
DirectoryEntry@50004 : DotNet "’System.DirectoryServices, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a’.System.DirectoryServices.DirectoryEntry";
DirectorySearcher@50003 : DotNet "’System.DirectoryServices, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a’.System.DirectoryServices.DirectorySearcher";
SearchResultCollection@50006 : DotNet "’System.DirectoryServices, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a’.System.DirectoryServices.SearchResultCollection";
SearchResult@50009 : DotNet "’System.DirectoryServices, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a’.System.DirectoryServices.SearchResult";
ResultPropertyCollection@50010 : DotNet "’System.DirectoryServices, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a’.System.DirectoryServices.ResultPropertyCollection";
ResultPropertyValueCollection@50007 : DotNet "’System.DirectoryServices, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a’.System.DirectoryServices.ResultPropertyValueCollection";
Query@50005 : Text;
KeyValue@50000 : Code[10];
SearchLoop@50008 : Integer;
PropertyLoop@50001 : Integer;
BEGIN
KeyValue := ‘00000001’;
DirectoryEntry := DirectoryEntry.DirectoryEntry(‘LDAP://’ + ConnectionString);
DirectorySearcher := DirectorySearcher.DirectorySearcher(DirectoryEntry);
Query := ‘(&(objectCategory=person)(objectClass=user)(memberOf=*))’;
DirectorySearcher.Filter := Query;
DirectorySearcher.PropertiesToLoad.Add(‘sAMAccountName’);
DirectorySearcher.PropertiesToLoad.Add(‘memberOf’);
SearchResultCollection := DirectorySearcher.FindAll;
FOR SearchLoop := 1 TO SearchResultCollection.Count DO BEGIN
SearchResult := SearchResultCollection.Item(SearchLoop – 1);
ResultPropertyCollection := SearchResult.Properties;
ADBuffer.INIT;
ADBuffer.Name := UPPERCASE(ResultPropertyCollection.Item(‘sAMAccountName’).Item(0));
FOR PropertyLoop := 1 TO ResultPropertyCollection.Item(‘memberOf’).Count DO BEGIN
ADBuffer.Code := KeyValue;
ADBuffer.Totaling := UPPERCASE(COPYSTR(SELECTSTR(1,ResultPropertyCollection.Item(‘memberOf’).Item(PropertyLoop – 1)),4));
ADBuffer.INSERT;
KeyValue := INCSTR(KeyValue);
END;
END;
END;
[/code]

Report upgrade from 2009 to 2013

I have noticed within the group of consultants and developers that the move to Visual Studio to handle report layout is quite a leap.  Not many are courageous enough to make this leap.

One of the most used tricks in the new layout is how headers and footers are handled.  To get the correct data up to the header og down to the footer a method using Visual Basic Code is used.

In our payroll system upgrade from NAV 2009 to NAV 2013 all reports had been upgraded.  However, they did not work properly and so the problem ended on my desk.

I found the problem and of course Claus had already blogged about this change of functionality between NAV 2009 and NAV 2013.

“The textbox is hidden and needs to be first thing that activated when the report is rendered. So hold that thought, it has to be hidden and at the top for Code.GetData to work. In NAV 2009 reports are rendered in Report Viewer 2008 this works like a charm, but in Report Viewer 2010 the SSRS team has been so kind to us to change the logic of how a reports is rendered. So if an textbox is hidden in RDLC 2008, the expression in this hidden textbox, is rendered at the end. So as I said the SetData textbox has to be hidden and at the top for Code.GetData to work, but now it is rendered as the last thing, and of course the result is that the fields get out of sync and the very idea using Code.GetData and Code.SetData is broken in NAV 2013 with Report Viewer 2010. Interesting thought!!! “

The first thing I needed to do was to add a single line of code to the Code.SetData function.  Go to Report Properties to solve this.

ReportProperties

The “Return True” line makes sure that the field is visible and therefore rendered at the top.

Next to change the text box with the Code.SetData function.  In NAV 2009 layout we called this function by using it as an expression and set visibility to hidden.

ReportExpression

In NAV 2013 layout clear the expression and move the Code.SetData function to the Visibility Section.

ReportVisibility

And that’s it folks…