Transferring small amount of data between databases

Most of us have needed to copy data from one Dynamics NAV database to another.  For example the posting setup tables, payment terms, currency, etc.

If the databases are not identical you will not be able to copy and paste the data and you have to solve this problem with dataports or XMLPorts.

I offer a solution to this problem.  I have created a form that is able to export and import data based on table and field numbers.  It will import data to a table even if some fields are missing in the destination database.

Here is the source code

Dynamics NAV and Soap Services

Soap Web Services return XML with name spaces.  You need to remove the name spaces before you can use XMLPort to read the answer.  Microsoft has supplied a solution to this problem by applying Stylesheet to the XML before it is read by the XMLPort.  I added to that Stylesheet a few lines to remove the Header section of the XML.

Attached is a working example that queries a bank for a given bank account and registration number.

Here is the source code.

Multiple NAS Services on a single license

Most Dynamics NAV databases have more than one companies.  When setting up Dynamics NAV Application Server as a service, you have to install a service for each company in your database.  Your license might not have enough user licenses to be able to run all services at once.  My solution is the following.

I install a NAS service for each company.  One of them set to automatic startup type, all others are set to manual. I then schedule a task every 30 minutes and execute a script that I save as NAS-Rotate.vbs.
[code lang=”vb”]strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & _
"\root\cimv2")

CurrentServiceName = ""
RunNextService "NAS-1","NAS-2"
RunNextService "NAS-2","NAS-3"
RunNextService "NAS-3","NAS-4"
RunNextService "NAS-4","NAS-5"
RunNextService "NAS-5","NAS-6"
RunNextService "NAS-6","NAS-1"
If CurrentServiceName = "" Then
StartService "NAS-1"
End If

Sub RunNextService(ServiceName,NextServiceName)
Set colServices = objWMIService.ExecQuery _
("SELECT * FROM Win32_Service WHERE Name = ‘" & ServiceName & "’")

‘* List all the services on the machine
For each objService in colServices
If objService.State = "Running" Then
‘* WScript.Echo "Stopping: " & objService.DisplayName & "," & _
‘* objService.StartName & "," & objService.State
objService.StopService
StartService(NextServiceName)
CurrentServiceName = NextServiceName
‘* WScript.Quit
End if
Next
End Sub

Sub StartService(ServiceName)
Set colServices = objWMIService.ExecQuery _
("SELECT * FROM Win32_Service WHERE Name = ‘" & ServiceName & "’")

‘* List all the services on the machine
For each objService in colServices
If objService.State = "Stopped" Then
‘* WScript.Echo "Starting: " & objService.DisplayName & "," & _
‘*objService.StartName & "," & objService.State
objService.StartService
End if
Next
End Sub[/code]
In this excample I have installed six services.

Forms and Pages are editable in lookupmode

Some forms and pages in Dynamics NAV are used both for setup and look up.  For example form and page “Payment Terms” with ID 4.  I like to add a code to forms and pages to prevent this.

On forms:
[code]OnOpenForm=BEGIN
CurrForm.EDITABLE := NOT CurrForm.LOOKUPMODE;
END;[/code]
On pages:
[code]OnOpenPage=BEGIN
CurrPage.EDITABLE := NOT CurrPage.LOOKUPMODE;
END;[/code]
This causes the form or page to be write protected if they are opened as a look up from the “Payment Terms Code” fields.

Got some visits

This web is slowly starting to appear in search machines and I got my first praise from a user that liked my Job Queue E-mailer solution. Please use my solutions and honor the copyright statement.

Hope that my effort will pay off, please visit my supporters website to support me.

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",’>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.

Collapse and Expand in Classic Client

In our Payroll design work I have tried a new solution inspired by the Role Tailored Client. I want to be able to collapse and expand the tab control to give more space for the subform.

I put a button with an up arrow and another with a down arrow on the form.  I also put a menu item in the Functions menu button to toggle between collapse and expand with a shortcut key Alt-F6, the same key that is used in RTC.

The buttons
[code]{ 57;CommandButton;15400;880;550;550;Name=DownBtn;
HorzGlue=Right;
Focusable=No;
ParentControl=&lt;TabControlID&gt;;
InPage=0;
ShowCaption=No;
Bitmap=1863;
OnPush=BEGIN
UpdateTabs(NOT TabExpanded);
END;
}
{ 58;CommandButton;15400;880;550;550;Name=UpBtn;
HorzGlue=Right;
Focusable=No;
ParentControl=&lt;TabControlID&gt;;
InPage=0;
ShowCaption=No;
Bitmap=1862;
OnPush=BEGIN
UpdateTabs(NOT TabExpanded);
END;
}[/code]
The function
[code]PROCEDURE UpdateTabs@51(Expanded@1 : Boolean);
BEGIN
TabExpanded := Expanded;
IF Expanded THEN BEGIN
CurrForm.HeadTab.HEIGHT := 6160;
CurrForm.BatchPanel.HEIGHT := 6160;
CurrForm.WageEarnerLines.YPOS := 6490;
CurrForm.WageEarnerLines.HEIGHT := CurrForm.HEIGHT – 7370;
CurrForm.DownBtn.VISIBLE := FALSE;
CurrForm.UpBtn.VISIBLE := TRUE;
END ELSE BEGIN
CurrForm.HeadTab.HEIGHT := 1320;
CurrForm.BatchPanel.HEIGHT := 1320;
CurrForm.WageEarnerLines.YPOS := 1650;
CurrForm.WageEarnerLines.HEIGHT := CurrForm.HEIGHT – 2530;
CurrForm.DownBtn.VISIBLE := TRUE;
CurrForm.UpBtn.VISIBLE := FALSE;
END;
END;[/code]
Where 7370 is the space on the form needed for other objects when the tab control is expanded and 2530 when the tab control is collapsed.

And in OnOpenForm trigger
[code]OnOpenForm=BEGIN
UpdateTabs(TRUE);
END;[/code]
And finnally the Global Variable
[code]TabExpanded@53 : Boolean;[/code]

@ms.is

Currently in house at MS having a few tasks to complete. The main task to build an application to handle grant requests that are imported from the company web.  Grant requests can be both for money and goods.  MS has a 140GB database with 80 users and are running Dynamics NAV 5.0SP1 client on 4.00 SP3 database.

Added to Job Queue E-Mail

Since the report that I posted earlier simply sends an PDF copy of the Invoice and Credit Memo to the recipient via SMTP mail you do not have a copy of that E-Mail in your inbox or sent items.

I added a single line of code to send a carbon copy of the E-Mail to the sender.

[code]

IF CompanyInformation."Ship-to Contact" <> ” THEN BEGIN
SMTPMail.AppendBody(CRLF);
SMTPMail.AppendBody(Text004);
SMTPMail.AppendBody(CRLF);
SMTPMail.AppendBody(CompanyInformation."Ship-to Contact");
SMTPMail.AppendBody(CRLF);
END;

SMTPMail.AddCC(CompanyInformation."E-Mail"); // Add this line
SMTPMail.AddAttachment(Directory + FileName);
SMTPMail.Send;
[/code]