Month: June 2013

Posted on

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).

Posted on

Reading the NAV 2013 Service Configuration

In a previous blog I showed how to get the database name and the database server name for NAV 2009.  I used Automation object to read the service configuration file and pull from there the required information.

This method is obsolete for NAV 2013 but here is the required code to pick similar and more information from the service configuration file.

[code]OBJECT Codeunit 50000 Read Service Config
{
OBJECT-PROPERTIES
{
Date=18.06.13;
Time=22:51:31;
Modified=Yes;
Version List=Dynamics.is;
}
PROPERTIES
{
SingleInstance=Yes;
OnRun=BEGIN
END;

}
CODE
{

PROCEDURE FindMiddleTierServicePath@1000000002() ServicePath : Text[1024];
VAR
ActiveSession@1000000002 : Record 2000000110;
ServerFile@1000000001 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.IO.File";
XMLDoc@1000000000 : DotNet "’System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Xml.XmlDocument";
XMLNode@1000000003 : DotNet "’System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Xml.XmlNode";
BEGIN
ActiveSession.SETRANGE("Session ID",SESSIONID);
ActiveSession.FINDFIRST;

XMLDoc := XMLDoc.XmlDocument;
IF ServerFile.Exists(APPLICATIONPATH + ‘Instances\’ + ActiveSession."Server Instance Name" + ‘\CustomSettings.config’) THEN
XMLDoc.Load(APPLICATIONPATH + ‘Instances\’ + ActiveSession."Server Instance Name" + ‘\CustomSettings.config’)
ELSE
XMLDoc.Load(APPLICATIONPATH + ‘CustomSettings.config’);

ServicePath := ‘DynamicsNAV://’ + ActiveSession."Server Computer Name" + ‘:’;

XMLNode := XMLDoc.SelectSingleNode(‘//appSettings/add[@key=”ClientServicesPort”]’);
ServicePath := ServicePath + XMLNode.Attributes.Item(1).InnerText + ‘/’ + ActiveSession."Server Instance Name";
CLEAR(XMLDoc);
END;

PROCEDURE FindSOAPWebServicePath@10010403() ServicePath : Text[1024];
VAR
ActiveSession@1000000002 : Record 2000000110;
ServerFile@1000000001 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.IO.File";
XMLDoc@1000000000 : DotNet "’System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Xml.XmlDocument";
XMLNode@1000000003 : DotNet "’System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Xml.XmlNode";
httpUtility@1000000004 : DotNet "’System.Web, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a’.System.Web.HttpUtility";
BEGIN
ActiveSession.SETRANGE("Session ID",SESSIONID);
ActiveSession.FINDFIRST;

httpUtility := httpUtility.HttpUtility;
XMLDoc := XMLDoc.XmlDocument;
IF ServerFile.Exists(APPLICATIONPATH + ‘Instances\’ + ActiveSession."Server Instance Name" + ‘\CustomSettings.config’) THEN
XMLDoc.Load(APPLICATIONPATH + ‘Instances\’ + ActiveSession."Server Instance Name" + ‘\CustomSettings.config’)
ELSE
XMLDoc.Load(APPLICATIONPATH + ‘CustomSettings.config’);

XMLNode := XMLDoc.SelectSingleNode(‘//appSettings/add[@key=”SOAPServicesSSLEnabled”]’);
IF UPPERCASE(XMLNode.Attributes.Item(1).InnerText) = ‘FALSE’ THEN
ServicePath := ‘http://’
ELSE
ServicePath := ‘https://’;

ServicePath := ServicePath + ActiveSession."Server Computer Name" + ‘:’;

XMLNode := XMLDoc.SelectSingleNode(‘//appSettings/add[@key=”SOAPServicesPort”]’);
ServicePath := ServicePath + XMLNode.Attributes.Item(1).InnerText + ‘/’ + ActiveSession."Server Instance Name" + ‘/WS/’;
ServicePath := ServicePath + httpUtility.UrlPathEncode(COMPANYNAME) + ‘/Services’;

CLEAR(XMLDoc);
END;

PROCEDURE FindODataWebServicePath@10010405() ServicePath : Text[1024];
VAR
ActiveSession@1000000002 : Record 2000000110;
ServerFile@1000000001 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.IO.File";
XMLDoc@1000000000 : DotNet "’System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Xml.XmlDocument";
XMLNode@1000000003 : DotNet "’System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Xml.XmlNode";
BEGIN
ActiveSession.SETRANGE("Session ID",SESSIONID);
ActiveSession.FINDFIRST;

XMLDoc := XMLDoc.XmlDocument;
IF ServerFile.Exists(APPLICATIONPATH + ‘Instances\’ + ActiveSession."Server Instance Name" + ‘\CustomSettings.config’) THEN
XMLDoc.Load(APPLICATIONPATH + ‘Instances\’ + ActiveSession."Server Instance Name" + ‘\CustomSettings.config’)
ELSE
XMLDoc.Load(APPLICATIONPATH + ‘CustomSettings.config’);

ServicePath := ‘http://’ + ActiveSession."Server Computer Name" + ‘:’;

XMLNode := XMLDoc.SelectSingleNode(‘//appSettings/add[@key=”ODataServicesPort”]’);
ServicePath := ServicePath + XMLNode.Attributes.Item(1).InnerText + ‘/’ + ActiveSession."Server Instance Name" + ‘/OData/’;

CLEAR(XMLDoc);
END;

PROCEDURE FindDatabaseServerName@1000000000() DatabaseServerName : Text[1024];
VAR
ActiveSession@1000000003 : Record 2000000110;
ServerFile@1000000002 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.IO.File";
XMLDoc@1000000001 : DotNet "’System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Xml.XmlDocument";
XMLNode@1000000000 : DotNet "’System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Xml.XmlNode";
DatabaseServer@1000000004 : Text[1024];
DatabaseInstance@1000000005 : Text[1023];
BEGIN
ActiveSession.SETRANGE("Session ID",SESSIONID);
ActiveSession.FINDFIRST;

XMLDoc := XMLDoc.XmlDocument;
IF ServerFile.Exists(APPLICATIONPATH + ‘Instances\’ + ActiveSession."Server Instance Name" + ‘\CustomSettings.config’) THEN
XMLDoc.Load(APPLICATIONPATH + ‘Instances\’ + ActiveSession."Server Instance Name" + ‘\CustomSettings.config’)
ELSE
XMLDoc.Load(APPLICATIONPATH + ‘CustomSettings.config’);

XMLNode := XMLDoc.SelectSingleNode(‘//appSettings/add[@key=”DatabaseServer”]’);
DatabaseServer := XMLNode.Attributes.Item(1).InnerText;

XMLNode := XMLDoc.SelectSingleNode(‘//appSettings/add[@key=”DatabaseInstance”]’);
DatabaseInstance := XMLNode.Attributes.Item(1).InnerText;

CLEAR(XMLDoc);

IF DatabaseInstance = ” THEN
DatabaseServerName := DatabaseServer
ELSE
DatabaseServerName := DatabaseServer + ‘\’ + DatabaseInstance;
END;

BEGIN
END.
}
}

[/code]

The four functions here will give you the path to start the Windows Client, the path to the Soap Web Service, the path to OData Web Service and finally the database server name including instance name if used.

The Codeunit is available here: ReadServiceConfig

Posted on

I want two new fields added to NAV 2013

In the tables 92, Customer Posting Group and 93, Vendor Posting Group I would like Microsoft to add a Description field.  This is a default field in almost all setup tables in the solution.  Don’t know why it is not in those tables.

So, I suggested this on Microsoft Connect and you can vote here.

Posted on

Web Client not in the installation language

I have installed the Web Client on several computers.  It was not until yesterday that I finally got to check why the client was not displaying my Icelandic language.  I always got English even if the Icelandic language module was installed.

As I looked at the Web.Config file (in the folder C:\Program Files\Microsoft Dynamics NAV\70\Web Client) I saw that the Icelandic installer change the Language and RegionFormat to France !

[code] <add key="TimeZone" value="UTC" />
<add key="Language" value="fr-FR" />
<!– The locale for formatting dates, times, numbers and currency. This is required. –>
<add key="RegionFormat" value="fr-FR" />[/code]

Now, change the fr-FR to is-IS and save (requires elevated access) and now I have Icelandic Web Client. I have reported this to Microsoft and they will most likely fix this soon. If I will be notified I will also let you know.

WebClientIcelandic