Building a clean database – remove not licensed objects

I just got a question from a client;

Gunnar,
Do you have a “King Kong” license that will allow you to delete any object?  It appears our development license does not have the rights to some of the newer LS Retail objects and I need to create a CRONUS database with just our stuff.

Well, I don’t have a “King Kong” license.  That is only for Microsoft.

There is a way to solve this dilemma.  It will take a few steps.

Start with we have two databases, one with the data we need (LSRetail), another with the application we need (CRONUS).

After the process is completed the LSRetail database will not be usable as a standalone database, so make a copy if you need one.  A new database will be created, CRONUS_APP.  To clean up it is safe to delete both these databases.

The following powershell script has two options.  Option 1 is to have the company data imported into the CRONUS database in the end.  This option requires a server instance running on the CRONUS database.  Option 2 is to create a new database with SQL Management Studio and merge the CRONUS application and the LSRetail data into that one.

[code lang=”powershell”]
$CronusDatabaseName = "CRONUS" # Database with destination Application
$CRONUSServerInstance = "DynamicsNAV80" # Instance for destination Application if using option 1
$LSRetailDatabaseName = "LSRETAIL" # LS Retail Demo Database, database with company data
$EmptyDatabaseName = "CRONUS WITH COMPANYDATA" # Create a new empty database using SQL Management Studio if using option 2
$SQLServerName = "SQL2014"
$SQLServerInstance = "NAVDEMO" # Set blank for default instance

$AppDatabaseName = $CronusDatabaseName + "_APP"
$ServiceAccount = $env:USERDOMAIN + "\" + $env:USERNAME
$ServerInstance = "UPGRADE"
$NavDataFile = (Join-Path $env:TEMP "NAVmerge.navdata")

$SelectOption = "2"

#Export Application from CRONUS Database to Application Database
Export-NAVApplication -DatabaseServer $SQLServerName -DatabaseInstance $SQLServerInstance -DatabaseName $CronusDatabaseName -DestinationDatabaseName $AppDatabaseName -ServiceAccount $ServiceAccount -Force

#Setup a temporary Server Instance for the new database
Get-Credential | New-NAVServerInstance -ServerInstance $ServerInstance -ManagementServicesPort 33555 -ClientServicesPort 33556 -SOAPServicesPort 33557 -ODataServicesPort 33558 -DatabaseInstance $SQLServerInstance -DatabaseServer $SQLServerName -DatabaseName $AppDatabaseName -ServiceAccount User -Force
Set-NAVServerConfiguration -ServerInstance $ServerInstance -KeyName "Multitenant" -KeyValue "true" -Force
Set-NAVServerInstance -ServerInstance $ServerInstance -Start -Force

#Prepare LSRetailDatabase for new configuration
Remove-NAVApplication -DatabaseInstance $SQLServerInstance -DatabaseServer $SQLServerName -DatabaseName $LSRetailDatabaseName -Force

#Mount and Sync LSRetailDatabase as a tenant
Mount-NAVTenant -ServerInstance $ServerInstance -DatabaseInstance $SQLServerInstance -DatabaseServer $SQLServerName -DatabaseName $LSRetailDatabaseName -Id DEFAULT -OverwriteTenantIdInDatabase -AllowAppDatabaseWrite -Force
Sync-NAVTenant -ServerInstance $ServerInstance -Tenant DEFAULT -Mode ForceSync -Force

if (Test-Path $NavDataFile)
{
Remove-Item -Path $NavDataFile -Force
}

