Copy data between companies and/or databases

I am upgrading a solution from NAV 2009 to NAV 2013.  The solution was running in the Role Tailored Client only so the upgrade process was fast and easy.

I wanted to move this customized solution to another database and remove all other customizations from the database.  On the other hand I am upgrading everything in the old database except this customized solution with the standard methods.

After I had the solution upgraded and the new database ready I started to think about how I would get the data from the old database.  It is a significant amount of data so I wanted to use the native sql commands to do this.  Since I had around one hundred tables I felt it would not be practical to copy manually nor to create the copy script manually.

So, I created a batch report.  The batch creates an INSERT INTO and SELECT FROM query that is very fast.  It requires the tables to be identical in both databases.

CreateCopyScript

This batch will create a copy script that runs fast…

Few things I had to check.  When copying Blob fields from NAV 2009 to NAV 2013 I needed to change the Compressed property to No.  I also needed to check the fields to make sure that fields with AutoIncrement property set to Yes would not be included in the script.

I started with a NAV 2009 batch that is basically raw.  I made the NAV 2013 batch smarter with direct connection to the sql server.

Here are both NAV Copy for 2009 and NAV Copy 2013 (2).

Access your SQL server through dotnet interop

As the Icelandic localization for NAV 2013 is supposed to land here in April it is now time to start moving the solutions to the new platform.  One of the things that I have used are the automation variables for SQL server connection.  In NAV 2013 I plan to replace all automation variables with dotnet variables.  Here is a code that could help you replacing some of your automation variables.[code]OBJECT Codeunit 50000 SQL Mgt.
{
OBJECT-PROPERTIES
{
Date=07.03.13;
Time=14:58:29;
Modified=Yes;
Version List=Dynamics.is;
}
PROPERTIES
{
OnRun=VAR
BEGIN
END;

}
CODE
{
VAR
ClientFile@1100408005 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.IO.File" RUNONCLIENT;
ServerFile@1100408008 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.IO.File";
SQLConnection@1000000000 : DotNet "’System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Data.SqlClient.SqlConnection";
SigningSetup@1200050000 : Record 10017128;
HostSetup@1200050027 : Record 10001061;
FileMgt@1200050026 : Codeunit 419;
SigningTools@1200050006 : Codeunit 10017129;
NoSeriesMgt@1200050011 : Codeunit 396;
HasGotSigningSetup@1200050001 : Boolean;
Text001@1200050004 : TextConst ‘ENU=Table %1 does not exist;ISL=Tafla %1 er ekki fyrir hendi’;

PROCEDURE GetSigningSetup@1200050001();
BEGIN
IF NOT HasGotSigningSetup THEN BEGIN
SigningSetup.GET;
IF SigningSetup."Default Storage" = SigningSetup."Default Storage"::"Web Site" THEN BEGIN
SigningSetup.TESTFIELD("Database Server Name");
SigningSetup.TESTFIELD("Database Name");
SigningSetup.TESTFIELD("Database Table Name");
SigningSetup.TESTFIELD("Database Permission Table Name");
SigningSetup.TESTFIELD("Database Login Name");
SigningSetup.TESTFIELD("Database User Password");
SigningSetup.TESTFIELD("Link Reference Nos.");
END;
SigningTools.TryFindHostSetup(HostSetup);
HasGotSigningSetup := TRUE;
END;
END;

PROCEDURE OpenLinkDB@1200050000();
VAR
ConnectionString@1200050000 : Text[1024];
BEGIN
GetSigningSetup;

IF ISNULL(SQLConnection) THEN BEGIN
ConnectionString :=
STRSUBSTNO(‘Data Source=%1;Initial Catalog=%2;User Id=%3;Password=%4′,
SigningSetup."Database Server Name",
SigningSetup."Database Name",
SigningSetup."Database Login Name",
SigningSetup."Database User Password");

SQLConnection := SQLConnection.SqlConnection(ConnectionString);
SQLConnection.Open;
END;
END;

PROCEDURE CloseLinkDB@1200050004();
BEGIN
IF ISNULL(SQLConnection) THEN
EXIT;

IF SQLConnection.State = 1 THEN
SQLConnection.Close;
END;

PROCEDURE TableExists@1200050006(TableName@1200050000 : Text[50];ShowError@1200050001 : Boolean) : Boolean;
VAR
SQLCommand@1000000000 : DotNet "’System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Data.SqlClient.SqlCommand";
SQLReader@1000000001 : DotNet "’System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’.System.Data.SqlClient.SqlDataReader" WITHEVENTS;
Query@1200050002 : Text[1024];
BEGIN
OpenLinkDB;

Query :=
STRSUBSTNO(‘SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ”%1” AND TABLE_TYPE = ”BASE TABLE”’,
TableName);

SQLCommand := SQLCommand.SqlCommand(Query,SQLConnection);
SQLCommand.CommandTimeout(0); // wait indefinitely for the command to finish.
SQLReader := SQLCommand.ExecuteReader;
IF SQLReader.HasRows THEN
EXIT(TRUE)
ELSE
IF ShowError THEN
ERROR(Text001,TableName)
ELSE
EXIT(FALSE);
END;

PROCEDURE CreateLinkTable@1200050020();
VAR
SQLCommand@1000000000 : DotNet "’System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Data.SqlClient.SqlCommand";
Guid@1200050000 : Text[40];
Query@1000000001 : Text[1024];
BEGIN
OpenLinkDB;

Guid := CREATEGUID;

Query :=
STRSUBSTNO(‘CREATE TABLE [%1](‘,SigningSetup."Database Table Name") +
‘[Link ID] [varchar](20) NOT NULL,’ +
‘[Table ID] [int] NOT NULL,’ +
‘[Period] [varchar](6) NOT NULL,’ +
‘[User ID] [varchar](20) NOT NULL,’ +
‘[Computer Name] [varchar](50) NOT NULL,’ +
‘[File Name] [varchar](1024) NOT NULL,’ +
‘[Content Type] [varchar](50) NOT NULL,’ +
‘[Object] [image] NULL,’ +
STRSUBSTNO(‘ CONSTRAINT [PK_%1_ID] PRIMARY KEY CLUSTERED’,Guid) +
‘([Link ID] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,’ +
‘IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]’ +
‘) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];’;

SQLCommand := SQLCommand.SqlCommand(Query,SQLConnection);
SQLCommand.CommandTimeout(0); // wait indefinately for the command to finish.
SQLCommand.ExecuteNonQuery;
END;

LOCAL PROCEDURE UploadFileToSQL@1200050011(FileName@1200050000 : Text[1024];TableID@1200050007 : Integer) URL : Text[1024];
VAR
SQLCommand@1000000005 : DotNet "’System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Data.SqlClient.SqlCommand";
SQLdbType@1000000003 : DotNet "’System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Data.SqlDbType";
TempFileName@1100408001 : Text[1024];
Query@1000000006 : Text[1024];
ComputerName@1200050002 : Code[50];
LinkID@1200050006 : Code[20];
InsertCount@1000000007 : Integer;
BEGIN
TempFileName := FileMgt.UploadFileSilent(FileName);
OpenLinkDB;

SigningSetup.TESTFIELD("Link Web URL");
ComputerName := SigningTools.GetComputerName;

LinkID := NoSeriesMgt.GetNextNo(SigningSetup."Link Reference Nos.",TODAY,TRUE);
URL := SigningSetup."Link Web URL" + STRSUBSTNO(‘&ID=%1’,LinkID);

Query := STRSUBSTNO(
‘INSERT INTO [%1] ‘ +
‘ ([Link ID] ‘ +
‘ ,[Table ID] ‘ +
‘ ,[Period] ‘ +
‘ ,[User ID] ‘ +
‘ ,[Computer Name] ‘ +
‘ ,[File Name] ‘ +
‘ ,[Content Type] ‘ +
‘ ,[Object]) ‘ +
‘ VALUES ‘ +
‘ (@LinkID ‘ + // varchar(20)
‘ ,@TableID ‘ + // int
‘ ,@Period ‘ + // varchar(6)
‘ ,@UserID ‘ + // varchar(20)
‘ ,@ComputerName ‘ + // varchar(50)
‘ ,@FileName ‘ + // varchar(1024)
‘ ,@ContentType ‘ + // varchar(50)
‘ ,@Object) ‘ + // image
”,SigningSetup."Database Table Name");

SQLCommand := SQLCommand.SqlCommand(Query,SQLConnection);
SQLCommand.CommandTimeout(0); // wait indefinitely for the command to finish.
SQLCommand.Parameters.Add(‘@LinkID’,SQLdbType.VarChar,20).Value := LinkID;
SQLCommand.Parameters.Add(‘@TableID’,SQLdbType.Int).Value := TableID;
SQLCommand.Parameters.Add(‘@Period’,SQLdbType.VarChar,6).Value := FORMAT(TODAY,0,'<Year4><Month,2>’);
SQLCommand.Parameters.Add(‘@UserID’,SQLdbType.VarChar,20).Value := USERID;
SQLCommand.Parameters.Add(‘@ComputerName’,SQLdbType.VarChar,50).Value := ComputerName;
SQLCommand.Parameters.Add(‘@FileName’,SQLdbType.VarChar,1024).Value := FileMgt.GetFileName(FileName);
SQLCommand.Parameters.Add(‘@ContentType’,SQLdbType.VarChar,50).Value := SigningTools.ContentType(FileName);
SQLCommand.Parameters.Add(‘@Object’,SQLdbType.Image).Value := ServerFile.ReadAllBytes(TempFileName);
InsertCount := SQLCommand.ExecuteNonQuery;
ServerFile.Delete(TempFileName);
END;

LOCAL PROCEDURE DownloadFileFromWeb@1100408027(URL@1200050000 : Text[1024]) FileName : Text[1024];
VAR
HttpWebRequest@1100408002 : DotNet "’System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Net.HttpWebRequest";
HttpWebResponse@1100408001 : DotNet "’System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Net.WebResponse";
HttpWebHeaderCollection@1100408003 : DotNet "’System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Net.WebHeaderCollection";
Credential@1100408008 : DotNet "’System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Net.NetworkCredential";
MemoryStream@1100408004 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.IO.MemoryStream";
ServerFileStream@1100408005 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.IO.FileStream";
TempBlob@1100408009 : TEMPORARY Record 99008535;
Document@1100408011 : BigText;
OutStr@1100408007 : OutStream;
InStr@1100408010 : InStream;
TempFile@1100408006 : File;
FilePath@1200050001 : Text[1024];
TempPath@1100408000 : Text[1024];
BEGIN
GetSigningSetup;
Credential := Credential.NetworkCredential;
Credential.UserName := SigningSetup."Database Login Name";
Credential.Password := SigningSetup."Database User Password";
Credential.Domain := ‘.’;
HttpWebRequest := HttpWebRequest.Create(URL);
HttpWebRequest.Timeout := 30000;
HttpWebRequest.Credentials := Credential;
HttpWebRequest.Method := ‘GET’;
HttpWebResponse := HttpWebRequest.GetResponse;
HttpWebHeaderCollection := HttpWebResponse.Headers;

FileName := HttpWebHeaderCollection.Get(‘Content-Disposition’);
IF STRPOS(FileName,’filename=’) = 0 THEN
FileName := ”
ELSE BEGIN
FileName := COPYSTR(FileName,STRPOS(FileName,’filename=’) + 9);
TempPath :=
FileMgt.GetDirectoryName(FileMgt.ServerTempFileName(”)) +
FileName;
TempFile.CREATE(TempPath);
TempFile.CREATEOUTSTREAM(OutStr);
COPYSTREAM(OutStr,HttpWebResponse.GetResponseStream);
TempFile.CLOSE;
FileName := FileMgt.DownloadTempFile(TempPath);
END;
END;

PROCEDURE OpenLinksForRecRef@1200050005(VAR RecRef@1200050000 : RecordRef);
VAR
RecordLinks@1200050001 : Record 2000000068;
URL@1200050002 : Text[1024];
FileName@1200050004 : Text[1024];
BEGIN
WITH RecordLinks DO BEGIN
SETCURRENTKEY("Record ID");
SETRANGE("Record ID",RecRef.RECORDID);
SETRANGE(Type,Type::Link);
IF NOT ISEMPTY THEN
OpenRecordLinks(RecordLinks);
END;
END;

PROCEDURE MailLinksForRecRef@1100408004(VAR RecRef@1200050000 : RecordRef;SuggestedEMailAddr@1100408000 : Text[1024];Body@1100408001 : Text[1024]);
VAR
RecordLinks@1200050001 : Record 2000000068;
URL@1200050002 : Text[1024];
FileName@1200050004 : Text[1024];
BEGIN
WITH RecordLinks DO BEGIN
SETCURRENTKEY("Record ID");
SETRANGE("Record ID",RecRef.RECORDID);
SETRANGE(Type,Type::Link);
IF NOT ISEMPTY THEN
MailRecordLinks(RecordLinks,SuggestedEMailAddr,Body);
END;
END;

PROCEDURE OpenRecordLinks@1100408000(VAR RecordLinks@1100408000 : Record 2000000068);
VAR
URL@1200050002 : Text[1024];
FileName@1200050004 : Text[1024];
BEGIN
WITH RecordLinks DO BEGIN
IF FINDSET THEN REPEAT
URL := URL1 + URL2 + URL3 + URL4;
IF COPYSTR(UPPERCASE(URL1),1,6) IN [‘HTTP:/’,’HTTPS:’,’FTP://’] THEN
FileName := DownloadFile(URL1 + URL2 + URL3 + URL4);
IF FileName <> ” THEN BEGIN
HYPERLINK(FileName);
END ELSE
HYPERLINK(URL);
UNTIL NEXT = 0;
END;
END;

PROCEDURE MailRecordLinks@1100408003(VAR RecordLinks@1100408000 : Record 2000000068;SuggestedEMailAddr@1100408011 : Text[1024];Body@1100408012 : Text[1024]);
VAR
Mail@1000000000 : Codeunit 397;
URL@1100408002 : Text[1024];
FileName@1100408001 : Text[1024];
BEGIN
Mail.NewMessage(SuggestedEMailAddr,”,Text031,Body,”,TRUE);

WITH RecordLinks DO BEGIN
IF FINDSET THEN REPEAT
URL := URL1 + URL2 + URL3 + URL4;
IF COPYSTR(UPPERCASE(URL1),1,6) IN [‘HTTP:/’,’HTTPS:’,’FTP://’] THEN
FileName := DownloadFile(URL1 + URL2 + URL3 + URL4);
IF FileName <> ” THEN
Mail.AttachFile(FileName)
ELSE
Mail.AttachFile(URL);
UNTIL NEXT = 0;
END;

Mail.Send;
END;

PROCEDURE MailFile@1100408002(VAR FileName@1100408011 : Text[1024];VAR Description@1100408000 : Text[250]);
VAR
Mail@1000000000 : Codeunit 397;
BEGIN
Mail.NewMessage(”,”,Description,”,FileName,TRUE);
Mail.Send;
END;

EVENT SQLConnection@1000000000::InfoMessage@48(sender@1000000001 : Variant;e@1000000000 : DotNet "’System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Data.SqlClient.SqlInfoMessageEventArgs");
BEGIN
END;

EVENT SQLConnection@1000000000::StateChange@49(sender@1000000001 : Variant;e@1000000000 : DotNet "’System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Data.StateChangeEventArgs");
BEGIN
END;

EVENT SQLConnection@1000000000::Disposed@50(sender@1000000001 : Variant;e@1000000000 : DotNet "’mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.EventArgs");
BEGIN
END;

BEGIN
{
You experience bad performance when you scroll any list in the classic client in Microsoft Dynamics NAV 2009

To resolve this problem, use one of the following methods:
You can create a required index directly in the SQL database for the Record Links table by using Microsoft SQL Server Management Stu
You can execute the following TSQL statement in Microsoft NAV 2009 database:

CREATE INDEX Sign_Index ON [Record Link] ([Record ID], [Type], Company)
}
END.
}
}

[/code]

Accessing your non english data from the MS SQL server

If you are like me, located in a non-English speaking country and would like users to be able to use other tools then the Dynamics NAV clients to access the company data you will find that all the meta data in the database is in English.  This means that you will have to translate the fields and sometimes the data to your language.

The problems are:

  • Field captions are unavailable in the MS SQL database
  • Option values are shown as number
  • Boolean is shown as number
  • Global dimension do not have the correct caption
  • Time is shown as DateTime
  • Not easy to see the difference between normal date and closing date

The solution that I am using is to create a separate database on the same database server and create localized views in that database.  What you get with the solution is:

  • A date table that can be used to show all properties of a given date
  • A import of the option value captions
  • A selection of tables to make accessible
  • A batch job to create the localized SQL view for each table

Here is a list of the fields in the date table

Field No. Field Name Data Type Length
1 Date Date
2 Date Name Text 30
3 Year Integer
4 Week Integer
5 Month Integer
6 Month Name Text 20
7 Day of Week Integer
8 Day Name Text 20
9 Day of Month Integer
10 Closing Date Boolean
11 SQL Month Name Text 20
12 SQL Day Name Text 20
13 Quarter Integer
14 Year Month Integer
15 Year Month Name Text 20
16 Month Year  Name Text 20
17 Quarter Name Text 20
18 VAT Period Integer
19 VAT Period Name Text 20
20 Sorting Date Integer
21 HRMS Integer Start Integer
22 HRMS Integer End Integer
23 Day of Year Integer
24 Day of Half Year Integer
25 Day of Quarter Integer
26 Day of Accounting Integer
27 Half Years Integer
28 Half Year of Year Integer
29 Is Holiday Boolean
30 Is Working Day Boolean
31 Month of Half Year Integer
32 Month of Quarter Integer
33 Month of Year Integer
34 Quarters of Half Year Integer
35 Quarters of Year Integer
36 Week of Year Integer
37 Is Week Day Boolean
41 Half Year Name Text 20
42 Week Name Text 20
102 Fiscal Day Date
103 Fiscal Year Integer
104 Fiscal Week Integer
105 Fiscal Month Integer
107 Fiscal Day of Week Integer
109 Fiscal Day of Month Integer
113 Fiscal Quarter Integer
123 Fiscal Day of Year Integer
124 Fiscal Day of Half Year Integer
125 Fiscal Day of Quarter Integer
127 Fiscal Half Years Integer
128 Fiscal Half Year of Year Integer
131 Fiscal Month of Half Year Integer
132 Fiscal Month of Quarter Integer
133 Fiscal Month of Year Integer
134 Fiscal Quarters of Half Year Integer
135 Fiscal Quarters of Year Integer
136 Fiscal Week of Half Year Integer
137 Fiscal Week of Month Integer
138 Fiscal Week of Quarter Integer
139 Fiscal Week of Year Integer
140 Fiscal Quarter Name Text 20
141 Fiscal Half Year Name Text 20
142 Fiscal Week Name Text 20
143 Fiscal Month Name Text 20

Lets take the G/L Entry table as an example. In the MS SQL the field names are in English and the data not readable for the normal user.

SELECT [Entry No_],[G_L Account No_],[Posting Date],[Document Type]
      ,[Document No_],[Description],[Bal_ Account No_],[Amount]
      ,[Global Dimension 1 Code],[Global Dimension 2 Code],[User ID] 
... 
      ,[FA Entry Type],[FA Entry No_] 
FROM [Dynamics NAV Demo Database (IS 2009 R2)].[dbo].[CRONUS Ísland hf_$G_L Entry]

and for example the [Document Type] will show as numbers.  Here is an example of the output of my tool to create a localized view for this table.

IF EXISTS(SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'dbo.ISL$CRONUS Ísland hf_$Fjárhagsfærsla'))
DROP VIEW [dbo].[ISL$CRONUS Ísland hf_$Fjárhagsfærsla]
GO
CREATE VIEW [dbo].[ISL$CRONUS Ísland hf_$Fjárhagsfærsla]
AS
SELECT
[Entry No_] As [Færslunr_]
,[G_L Account No_] As [Fjárhagsreikn_nr_]
,[Posting Date] As [Bókunardags_]
,CASE [Document Type]
WHEN 0 THEN ' '
WHEN 1 THEN 'Greiðsla'
WHEN 2 THEN 'Reikningur'
WHEN 3 THEN 'Kreditreikningur'
WHEN 4 THEN 'Vaxtareikningur'
WHEN 5 THEN 'Innheimtubréf'
WHEN 6 THEN 'Endurgreiðsla'
END As [Tegund fylgiskjals]
,[Document No_] As [Númer fylgiskjals]
,[Description] As [Lýsing]
,[Bal_ Account No_] As [Mótreikningur nr_]
,[Amount] As [Upphæð]
,[Global Dimension 1 Code] As [Deild Kóti]
,[Global Dimension 2 Code] As [Verkefni Kóti]
,[User ID] As [Kenni notanda]
,CASE [System-Created Entry]
WHEN 1 THEN 'Já'
WHEN 0 THEN 'Nei'
END As [Kerfisfærsla]
...
,CASE [FA Entry Type]
WHEN 0 THEN ' '
WHEN 1 THEN 'Eignir'
WHEN 2 THEN 'Viðhald'
END As [Eignafærslutegund]
,[FA Entry No_] As [Eignafærslunr_]
FROM [CRONUS Ísland hf_$G_L Entry]
GO

Executing this will give me a view in my database that I can use to fetch localized data from the G/L Entry table with all the above problems solved.

By using the additional date table as dimension in OLAP or as join in a SQL query I can easily find all aspects of the “Posting Date” in the G/L Entry table and group entries accordingly.

Kill Idle Connections

To save concurrent uses in NAV it might be a good idea to kill idle connections automatically.

This can be done by looping through the Session Virtual Table and check the Idle Time field. I like to add the field “Maximum Idle Time” to the User Setup table and compare that field to the Idle Time in the Session table before deciding to kill the session.

The use running this batch must have permission to kill sessions on the SQL Server.
[code htmlscript=”false”]IF ISCLEAR(ADOConnection) THEN
IF NOT CREATE(ADOConnection) THEN
ERROR(Text001);

IF ADOConnection.State = 1 THEN
ADOConnection.Close;

ADOConnection.Open(
‘Driver={SQL Server Native Client 10.0};’ +
‘Server=<ServerName>;’ +
‘Database=<DatabaseName>;’ +
‘Trusted_Connection=yes;’);
ADOConnection.CommandTimeout(0);

Session – OnAfterGetRecord()
IF "Application Name" <>
‘Microsoft Dynamics NAV Classic client’
THEN
CurrReport.SKIP;
BackSlashPos := STRPOS("User ID",’\’);
IF NOT
UserSetup.GET(
UPPERCASE(COPYSTR("User ID",BackSlashPos + 1)))
THEN
CurrReport.SKIP;
IF FORMAT(UserSetup."Maximum Idle Time") = ” THEN
CurrReport.SKIP;
Idle := "Idle Time";
MaxIdle := (UserSetup."Maximum Idle Time" – 000000T);
IF Idle < MaxIdle THEN
CurrReport.SKIP;

ADOConnection.Execute(STRSUBSTNO(‘KILL %1’,"Connection ID"));

Session – OnPostDataItem()
ADOConnection.Close;
CLEAR(ADOConnection);[/code]

Name DataType Subtype Length
UserSetup Record User Setup
ADOConnection Automation ‘Microsoft ActiveX Data Objects 2.8 Library’.Connection
MaxIdle Decimal
Idle Decimal
BackSlashPos Integer

This can be added as a Job Queue Batch.

See here to check for required permission to use the kill statement.

Scanning in Remote Desktop

When running Dynamics NAV as a Remote Application or in Remote Desktop you are missing connection to your locally connected scanner.  I have created a solution that uses a standalone application running on the client computer.  That application uses the TwainControlX from Ciansoft and connects to a Microsoft SQL database.  The database needs to be on a computer that both the client computer and the remote desktop computer have access to.

On that database server a database is created with only one table.  That table can be truncated every night in a maintenance job.  This is the table creation script.

CREATE TABLE [dbo].[ScanRequests](
	[ComputerName] [varchar](20) NOT NULL,
	[ClientName] [varchar](20) NOT NULL,
	[SelectedDeviceID] [varchar](250) NULL,
	[Image] [image] NULL,
	[RequestDateTime] [datetime] NULL,
	[FormatID] [varchar](50) NULL,
	[AnswerDateTime] [datetime] NULL,
	[Answered] [int] NOT NULL,
	[AnswerText] [varchar](250) NULL,
	[PageNo] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

The program on the client then connects to the database and to the scanner.

A code in Dynamics NAV creates the request for a scan by inserting a entry into the ScanRequest table and the application on the client computer automatically scans the images and updates that scan request entry.  The updated entry then containes the image file that is imported into Dynamics NAV or attached as a link.

The following ZIP files are encrypted.  View the Product page for more information.

Twain Scanner (ISL)
NAV Myndlestur (TwainOCX) Source Code (ISL)

 

Record Links and Attachments

The standard Record Links table in Dynamics NAV has the ability to store a URL and link it to any record in the Dynamics NAV database.  However, in order to fully use this each user must have access to the give URL.

As a part of the Purchase Invoice signing solution I built a process to select or scan a file, upload it to a SQL database and create a URL to that file in the Record Link table.  I create a  SQL link table that stores the file and a permission table that holds information on users permission by table id.

I used TwainControlX to scan and create files to import and link.  Then I add a “Link File” button to the Purchase Order.

When Link File is selected the following dialogue appears.

After selecting or scanning a file a new link is created and the file is uploaded to the SQL server.

The file is now available to all users that have the required permission through the following link.

The can be downloaded directly with the Open button or NAV can download and open the file with code.  I created a .ASP web page to download files from the Link database.

SQL Maintenance from Dynamics NAV

When running Dynamics NAV on a MS-SQL database you need to execute some maintenance tasks.  They include defrag and rebuild for indexes and statistics update.  By using SQL Native Client, the Table Information from NAV and Job Queue you can automate this maintenance with Dynamics NAV Application Server.

First you must make sure that automatic update statistics and auto shrink are set to false.

Then you create codeunit for each task.  Here is an example.

Variables
[code]TableInformation@1100409000 : Record 2000000028;
ADOConnection@1100409004 :
Automation "’Microsoft ActiveX Data Objects 2.8 Library’.Connection";
SQLSetup@1100409006 : Record 10017176;
Window@1100409003 : Dialog;
WindowLastUpdated@1100409002 : DateTime;
Counter@1100409001 : Integer;
Total@1100409000 : Integer;
Text001@1100409005 :
TextConst ‘ENU=Microsoft ADO not found.;ISL=Microsoft ADO finnst ekki.’;[/code]
Code for Rebuild Indexes using “DBCC DBREINDEX”
[code]SQLSetup.GET;
SQLSetup.TESTFIELD("SQL Server Name");
SQLSetup.TESTFIELD("SQL Database name");
IF GUIALLOWED THEN
Window.OPEN(‘#1####################################\\’ +
‘@2@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@’);
WindowLastUpdated := CURRENTDATETIME;

IF ISCLEAR(ADOConnection) THEN
IF NOT CREATE(ADOConnection) THEN
ERROR(Text001);

IF ADOConnection.State = 1 THEN
ADOConnection.Close;

ADOConnection.Open(
STRSUBSTNO(
‘Driver={SQL Native Client};Server=%1;’ +
‘Database=%2;Trusted_Connection=yes;’,
SQLSetup."SQL Server Name",
SQLSetup."SQL Database name"));
ADOConnection.CommandTimeout(0);

WITH TableInformation DO BEGIN
SETFILTER("No. of Records",’&gt;0′);
SETFILTER("Table No.",'<>150014&<2000000002′);
Total := COUNTAPPROX;
Counter := 0;
IF FINDSET THEN REPEAT
Counter := Counter + 1;
IF GUIALLOWED THEN
IF (CURRENTDATETIME – WindowLastUpdated) > 1000 THEN BEGIN
Window.UPDATE(1,"Table Name");
Window.UPDATE(2,ROUND(Counter / Total * 10000,1));
WindowLastUpdated := CURRENTDATETIME;
END;

IF "Company Name" <> ” THEN
ADOConnection.Execute(
STRSUBSTNO(
‘DBCC DBREINDEX ([%1$%2],””,90);’,
CONVERTSTR("Company Name",’."\/”’,’_____’),
CONVERTSTR("Table Name",’."\/”’,’_____’)))
ELSE
ADOConnection.Execute(
STRSUBSTNO(
‘DBCC DBREINDEX ([%1],””,90);’,
CONVERTSTR("Table Name",’."\/”’,’_____’)))

UNTIL NEXT = 0;
END;

ADOConnection.Close;[/code]
This example is using trusted connection to the SQL Server, that is a domain user account.  Grant that user the processadmin server role.

To defragment indexes, use “DBCC INDEXDEFRAG”.

Skýrr has an addon solution that includes all these functions and more with granule 10,017,160

Import from ODBC into Dynamics NAV

Importing data from ODBC database into Dynamics NAV is a regular task.  The most common problem is where the external database has data types and null values that are not supported by Dynamics NAV.

I have solved this with a function that builds the query command.  The function selects all fields from the table and then loops through them, checks the data type and builds an new query command using both “ISNULL” and “CONVERT” functions.

The attached example is where I import customers and vendors from Pervasive database with DK business data.  The create function requires the table name and the primary key.  If the external table has to many fields to fit into one query command more queries will be created for remaining fields along with the primary key.

Here you can download a ZIP file with the example codeunit.