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.

Unblock downloaded files

In the process of downloading a file from an insecure location Windows blocks the file.

I have had this bothering to often.  For example when I downloaded an add-in and hit an error.

And more recently I downloaded a knowledge base packed and installed with my PowerShell script just to find out that every file in the download had been blocked.

Once again, PowerShell came to the rescue.  Opened Windows PowerShell ISE in Administration Mode and execute the below script for the needed folders.

[code lang=”ps”]$folder = ‘C:\PUB’
#$folder = ‘C:\Program Files\Microsoft Dynamics NAV’
#$folder = ‘C:\Program Files (x86)\Microsoft Dynamics NAV’
$files = Get-ChildItem -Path $folder -Recurse
foreach ($file in $files)
{
Write-Host "Unblocking file $file…"
Unblock-File -Path $file.FullName
}
[/code]

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

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.

 

Browse for folder dialog

I have in several cases needed to allow a user to select a folder.  I was surprised to see that Microsoft did not have a browse for folder function in Codeunit 419.

I hereby suggest that Microsoft add this function to Codeunit 419.

[code] PROCEDURE BrowseForFolder@47(VAR FolderName@1000 : Text;Description@1001 : Text) : Boolean;
VAR
FolderBrowserDialog@1002 : DotNet "’System.Windows.Forms, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Windows.Forms.FolderBrowserDialog" RUNONCLIENT;
DialagResult@1003 : DotNet "’System.Windows.Forms, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Windows.Forms.DialogResult" RUNONCLIENT;
BEGIN
FolderBrowserDialog := FolderBrowserDialog.FolderBrowserDialog;
FolderBrowserDialog.Description := Description;
FolderBrowserDialog.SelectedPath := FolderName;
DialagResult := FolderBrowserDialog.ShowDialog;

IF DialagResult.CompareTo(DialagResult.OK) = 0 THEN BEGIN
FolderName := FolderBrowserDialog.SelectedPath;
EXIT(TRUE);
END;
EXIT(FALSE);
END;
[/code]

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 ?