Kine’s Info

Info about NAV

Copy Microsoft Dynamics NAV company by SQL script into another database

After Microsoft Dynamics NAV 2013 R2 release, there are complains about impossibility to copy company from one database to another (you can copy company inside database through standard NAV client interface). Thus I spent some time creating stored procedure which will do it for you… or, this stored procedure will generate script for you, which will do it for you… of course, you can modify the script as you wish to fix possible bugs or extend the functionality and I will be happy when you will share your versions with us.

There is script to create the stored procedure:

USE master
– =============================================
– Author:        Kamil Sáček
– Create date: 18.10.2013
– Description:    Function for copying comany from one database to another
– =============================================
@sourcecompany varchar(max),
@targetdb varchar(max),
@targetcompany varchar(max)
– SET NOCOUNT ON added to prevent extra result sets from
– interfering with SELECT statements.
– Insert statements for procedure here
declare @tablename varchar(1000)
declare @columns varchar(max)
declare @columnname varchar (max)
declare @targettable varchar (max)
declare @isidentity int

DECLARE table_cursor CURSOR for
select name from sys.all_objects where type=’U’ and object_id>0 and name like @sourcecompany+’$%’
OPEN table_cursor

FETCH NEXT FROM table_cursor
INTO @tablename
DECLARE column_cursor CURSOR for
select @columns=”
OPEN column_cursor
FETCH NEXT from column_cursor
INTO @columnname
SELECT @columns=@columns+’,[’+@columnname+’]’
FETCH NEXT from column_cursor
INTO @columnname
CLOSE column_cursor;
DEALLOCATE column_cursor;
select @columns = SUBSTRING(@columns,2,LEN(@columns)-1)
select @targettable= @targetdb+’.dbo.[’+@targetcompany+SUBSTRING(@tablename,LEN(@sourcecompany)+1,LEN(@tablename)-LEN(@sourcecompany)+1)+’]’
print ‘RAISERROR (”’+REPLACE(@targettable,’%',’%%’)+”’ , 0, 1) WITH NOWAIT ‘
IF (@isidentity>0)
print ‘SET IDENTITY_INSERT ‘+@targettable+’ ON’

print ‘delete from ‘+@targettable
print ‘insert into ‘+@targettable+ ‘ (’+ @columns + ‘)’
print ‘    select ‘+@columns
print ‘       from [’+@tablename+’]’

IF (@isidentity>0)
print ‘SET IDENTITY_INSERT ‘+@targettable+’ OFF’

FETCH NEXT FROM table_cursor
INTO @tablename
CLOSE table_cursor;
DEALLOCATE table_cursor;
GOAfter you create the stored procedure on the server, you can use it by running something like this:use myNavDatabase
exec sp_NAVCopyCompany ‘CRONUS International Ltd_’,'NAV2009R2_W1′,’Test’
In output window you will have script with needed commands which will copy the data. Copy it, run it. It will do what you need. If you focus the output window after starting the script (by pressing F6 for example), you will see the tables which are copied.

This script copies all “per company” tables and it is able to copy the data into database, in which you already prepared the company (all existing data in the target company are deleted!). It means, the structure of tables must be same between source and target company!

You can use this script to “update” existing copy of the company to keep e.g. testing company actual

Usage of this script are on your own danger!

I wish you many copied companies without any problem…

October 18th, 2013 Posted by kine | Dynamics NAV, NAV 2009, NAV 2009 R2, NAV 2009 R2, NAV 2013, NAV 2013, NAV 2013 R2 | no comments

Microsoft Dynamics NAV 2013 Tip 1

NAV 2013 is bringing many changes from technological point of view, but even from end-user view. I will try to give you few interesting tips about features, which could be very useful, but could be easily overlooked.

First tip, which is very small and hardly to find, because it is nothing “new”, with some new Action button, but rather re-worked standard functionality.

In NAV 2009 RTC nearly all users were missing possibility to Copy&Paste records. In classic client, users were used to copy records between same tables. It was not possible in RTC. Now, in NAV 2013, you are able again to do that. But on totally another level than in classic client.

NAV Copy&Paste Extended

Try this:

  • Open MS Excel
  • Create this small table:
Item 1000
Resource LINDA
  • Create new Sales Order in NAV Win client
  • Mark the table in Excel, Copy it
  • Go into Sales order lines, Paste the rows (Ctrl+Shift+V or use the context menu) image

Result? New two lines for item 1000 and resource LINDA.

Are you excited? I was too… Just imagine – you do not need Excel import, you only need page with correct columns in correct order and Excel with data. You are done. You can fill the table by just copy&paste.

Of course, data are pasted column by column, row by row. It means, column must be in same order, including non-editable fields, validation is triggered in same order as columns. But you can easily change the field order and visibility through the customization options in Win client or change the column order in excel.

Of course, you can select all rows, copy them into excel, correct the data, paste them back.

I think that this, is one from the invisible jumps which are included in NAV 2013, but are not the correct features for the “big applaus”. But I think that many consultants and end-users will be happy.

Bravo NAV team! :applaus:Thumbs up

June 5th, 2012 Posted by kine | Dynamics NAV, NAV 2013, NAV 2013 | one comment

DynamicsNAV protocol handler

Now, when NAV 2013 beta was released, many partners could struggle from the problem of side-by-side installation of the NAV clients. Problem is, that the development environment is using the URI (hyperlinks) to open the objects in RTC. Because the protocol is handled by default only the latest installed version, you can have problem if you need to use it with more versions. And because that, I have created this handler for you.



After you copy the files somewhere and run the exe, you can activate the handler. Since that, instead the RTC my app will be started for each opened URI. The app will try to find correct version of RTC to run. This could be a problematic, but if you open the URI directly from active classic client, it should work. If you run the hyperlink e.g. from “Run” dialog of windows, the default RTC will be used (there is no way yet to check version of the target NST I know). If you have some tip how to check that…


Have a fun with NAV!

May 18th, 2012 Posted by kine | Dynamics NAV, NAV 2009, NAV 2009 R2, NAV 2009 R2, NAV 2013, NAV 2013 | no comments

Microsoft Dynamics NAV 2013 beta–installation and configuration tips

If you are installing Microsoft Dynamics NAV 2013 beta, you can struggle from some problems. There are some tips for you:


If you are installing the NAV server on computer, where are already some NAV services, you need to use different ports for the NAV 2013 services. NAV 2013 beta is not supporting Port Sharing. You need to think on it when configuring the installation.

Function not supported

When you successfully installed the NAV 2013 client with development environment on PC, where the NAV 2009 is already installed, you can have problems when you try to run something from the devenv. Instead starting the RTC client, you will see error that “Function not supported”. Weird on first sight could be, that you see the error e.g. in your local language, even you have installed the W1 version of NAV 2013. Problem is, that the devenv is using the protocol DYNAMICSNAV to run the RTC. On my PC the NAV 2013 installation have not changed the path for this protocol. You need to check the registry path “HKEY_CLASSES_ROOT\DYNAMICSNAV\Shell\Open\Command” and look if there is NAV 2013 path or not.

You can deduct from this, that coexistence of NAV 2009 and NAV 2013 will be problem. Running page from NAV 2009 classic client will run NAV 2013 RTC after this, or NAV 2013 devenv will start NAV 2009 RTC if not changed. You can decide what will suit you most. But one info: to run the table from NAV 2013 devenv, NAV need to run the RTC, In general, NAV 2013 devenv is more linked to the RTC. I expect that most of you will want to run NAV 2013 RTC through the DYNAMICSNAV protocol. It is question of time till someone will develop some tool which will correctly decide what to run… ;-)

NavUserPassword authentication

Because the Domain groups are not working in the NAV 2013 beta, it could be a problem to add all the users into NAV. You can decide to use the new authentication model “NavUserPassword”, where you create login and password directly in NAV without connection to Active Directory, which you can then share with the users to provide the “demo” access. BUT! It is not so simple. You need to go through few steps:

  1. Change the NST settings – you need to change the ClientServicesCredentialType to NavUserPassword instead the default Windows value.
  2. Create certificate – to be able to login by user name and password, NAV need to crypt the transport. For this, he need certificate. You can create self-signed certificate by using commands mentioned in the .config file:makecert -n “CN=YourServiceNameOrURL” -r -sky exchange -sv YourFileName.pvk YourFileName.cer
    pvk2pfx -pvk YourFileName.pvk -spc YourFileName.cer -pfx YourFileName.pfx
    certutil -importpfx YourFileName.pfx
    Where to take the makecert and pvk2pfx tools? You can take them e.g. from Windows SDK.
  3. Enter the certificate thumbprint into the config – first, you need to look at the Thumbprint on the generated certificat. Open it, find the field thumbprint, remove the spaces and the value enter into ClientServicesCertificateThumbprint key in the config of NST.
  4. Change the RTC authentication type – in the file c:\Users\<loginname>\AppData\Roaming\Microsoft\Microsoft Dynamics NAV\70\ClientUserSettings.config change the settings named ClientServicesCredentialType to same value as in step 1.
  5. Make the client to trust the certificate – import the certificate to the client PC, to trust it. Or you can disable the check by changing value ClientServicesCertificateValidationEnabled in the config.
  6. After all this, you can still have problem to run the NST service. In event log you can see error with error like “no access to the keys” or something similar. It means that the NST service have not enough permissions to read the certificate keys. Try to run it as admin. It should work. Quick fix is to add permissions for the service account on folder c:\ProgramData\Microsoft\Crypto\RSA\MachineKeys\.

Installation of NAV 2009 when compiling objects

After you install NAV 2013, you can have problems with compiling objects with older version of NAV like NAV 2009. When you start compile some object which use automation variable, it will start NAV 2009 installer and will try to repair the installation. It is because registry info about NAV help libraries like NTimer.dll, NSAppHandler.dll etc. were changed. You can repair it by unregistering and reregistering the dlls. I have created batch for this. You can use it (it is designed for 64bit systems). Just create file with this text, save it as .cmd file and run it as admin:


regsvr32 "c:\Program Files (x86)\Common Files\Microsoft Dynamics NAV\OLHandler\OLHandler.dll" /u /s
regsvr32 "c:\Program Files (x86)\Common Files\Microsoft Dynamics NAV\Application Handler\NSAppHandler.dll" /u /s
regsvr32 "c:\Program Files (x86)\Common Files\Microsoft Dynamics NAV\Commerce Gateway Client\CGReqClient.dll" /u /s
regsvr32 "c:\Program Files (x86)\Common Files\Microsoft Dynamics NAV\Commerce Gateway Client\CGTimer.dll" /u /s
regsvr32 "c:\Program Files (x86)\Common Files\Microsoft Dynamics NAV\Communication Component\NPipeBusAdapter.dll" /u /s
regsvr32 "c:\Program Files (x86)\Common Files\Microsoft Dynamics NAV\Communication Component\NSComCom2.dll" /u /s
regsvr32 "c:\Program Files (x86)\Common Files\Microsoft Dynamics NAV\Communication Component\SocketBusAdapter.dll" /u /s
regsvr32 "c:\Program Files (x86)\Common Files\Microsoft Dynamics NAV\Communication Component\MSMQBusAdapter.dll" /u /s
regsvr32 "c:\Program Files (x86)\Common Files\Microsoft Dynamics NAV\Timer\NTimer.dll" /u /s
regsvr32 "c:\Program Files (x86)\Common Files\Microsoft Dynamics NAV\NatHash\NATHash.dll" /u /s
regsvr32 "c:\Windows\SysWOW64\comdlg32.ocx" /u /s

regsvr32 "c:\Program Files (x86)\Common Files\Microsoft Dynamics NAV\OLHandler\OLHandler.dll" /s
regsvr32 "c:\Program Files (x86)\Common Files\Microsoft Dynamics NAV\Application Handler\NSAppHandler.dll" /s
regsvr32 "c:\Program Files (x86)\Common Files\Microsoft Dynamics NAV\Commerce Gateway Client\CGReqClient.dll" /s
regsvr32 "c:\Program Files (x86)\Common Files\Microsoft Dynamics NAV\Commerce Gateway Client\CGTimer.dll" /s
regsvr32 "c:\Program Files (x86)\Common Files\Microsoft Dynamics NAV\Communication Component\NPipeBusAdapter.dll" /s
regsvr32 "c:\Program Files (x86)\Common Files\Microsoft Dynamics NAV\Communication Component\NSComCom2.dll" /s
regsvr32 "c:\Program Files (x86)\Common Files\Microsoft Dynamics NAV\Communication Component\SocketBusAdapter.dll" /s
regsvr32 "c:\Program Files (x86)\Common Files\Microsoft Dynamics NAV\Communication Component\MSMQBusAdapter.dll" /s
regsvr32 "c:\Program Files (x86)\Common Files\Microsoft Dynamics NAV\Timer\NTimer.dll" /s
regsvr32 "c:\Program Files (x86)\Common Files\Microsoft Dynamics NAV\NatHash\NATHash.dll" /s
regsvr32 "c:\Windows\SysWOW64\comdlg32.ocx" /S

This will do it for you. I am not sure if it is just my local problem or it is because NAV 2013 was installed side-by-side with NAV 2009. If you hit this problem, it should help you…

May 17th, 2012 Posted by kine | Dynamics NAV, NAV 2013 | 2 comments

NAV 2013 beta–What’s new

It is time to look at new features in NAV 2013 C/Side. This will be quick overlook of new commands and functionality in the developers environment, which is replacement of the classic client and is intended only for developing now.

New commands

[Ok :=] xRec.SETAUTOCALCFIELDS([Field1] ,…)[Ok :=] xRec.SETAUTOCALCFIELDS([Field1] ,…)

Using this command you tell NAV to automatically calc the FlowFields after new record is read into the variable. You do not need to call CALCFIELDS in the repeat-until loop now and the fields will be calculated in one step with the reading the data.

[SecurityFiltering :=] xRec.SECURITYFILTERING([SecurityFiltering])

The security filter model was changed in NAV 2013 and I hope that it will be much more usable now. Through this command you can set different modes:

  • Filtered – The security filters are applied to the record. Records outside the filter are unvisible for the user and for the code it looks like there are no other records.
  • Validated – Similar to Filtered but if you try access record outside the filter, you will get error. This mode is similar to the pre NAV 2013 behavior and have impact to performance.
  • Ignored – The security filters are ignored for this record variable.
  • Disallowed – If there is some security filter set on the table, it will lead to the error. It means, you can use it in process, where you want to be sure, that it is running by user, which have no security filters set on the table.

For more info I recommend to read the C/Side help topic “Security Filter Modes”.


If you run the Symbol menu, you can find out that there is new group of command for each record variable, just under the “Functions” group. In this Keys group you can select existing keys and insert it into the code. No more manual entry of keys in NAV 2013!

Type := GETDOTNETTYPE(Expression)

This command will help you when using DotNet interop. It will return you the type object for the selected assembly. In NAV 2009 you needed to use workarounds to get the type.


Before you use some assembly, you can test if you can use it. Could help you when you are not sure, if the assembly is installed.


Function will translate the caption class. It is using new function in CU1 with ID 15.

[IsActive :=] CODECOVERAGELOG([NewIsActive] [, MultiSession])

This old command have new parameter – MultiSession. It looks like now you can use codecoverage for all sessions…


No more info on this, it looks like you can enable CodeCoverage only for selected object.


It looks like it reread the codecoverage data.


No info on this. May be similar to previous one…


New command to read the error code.


If there is error, you can read the call stack to be able to show more info to user or log more info somewhere.

New debugger commands

There is whole new set of commands to work with debugger. Because the new debugger is based as Page in NAV, all functions of the debugger are exposed as commands and tables within NAV. It means, if you want, you can create your own debugger in NAV!

New session commands

Classic NAS was replaced with functionality, which allows C/AL to create new sessions which are doing what you need in background. There are new three commands for this:

[{Ok} :=] STARTSESSION(SessionId, CodeunitId [, Company] [, Record])

Start new session, which will run the selected codeunit, in selected company with selected record.


Return the execution mode of the current session. The execution modes are EXECUTIONMODE::Standard or EXECUTIONMODE::Debug.

[{Ok} :=] STOPSESSION(SessionId [, Comment])

You can decide to stop previously started background session.


Will tell you the User SID. Usage? Do not know yet…


If you are using NavUserPassword authentication, you can change the password for selected user through code.

CHANGEUSERPASSWORD(OldPassword, NewPassword)

If you are using NavUserPassword authentication, you can change the password of current user through this function.


Will return the current session ID.


Will tell you ID of your NST.

Test page functionality

There is whole new area of functionality about testing the pages. You can simulate the user actions through the new commands. Than you can run automatized tests of the UI! I recommend to read more in documentation, look at some blog articles, watch the recorded sessions about this…

Query functionality

Again, whole new area of functionality. You can use the Query object to read data through complex select statements and loop through the result in code or save it as XML or CSV file.

DotNet interop changes

You can now consume events of the DotNet assemblies (WithEvents property).

Addin changes

When you are creating addins, you can again create events which you can consume in NAV, or you can add methods, which you can call from NAV. And there is more than that. You can set the Addin behaviour lik automatic resize etc.


There is a LOT of new things in NAV 2013, but I am sure, that there will be LOT of blog articles in next days, because the NDA for MVPs about NAV 2013 beta was lifted. Thus, watch the blogs of my fellow MVP friends for more info…

As you can see, NAV 2013 is really big jump, may be bigger than NAV 5 to NAV 2009 jump.

May 17th, 2012 Posted by kine | Dynamics NAV, NAV 2013 | no comments

Garbage Collector in NAV 2009

Today I learned one new thing:

Garbage collector in NAV 2009 is set to DISABLED by default. Do not ask me what does it mean and why it is disabled. I do not know. Regardless the value DISABLED, this settings just means that Workstation mode is used for GC. Microsoft NAV team have learned, that by enabling it, you can speed up the NAV Server! Are you asking how you can enable it? It is easy, you need only to add this into the Microsoft.Dynamics.NAV.Server.exe.config file:

<gcServer enabled=”true”/>

It is standard Dot.NET config parameter. What I have noticed when I just googled this parameter, that it could (and probably would) raise memory consumption of the process. Of course, because if I understand it correctly, it will not release the memory of created object immediately, after the object is not used anymore, but only when the GC is started (e.g. there is no enough free memory or max time between runs was hit). But this “not releasing” leads to better performance. But, again, this is just my understanding of the problem. I can be wrong. Based on the comment from Per (, the difference is, that Server GC will be used instead workstation GC, which means separate threads on highest priority for the GC, instead running GC in context of user thread. And this means using all available CPU cores etc. and this leads to better performance when doing GC, because during GC run the threads are suspended. But still, that the performance is better, is true.

Please, post into comments your experience with this setting.

I want to thank to Michael Nielsen for this info…

(Edited 7:08 CET 25.4.2012)

April 24th, 2012 Posted by kine | Dynamics NAV, NAV 2009 | one comment

Possible new way to install NAV 2013

Just now, I am in Rome, waiting for tomorrow, when Directions EMEA 2012 begins. Whole Directions will be somehow connected to NAV 2013, and it seems that it will be beginning of blog articles about NAV 2013 and new technologies/functions inside it.


First new feature you can use in connection with 2013 is ClickOnce install. This way of installing and distributing of applications exists since .NET 2. But it was not possible to use it for previous versions of NAV. Now, with upcomming NAV 2013, partners could prepare installations for their customers, which includes all what is needed, like addon libraries and custom client settings.

How it works?

Partner prepares the installation (set of easy steps, which could be automatized – it is out of scope of this article right now), publish it somewhere and send the URL to the customer. Customer just by opening this URL will install the RTC, which will be preconfigured e.g. to connect to his NAV server. Partner than can update the installation e.g. by newer version of addon. This update is automatically installed into the customer’s computer when he click the NAV icon. It means much less effort to keep all customer’s clients up-to-date, no problems with distributing the addons. This is very good in days, when it depends on which build is using each customer and user. On user’s computer there could be more versions/variants of the app installed side by side, each with own config.


But! There are some limits for this feature in connection with NAV:

  • Prerequisites are not automatically installed
  • The install process have no access to the registry (e.g. when addin installation needs something to write there)
  • You cannot run the client.exe directly from outside (ClickOnce is using different way to run the app to check for the updates first etc.) and thus you cannot pass runtime parameters to the client
  • You cannot use the URLs to run the client
  • It is possible to deploy only RTC this way

For me, it is something similar to App-V technology, but much more easy to create the installation and you do not need any special client to be able to use it (and any special server). But the “installed” app is somehow “encapsulated” and thus not working as standard client installed on the PC. Still, it is worth it for partners, which are deploying e.g. cloud solutions or simple solutions, where these features are not used. I recommend to read more about ClickOnce e.g. here: For using ClickOnce with NAV, there will be tools distributed together with NAV 2013 to make the process as easy as possible. I hope, that future will drop the limits in some way, because it will be cool to have this way together with possibility to install the dev. environment and possibility to use links to run the client.

April 24th, 2012 Posted by kine | Dynamics NAV, NAV 2013 | one comment

E-Book Give away winners

Hello all,

thanks to all for the e-mails regarding the e-book give away. I made small overview of most hated/loved feature of NAV 2009 and feature of NAV “7” you are looking for most. I must say, that from the e-mail I can see, that expectation from NAV “7” are very “biased” by rumors and wrong information about what will be in NAV “7”. There are not much info released, but I highly recommend to spend some money and be on events like Directions (Directions EMEA), Convergence, or NAV TechDays. You will get much more info and you will not wait for something, what will not be in NAV “7”.

There is graph of the result:


As you can see, most loved features are WebServices, Addins, .NET Interop and new Reporting (I agree!). Most hated are Reporting and Debugger. And you are looking forward the new Debugger, Queries and web client. Based on my knowledge, if I look at the list of features you hate on NAV 2009, I must say that NAV “7” is step in correct direction in most areas.

That’s the result of my small “research” and I hope, that the winners of the e-book will hate NAV 2009 less after they read the book Veselý obličej.

The winners are:

  • Márton Nagy
  • Martinez Gonzalez Chemanel
  • Peter Rozmus


For rest of you, do not hesitate and use the Packt Dynamics Mayhem capaign to buy your copy for lower price.

I hope that I meet you e.g. on the Directions EMEA in Rome!

February 7th, 2012 Posted by kine | Dynamics NAV, GiveAway, NAV 2009, NAV 2009 R2, NAV 2009 R2 | no comments

“Microsoft Dynamics NAV 2009: Professional Reporting” book give away

hanks to the kind people of Packt Publishing, I’m able to give away 3 e-copies of the book “Microsoft Dynamics NAV 2009: Professional Reporting” written by Steven Renders. What you need to do for that? Send me e-mail to kamil.sacek at gmail dot com with these info:

Subject: Book giveaway


Your Name:

What you like and hate most on the NAV 2009 from technical point of view:

What you are looking for most on NAV “7”:

I will randomly select 3 winners which will receive the e-books (deadline for the emails is 23:59:59 5.2.2012 CET). This give away is running on all my blogs together, it means only 3 e-books for all 3 blogs sites I am using…

January 31st, 2012 Posted by kine | Dynamics NAV, GiveAway, NAV 2009 | no comments

NAVTechDays 2012!

For all technical people around Microsoft Dynamics NAV: Be informed, that next NAV TechDays will be THIS YEAR! Who wants to learn new things and see many interesting tools/applications/hints/tips etc. – mark the date September 27.-28. 2012 in your calendar! Place will be same, Antwerpen, Belgium. Who missed the last conference, do not hesitate and plan your trip! It is a must for everyone to be there at least once… Veselý obličej

Sorry for so many exclamation marks in a so short text, but I am excited…


Thanks Luc, I am looking forward it!


I hope I will see you all there!

January 16th, 2012 Posted by kine | Dynamics NAV, NAVTechDays | no comments