Copy Microsoft Dynamics NAV company by SQL script into another database v2

Because the first script I have released was “two step job” and it is too complicated to use it in some automated way, I have prepared this new script, which is only modification of the original. This new script directly copy the data in one go, you do not need to save the script and execute it in second step. This SP do not need to be marked as system.

 

-- =============================================
-- Author:        Kamil Sáček
-- Create date: 18.10.2013
-- Description:    Function for copying comany from one database to another
-- =============================================
CREATE PROCEDURE [dbo].[sp_NAVCopyCompany_v2] 
    @sourcecompany varchar(max), 
    @targetdb varchar(max),
    @targetcompany varchar(max)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    -- 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 @sqlcommand nvarchar (max) = 'select name from '+@targetdb+'.sys.all_objects where type=''U'' and object_id>0 and name like '''+@sourcecompany+'$%'''
    declare @sqlcommandIdentity nvarchar (max)
    declare @tablevar table(name varchar(300))
    declare @columntablevar table(COLUMN_NAME varchar(300))
    declare @identitytablevar table(C int)
    insert into @tablevar(name) exec sp_executesql  @sqlcommand
    DECLARE table_cursor CURSOR for
      select name from @tablevar
    OPEN table_cursor

    FETCH NEXT FROM table_cursor 
    INTO @tablename
    WHILE @@FETCH_STATUS = 0
    BEGIN
        --RAISERROR (@tablename, 0, 1) WITH NOWAIT 
           set @sqlcommand = 'SELECT COLUMN_NAME FROM '+@targetdb+'.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '''+@tablename+''' and COLUMN_NAME <> ''timestamp'''
        DELETE from @columntablevar
        insert into @columntablevar(COLUMN_NAME) exec sp_executesql  @sqlcommand
        DECLARE column_cursor CURSOR for select COLUMN_NAME from @columntablevar
           
        select @columns=''
        OPEN column_cursor
        FETCH NEXT from column_cursor
        INTO @columnname
        WHILE @@FETCH_STATUS=0
        BEGIN
            SELECT @columns=@columns+',['+@columnname+']'
            FETCH NEXT from column_cursor
            INTO @columnname
        END
        CLOSE column_cursor;
        DEALLOCATE column_cursor;
        select @columns = SUBSTRING(@columns,2,LEN(@columns)-1)
        --RAISERROR (@columns, 0, 1) WITH NOWAIT 
        select @targettable= @targetdb+'.dbo.['+@targetcompany+SUBSTRING(@tablename,LEN(@sourcecompany)+1,LEN(@tablename)-LEN(@sourcecompany)+1)+']'
        --
        select @isidentity=COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =@tablename AND COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
        --
        set @sqlcommandIdentity = 'SELECT COUNT(*) as C FROM '+@targetdb+'.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='''+@tablename+''' AND COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, ''IsIdentity'') = 1'
        DELETE from @identitytablevar
        insert into @identitytablevar(C) exec sp_executesql  @sqlcommandIdentity
        select @isidentity=SUM(C) FROM @identitytablevar

        RAISERROR (@targettable, 0, 1) WITH NOWAIT 
        set @sqlcommand = ''
        IF (@isidentity>0)
          set @sqlcommand = @sqlcommand + 'SET IDENTITY_INSERT '+@targettable+' ON;'

        set @sqlcommand = @sqlcommand + 'delete from '+@targettable+';'
        set @sqlcommand = @sqlcommand + 'insert into '+@targettable+ ' ('+ @columns + ')'
        + '    select '+@columns
        + '       from ['+@tablename+']' 

        IF (@isidentity>0)
          set @sqlcommand = @sqlcommand + ';SET IDENTITY_INSERT '+@targettable+' OFF'
        --RAISERROR (@sqlcommand, 0, 1) WITH NOWAIT 
        exec sp_executesql @sqlcommand
        FETCH NEXT FROM table_cursor 
        INTO @tablename
    END 
    CLOSE table_cursor;
    DEALLOCATE table_cursor;
END



.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 



This SP could be run like this:



use [Demo Database NAV (7-1) W1]
sp_NAVCopyCompany 'CRONUS International Ltd_','[Demo Database NAV (7-1) W1]','Test'


.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 



Have a nice copies of the companies!

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
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Kamil Sáček
-- Create date: 18.10.2013
-- Description:    Function for copying comany from one database to another
-- =============================================
CREATE PROCEDURE sp_NAVCopyCompany 
    @sourcecompany varchar(max), 
    @targetdb varchar(max),
    @targetcompany varchar(max)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    -- 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
    WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE column_cursor CURSOR for
            SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @tablename and COLUMN_NAME <> 'timestamp'
        select @columns=''
        OPEN column_cursor
        FETCH NEXT from column_cursor
        INTO @columnname
        WHILE @@FETCH_STATUS=0
        BEGIN
            SELECT @columns=@columns+',['+@columnname+']'
            FETCH NEXT from column_cursor
            INTO @columnname
        END
        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)+']'
        select @isidentity=COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =@tablename AND COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 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
    END 
    CLOSE table_cursor;
    DEALLOCATE table_cursor;
END
GO
------------ Update 6.11.2013--------
After that you need to mark the SP as system by running this, else the SP will return no data (will work with master DB instead actual DB):
 
EXEC sys.sp_MS_marksystemobject sp_NAVCopyCompany
------------End of update -----------

After 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 Veselý obličej


Usage of this script are on your own danger!


 


I wish you many copied companies without any problem…

Microsoft Dynamics NAV 2013 R2–What’s new?

Long time passed since my last blog article and many things happened. We have bought new flat (which we are now preparing for us to move there), I have lot of work with our developers to make the development more smooth and more and more…


But today is big day for Microsoft Dynamics NAV 2013, because new release (R2) is right now presented to public on Directions EMEA. Because that, NDA is dropped and I want to show you the main changes which it brings. And some of them are not only some “facelifts”.


New and modified features of NAV 2013 R2


Multitenancy


What is it? It is feature which allows you to separate the Business logic into separate database on SQL and apply it to more data databases (e.g. each for customer). It means you maintain one set of objects, but you are using them for multiple databases. And these databases are accessible through one or more NAV Servers (one NAV Server can now work with multiple databases). This feature is mainly for hosting partners which can now serve many customers in separate databases with much less effort. E.g. upgrade of the data database is done “just” b moving the database from one tenancy to another. Of course, there are many technical issues with this separated business logic from the data (some data are still part of the business logic database –mainly the system wide settings etc.) like changing table definition, because this change must be done in many databases which can take time (and some data databases could be off-line during this change) and thus this change is done off-line by the engine in background.


Of course, you can still use the NAV 2013 R2 as you are used to, as one SQL database. Who need that, can use it. But I think, not many partners will start use the multitenancy in near future.


Help Server


To solve problems with accessing on-line help from web client, Microsoft added the Help Server component into the product. During install of NAV you can install it to your IIS server. It will include all what is part of the on-line help in older versions as plain HTML pages with some engine around (search, jump list…). Partners can modify and extend the help with their own pages as they are used to, but now you do not need to update the local help on all clients. Same help is accessed from Windows client and from web client.


The URL of the help server is entered when installing clients (in web.config of the web client and in the local config of each Windows client), thus do not forget to enter correct URL and use e.g. public URL when the client will be used outside your local network (and you need to publish the help server for such an users).


On-line document storage integration


NAV 2013 R2 is prepared for integrations with e.g. Office 365 service. You can use these services to store your documents and link them into NAV or Export to Excel using the Office 365.


Writable OData


OData protocol is now writable, thus external applications could use this protocol to write into NAV in easy way.


Dynamic flat file XMLPort


XMLPort used as replacement for old dataport (importing/exporting flat files) have new features which allows developers to create more dynamic import/export. Last field in XMLPort could be set as “Unbound” (you do not know how many fields will be there) and trigger is fired for each column, until developer calls currXMLport.BREAKUNBOUND. Thus generic import export functions could be done and this feature is used in the new Banking module in NAV 2013 R2 for importing the Bank statements and exporting Bank payments.


You can change the field/record/table separator, field delimiter and encoding for the XMLPort in run-time through new properties.


Modern style UI


Of course, the UI of both clients (web and windows) was re-designed to match the Microsoft Modern Style. The design is now much simpler. If you like it or not, it is on you…


image



Extended Web client


The web client is now much better. It could include charts now and you can extend it by add-ins written in JScript. You can use the on-line help thanks to Help Server. Only small tip: if you want to see help topic for field, keep cursor over the field label until the hint is displayed and the cursor changes to cursor with question mark.


New C/AL commands and properties


  • GetURL – create the URL for specific client, company, object and record
  • TENANTID – ID of the Tenant under which you are running
  • COPYCOMPANY – you can copy company from one name to another from C/AL
  • Report-PreviewMode property – you can select which layout is default for preview (default or Print Layout)

Report Builder


Developers can now use Report Builder to design the layout for report. This could be enabled in the Options of the Development Environment. It means no Visual Studio is needed to design the reports.


 


Closing


And I think that there are many things I not mentioned. I expect that there will be much more info about each new thing i next days on blogs and tweets. I wish you many happy hours with NAV 2013 R2, because I think it is best ERP today!

Microsoft Dynamics NAV 2013 Tip 2

After long time I have found some free time and subject worth to write something. During my work on my DynamicsNAVProtocolHandler (with some help from Daniel Steiner from redIT) I have found one thing I didn’t know about in NAV 2013.

Do you need to know server, instance or some setting of the NAV Server you are connected to?

If you need something regarding the NST you are just using in the NAV Win client, you can be inspired by code from codeunit 9500. There is one DotNet class you can use for this:

Microsoft.Dynamics.Nav.Types.ConfigSettings

If you use this class, you gain access to the server settings. And if you are lucky, you can even change the settings (property CanSetSettings will tell you that). This class have method to read any setting by entering the name of the setting, or there are specific properties for the most used.

Bug in CU 9500

You can hit this bug when you are running NST on non-default port and you want to open session list. Because the session list is opened by CU 9500 by generating hyperlink and launching it, you can correct this bug easilly. The code is just not taking into account the server port. We are lucky, that this part is in C/AL and not hardcoded into the client. Just extend the code like this:

   1:  

   2: ServerComputerName := Config.GetStringSetting('Server');

   3: ServerInstance := Config.GetStringSetting('ServerInstance');

   4: ServerPort := FORMAT(Config.ClientServicesPort); //added line

   5:  

   6: //Add parameter %4 into the URL

   7: UrlString := STRSUBSTNO('DynamicsNAV://%1:%4/%2/%3/debug',ServerComputerName,ServerInstance,COMPANYNAME,ServerPort);

   8: HYPERLINK(UrlString);


 


You can see both ways of reading the settings:


  • through the GetStringSetting
  • through property (CientServicesPort)

Conclusion


Thanks for the DotNet interop and the standard NAV assemblies which allows NAV developer to have access to NAV inside values! I hope that I will find more connections between .NET and C/AL like access to the actions etc. from within C/AL code.


 


Have a nice days with playing with C/AL!

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:image

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

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

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.


Download v 1.0.2.0


NVR_DynamicsNAVProtocolHandler


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…


 P.S.: Using this handler, the Windows 7 “Jump Lists” will not work for NAV. It means, you will not have the new feature of Windows 7, when you can directly from the application icon start some functionality without opening the client first. This is working only when the handler is deactivated and you run the RTC once after that.


P.S.: 18.5.2012 11:09 – download updated to v 1.0.2.0


P.S.: 19.5.2012 17:01 – project moved to http://navprotocolhandler.codeplex.com/, new versions released (with some bugs fixed)


Have a fun with NAV!

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:

Ports

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


------------------




.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

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…

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


Keys

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.


Ok := CANLOADTYPE(DotNet)

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.


String := CAPTIONCLASSTRANSLATE(CaptionClassText)

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…


CODECOVERAGEINCLUDE(ObjectRecord)

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


CODECOVERAGEREFRESH

It looks like it reread the codecoverage data.


CODECOVERAGELOAD

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


String := GETLASTERRORCODE

New command to read the error code.


String := GETLASTERRORCALLSTACK

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.


ExecutionMode := CURRENTEXECUTIONMODE

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.


USID := USERSECURITYID

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


SETUSERPASSWORD(USID, Password)

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.


ID := SESSIONID

Will return the current session ID.


ID := SERVICEINSTANCEID

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.


Conclusion


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.

Directions EMEA day 1

Ok, it is late and I am tired. But I will pack the day into few sentences:

  • Page preview in page designer – interactive preview of the page directly in C/SIDE, clicking on the object in the page select the corresponding line in the designer or you can open properties for the object (action/field)
  • “Find” function is back – you can again instantly search for specific data in the actual column
  • Variable filters – when filtering in the RTC, you can use “variables” like “%mycust”, which will be “expanded” to e.g. “10000|20000|300000” based on your customer selection. This replacement and expansion is handled with special codeunit in NAV, thus you can add own variables (some other default are already there like “%today”). Saving view with this variable in filter will save the filter including the variable, thus by changing the data will have effect to the saved view (each time when opening the view the variable is replaced with the values)
  • New tool to quickly setup the new company (something like RIM). Partners could add own packages, which will add new settings.
  • Cloud-RTC-debugger  — all is working together.
  • Query and OData – new way how to get the data from NAV to outside and consume them, e.g. in PowerPivot. Query generate complex SELECT statement on SQL with possible JOINS, GROUP BY and preselected set of fields. This could be consumed through OData protocol (something like RSS feed) which could be filtered.
  • New version of NAV should be released ONCE per YEAR! – partners needs really think about this and how to keep all customers up-to-date!

And short list from Waldo’s presentation:

  • Remote Desktop Manager
  • smtp4dev (www.nav-magno.be)
  • Codegenius
  • Mantis bug tracker
  • iFacto Revision

Ok, going into bed. Regarding details, I think more blog articles are needed, but right now, I still do not know how deeply I can go and not hit the NDA… you need to wait for the correct time.

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:


<configuration>
   <runtime>
      <gcServer enabled=”true”/>
   </runtime>
</configuration>


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)