#Option 1, Copy Company data to the original CRONUS database. Requies a service running on the CRONUS database
if ($SelectOption -eq "1")
{
Export-NAVData -ServerInstance $ServerInstance -Tenant DEFAULT -AllCompanies -FilePath $NavDataFile -Force
Import-NAVData -ServerInstance $CRONUSServerInstance -FilePath $NavDataFile -AllCompanies -Force
}
#Option 2, Import into the new empty database created by SQL Management Studio
if ($SelectOption -eq "2")
{

Export-NAVData -ServerInstance $ServerInstance -Tenant DEFAULT -AllCompanies -FilePath $NavDataFile -IncludeApplication -IncludeApplicationData -IncludeGlobalData -Force
if ($SQLServerInstance -eq "")
{
Import-NAVData -DatabaseServer $SQLServerName -DatabaseName $EmptyDatabaseName -FilePath $NavDataFile -AllCompanies -IncludeApplicationData -IncludeGlobalData -IncludeApplication

}
else
{
Import-NAVData -DatabaseServer ($SQLServerName + "\" + $SQLServerInstance) -DatabaseName $EmptyDatabaseName -FilePath $NavDataFile -AllCompanies -IncludeApplicationData -IncludeGlobalData -IncludeApplication
}

}

Set-NAVServerInstance -ServerInstance $ServerInstance -Stop -Force
Remove-NAVServerInstance -ServerInstance $ServerInstance -Force

if (Test-Path $NavDataFile)
{
Remove-Item -Path $NavDataFile -Force
}
[/code]

To walk you through what happens;

  • Application from CRONUS is exported into CRONUS_APP database
  • New Service Instance is created for CRONUS_APP database
  • Service Instance is changed to Multi Tenant and started
  • Application is removed from LSRetail database
  • LSRetail database is mounted as a tenant for CRONUS_APP database
  • LSRetail database structure is force-synched to CRONUS_APP application
  • Data from CRONUS_APP and LSRetail tenant is exported to NAVData file
  • NAVData file is imported into an empty database or the existing CRONUS database

 

Updated Hardware Hub Twain Client

The Hardware Hub Twain Client has been updated.  For more details on that solution read posts tagged with twain on my blog.

The updated version should make is easier to understand what is going on and how the scanning process is going.  By ticking in the Log checkbox all actions will be logged in the Messages text box.

twainclient

Another new feature is designed to solve a problem some of the users have been reporting.  When a users selects to have the scanning settings shown that dialog sometimes is opened in the background.

StoreCard

The solution is to add the process name to the Twain Client.  This is an example settings dialog.

settings

Looking at the Task Manager I can see that the process name is wiawow64.exe.

taskmanager

And by using this small PopUp2Front solution I can see the process name I need to add to my Twain Client.

popup2front

The idea is to have the Twain Client look for this process and bring it to the front of the desktop for the user.  In this case I put (wiawow64) as the PopUp Process in the Twain Client.

twainwithpopup

The Select Process button on the Twain Client will give the same process list, but it is not accessible while scanning.

Please remember to restart the Twain Client after you modify the settings to make sure that they are saved in your registry.

Also please remember that the HardwareHub on hub.dynamics.is is only for developement and testing.  All the clients on this website and all the code supplied can be freely used.  To install your own secure Hardware Hub Service go to Objects4NAV.com to download it.

 

Mr. Singelton I presume

We do have one Mr. Singelton in our Dynamics NAV MVP group.  However, this blog post is not about him.

Singelton is a well known Design Pattern.  A Singelton object is a one instance object that is shared in the whole application.  Vjeko showed an example of this in his recent post.

With a Singelton class it is possible to store values that can later be retrieved by another call not related to the first.

It is common practise in Dynamics NAV to create a single-instance Codeunit to keep data that needs to be accessible from all processes within the user session.  A Singelton class is common for all user sessions on the server.

The example Vjeko talks about it that you can store data in a Singelton class to implement a state information into an otherwise stateless web service.

In a recent post I talked about a Variable Store Codeunit.  That was a single-instance Codeunit where I used the DotNet Dictionary to store and retrieve values.  This worked well inside a single user session.

I wanted to make a similar functionalilty available as a Singelton class that would be common for all sessions.  The class code is in c#

[code lang=”csharp”]using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace SingeltonDictionary
{
public class SingeltonDictionary
{
private static SingeltonDictionary instance;
private Dictionary<string, string=""> NavDictionary = new Dictionary<string, string="">();

private SingeltonDictionary() { }

public static SingeltonDictionary Instance
{
get
{
if (instance == null)
{
instance = new SingeltonDictionary();
}
return instance;
}
}
public bool AddToDictionary(string KeyName, string Value)
{
if (NavDictionary.ContainsKey(KeyName))
return false;
else
{
NavDictionary.Add(KeyName, Value);
return true;
}
}
public void AddReplaceInDictionary(string KeyName, string Value)
{
ClearFromDictionary(KeyName);
NavDictionary.Add(KeyName, Value);
}
public bool ClearFromDictionary(string KeyName)
{
if (NavDictionary.ContainsKey(KeyName))
{
NavDictionary.Remove(KeyName);
return true;
}
else
return false;
}
public bool GetFromDictionary(string KeyName, ref string Value)
{
return NavDictionary.TryGetValue(KeyName,out Value);
}
public void ClearDictionary()
{
NavDictionary.Clear();
}
}
}
[/code]

The Singelton Dictionary NAV add-in can be downloaded here -> Microsoft.Dynamics.Nav.SingeltonDictionary

There is another reason why I wanted to create this add-in.  I have a solution for the Windows Client to be able to easily create textboxes and buttons for a NAV Page.  One of the problems with this add-in can be solved with a Singelton class.

The solution has a Panel Class that is built when the add-in is initiated on the page.  On top of that panel any number of buttons and textboxes are added dynamically.  To support this the panel has the autosize property set and will resize as needed.

The problem is that you can’t start to add things to the panel until it has been initiated and at that time NAV draws the page and decides how much space is needed for the panel.  At the time NAV decides the panel is empty and therefore no space is allocated for the panel.

It is not possible to change a property or call a method on the add-in until the add-in is ready and the page has been created.  So, here I solve this by using the Singelton class.  I make the Singelton add-in a reference in the Buttons & TextBoxes add-in and the Singelton class can be accessed the whole time.

In my NAV Page I added the Singelton NAV add-in as a client-side DotNet variable and added three lines of Singelton code.

SingeltonInit2

Similar in my Buttons & TextBoxes add-in I added two properties to read these Singelton values.

SingeltonGet

When the panel is created I make sure that I read these properties.

panelsize

It is important to use the XML format in NAV when using this Singelton Dictionary.  Make sure to always use FORMAT(<value>,0,9) and on the other side use EVALUATE(<value>,SingeltonValue,9).

Evaluate

 

 

Masterclass Workshop in Iceland

Iceland has from the beginning been in the front row when using, developing and supporting Dynamics NAV and it predecessors.  When Mark Brummel started to talk about bringing the Masterclass Workshop to Iceland I knew that there would not be any problem finding attendees.

Mark came up to Iceland and I flew him up to the north cost to my home town.  After a few hours browsing we headed to the Drangey Music Festival (just bing-it).

WP_20150627_20_27_15_Pro

We bathed in Grettislaug and listened to music until it was time to look at the sunset.

WP_20150628_00_44_43_Pro

To our amazement the sun never went below the horizon and before we knew it we where watching a sunrise.

The workshop had twenty attendees with average experience of over ten years.  Two great days and Mark got good reviews.

WP_20150630_13_52_00_Pro

Thank you all

Sellfy.com – get into it…

I have been using Sellfy.com for some time now.  This has forced me to take some of my smaller solutions and turn them into a product.

I connect my Sellfy user to PayPal and give them information about my location.  That is it, all you need to do.  They are supporting the new VAT regulation in between EU countries.

To make things easier to find I created the web site Objects4NAV.com.  There I have a product catalogue with download links.  All download links point to a source outside of Objects4NAV web site.  Some to MiBuSo, some to the source blog post and some to the Sellfy product purchase button.

When a product is made available on Sellfy a price tag has to be applied.  That is normal, this is a store.  The price can be as low as €0.99 so this should not stop anyone.

There is also another great thing about using Sellfy.  If you update your product you can use Sellfy to send the updated product to the ones that bought the product even if you don’t have any information about the purchasers.

Step 1; update the product file.

replaceproductfile

Step 2; send a notification email.

SendUpdate

Step 3; sit back and smile

eMailSent

This example is where I just sent an upgrade to the tool I use to create temporary tables and upgrade codeunit for NAV 2015.  I must say that I am a bit surprise how few have downloaded this product.  It will save so much time in a data upgrade.  Are you not upgrading NAV installations ???

Another example could be a currency import from xe.com.  I posted a function to do this for NAV 2009 R2 and was recently asked for the NAV 2013 R2 version.  I don’t have that in my product list but would it not be nice to be able to find that solution on Objects4NAV.com and download for just €9.  That would be a win-win game.

Technical upgrade from NAV 2013 R2 to NAV 2015

I just made a backup of a live NAV 2013 R2 database to do an upgrade to NAV 2015.  Since the system is live we have several servers running when the backup is created.

The backup was restored in a new environment and the database opened with a NAV 2015 Developement Client.  The database is put in single user mode and converted.  After the conversion a process to upgrade objects starts.  Here is where things start to behave in an unwanted way.

chooseInstance

The database is in a Single User Mode since it is still in the upgrade transaction and no NAV Server instance is connected.  However, in the table “Server Instance” all the old NAV Server instances are listed.  To get through this I needed to press N like a thousand times…

In a test upgrade process like this we need to add another step to the usual upgrade procedure.  After the restore is completed make sure that the tables “Server Instance” and “Active Session” are empty.

After a restore to a new environment it is also good to clean unneeded users from the database.  Only leave what is actually needed.

users

In this case the NAVLIGHT\srvNAV is the service user.  Also make sure that the Compatibility Level is as new as possible and the Recovery Model in line with the backup process.

dbproperty

Also, when moving from one environment to another the users connected to the old domain are obsolete.  Going to Users and trying to remove them results in an error and the user is only disabled.

userdelete

In some cases this could be enough.  It is possible to completely remove the user, just do some housekeeping first.  Find the user in “User Personalization” and Edit the record.  On the Action ribbon we can clean some of the user trails.

clearpersonalization

Finally make sure that the record is deleted from “User Personalization”.

After this cleanup work the user can be safely deleted.