Error : Microsoft Dynamics NAV,The following SQL error was unexpected.

When we try to login with a user who does not have dbo-rights,we will get the following error

Error : Microsoft Dynamics NAV
---------------------------

The following SQL error was unexpected.
The SELECT permission was denied on the object 'User Property', database 'Cronus TEST', schema 'dbo'.

---------------------------
OK
---------------------------


Solution :

We can resolve the issue to run the following though sql management studio (replace tblYourTableName with your table name) 

grant select on dbo.tblYourTableName to public

How to Repeat Table Header on Each Page in NAV 2013 & NAV2013R2 Reports

After NAV2013 that the table header doesn't repeat when there is a next page.
Even the table properties Boolean Repeat Header Rows on New Page doesn't help.

So let's see how we can repeat header in New Page.

I will be doing it for NAV2013R2, and using Report Builder. The Steps Remain Same for NAV2013 also.

The following setting are required to repeat the header in New Page

1. Open the Layout of Report in

2. In the Column Group Pane Select Advanced Mode
3. Once in advanced mode you will see static Rows for Each Header Line

4. For Each Header Row that you want to repeat on New Page, select the corresponding Static Row and set these properties.

- Set KeepWithGroup to AFTER
- set RepeatOnNewPage to TRUE.


 When you click on a static Row, the corresponding row is selected in the table.

If you want to repeat all four lines in New Page, Repeat the steps for For all Header Rows.

Save the Report and Run in Role Tailored Client.

How to create a new ServiceTier in NAV2013R2

     1. Create a copy from the service-tier folder. (for example C:\Program Files\Microsoft Dynamics NAV\71\Service)

     2. Change the settings in file Customsettings.config. (database and instance name)

  

     3. Change in query below the values NewServiceTierName into the new name of the service-tier 

        Execute below query With PowerShell (Run As Administrator)

     4. New-Service -Name 'MicrosoftDynamicsNAVServer$NewServiceTierName' -BinaryPathName '"c:\Program Files\Microsoft Dynamics NAV\71\NewServiceTierName\Microsoft.Dynamics.Nav.Server.exe" $NewServiceTierName /config "c:\Program Files\Microsoft Dynamics NAV\71\NewServiceTierName\Microsoft.Dynamics.NAV.Server.exe.config"' -DependsOn 'NetTcpPortSharing' -Description 'Service handling NewServiceTierName' -DisplayName 'Microsoft Dynamics NAV 71 Server [NewServiceTierName]' -StartupType Automatic

     5 Execute with Command Prompt (Run as Administrator) the query: (change also NewServiceTierName)

      sc config MicrosoftDynamicsNavServer$NewServiceTierName depend= NetTcpPortSharing/HTTP

      6  Go to Services.msc and change the service-account from the new created service-tier into ….

      7  Start new service-tier.

How to Debug RTC Pages in NAV2009

Debugging RTC Pages in NAV2009

   1.      Go to C:->Program Data-> Microsoft->Dynamics Nav->60->Server->Microsoft  Dynamics Nav Server->Source -> Codeunit. Here you can find the source of the pages DLL files written in C#, if the    pages are generated successfully.

   2.      Go to C:\Program Files (x86)\Microsoft Dynamics NAV\60\Service. Give full control to                      CustomSettings.config file.

   3.      Opne CustomSettings.config file. Make the enable debugging key to true by editing the following            settings for making the RTC page debugging possible :

 <add key="EnableDebugging" value="True"></add>

   4.      Run Visual Studio as Administrator.

    5.      Open the RTC page DLL file.

    6.      Go to Tools->Attach Process. Place check mark in Show Process from All Users check box.

    7.      Select the Nav Server process .

    8.      In the Page, put breakpoints wherever required.

    9.      Run RTC page in Nav. Then in Visual studio the code execution will be stopped at the break point.

               This way, you can make the RTC pages debugging possible.

                                                       ***Thank you very much***

Upgrade NAV5SP1 to NAV2013

Prerequisites:

The below database objects are required to go upgrade NAV5SP1 to NAV2013.

NAV5SP1 Base Database

NAV5SP1 customer Database

NAV2009R2 Base Database

NAV2013R2 Base Database

To Upgrade from NAV5Sp1 to NAV2009R2 Data Upgrade is not necessary, follow the steps below

·        Merge the customized objects

·        Open/Convert the database in Nav 2009 R2

·        Delete all the objects except tables

·        Cleanup the data which are not required. Like some add-ons might not require to upgrade,etc.

·        Import the latest merged Objects.

·        Transform Forms to page using Transformation Tool, if we find any matrix forms then we need to convert those matrix form to pages manually.

·        Customized Reports needs to be converted to RDLC

·        Compile the objects and resolve the compilation errors

1.   Update the Application Code.

Any customization in the customer’s old database must be implemented in the       new database

2.   Export all the objects in the new customized Microsoft Dynamics NAV 2013 database to an object file: objects.fob.

3.   Prepare for the data conversion.

a.   Make sure that you are the only user connected to the database.

b.   Back up the database and save the backup file in a safe place.

c.    Make a copy of the customer’s database in SQL Server Management Studio. You will upgrade the copy and keep the original.

d.   Verify that both your partner development license file and customer license file have been updated to Microsoft Dynamics NAV 2013.

e.   Identify a User ID and password for a superuser in the system or create a new superuser that you can use for the upgrade process. Log in as the superuser when you perform the upgrade.

f.     Verify that you have the dbcreator and securityadmin Server Roles for the Microsoft Dynamics NAV SQL Server instance, using SQL Server Management Studio. To manage companies, objects, and licenses within a Microsoft Dynamics NAV database you must also have the db_owner database permission.

g.   Disable integration properties for the database.

h.   Open the database in the Microsoft Dynamics NAV 2009 Classic with Microsoft SQL Server client.

i.     Run a database test to determine the state of the customer’s database.
Note: The database test can require a significant amount of time, depending on the size of the database.

4.   Import Upgrade Step 1 Objects

Import Upgrade601700.1.fob.

5.      Run the “Adjust Cost-Item Entries” batch job to ensure that the inventory cost data in the customer’s database is up to date.

6.   If your organization posts inventory costs to the Microsoft Dynamics NAV general ledger, run the “Post Inventory Cost to G/L” batch job.

Run the job for each company in the database.

7.      For the SCM feature, make the changes to tables 32, 83, 89, and 5841 described in the Update Guide.

8.   Perform Step 1 data conversion.

a.    In the Classic client, open Object Designer, and then open form 104001, Upgrade - Old Version.

2. Choose Run.
3. Choose Transfer Data.

Repeat the conversion for each company in the database.

9.   On form 104001, choose Delete Objects.

This runs codeunit 104002, Delete Old Objects, which deletes all of the objects that are not tables. It also deletes tables that belong to the features that are discontinued in Microsoft Dynamics NAV 2013.

10.Create the New Microsoft Dynamics NAV Database.

This involves installing Microsoft Dynamics NAV 2013, and then opening your database in the Microsoft Dynamics NAV 2013 development environment.

See the Upgrade Guide for details.

11.Change the compatibility level of the database.

For SQL Server 2008 and SQL Server 2008 R2 editions, verify that the compatibility level of the database is set to 100. For SQL Server 2012, the appropriate compatibility level is 110. To set the compatibility level, run a query like the following in Microsoft SQL Server Management Studio:

USE [master]; ALTER DATABASE [YourDatabaseName] SET COMPATIBILITY_LEVEL = 100

12.   Import all customized Microsoft Dynamics NAV 2013 objects.

13.   Compile all objects.

14.Import Upgrade Step 2 objects.

 Import Upgrade601700.2.fob in the development environment.

15.Set the language

In the development environment, choose Tools, choose Language, and then select the language of the old customer database.

16.Perform Step 2 data conversion.

1. Open page 104002, Upgrade - New Version.
2. Choose Run.
   This opens the Windows client and displays page 104002, Upgrade - New Version.

Choose Transfer Data.
This runs codeunit 104048, Upgrade NAV 2009 Step 2.

17.Update source codes.

Run page 257, Source Codes, in the Windows client. Note any source codes that have been added, and then update the Source Code and Source Code Setup tables for any additional source codes in the new version.

18.Initialize all companies.

For each company, run codeunit 2, Company-Initialize and open page 531, Setup Checklist.

19.Update data in tables that are not specific to a single company.

This includes tables 2000000003 (Member Of), 2000000004 (User Role), 2000000005 (Permission), 2000000053 (Windows Access Control), and 2000000203 (Database Key Groups).

20.   Upgrade Roles (Permission Sets) and Permissions.

21.Delete obsolete tables

a.    Open Object Designer

b.    Open form 104002.

c.    Run Upgrade - New Version.

d.    Choose Delete, and then choose Mark Unused Old Tables.

e.      Set a filter on the Version List column to select tables marked as “Old Unused Table - marked for deletion.

f.       The Edit menu, choose Delete to delete the tables.

22.Delete Upgrade Toolkit Objects

1. In form 104002, choose Delete, and then choose Mark/Delete Upgrade Toolkit.
2. All Upgrade Toolkit objects, except tables, are removed automatically. The tables are marked for deletion.
3. Set a filter on the Version List column to select tables marked as “Upgrade Toolkit Tables - marked for deletion.”

On the Edit menu, choose Delete to delete the tables.

23.Test the database

a.    Test everything except field relationships between tables. If the test fails, follow the workflow for repairing damaged databases.

b.    Run the remaining part of the database test, which is to test field relationships between tables.

Test the upgraded item ledger entry invoiced quantities to make sure that any date compressed item ledger entries have been upgraded correctly.

                                                     

                                          ***Thank you very much ***

Hooks Pattern in NAV

What is Hooks Pattern?

When doing development over years, by different developers with different mind-sets, the standard code base gets changed a lot, adding multiple lines of code, adding local and global variants, adding or changing keys, changing existing  business logic.  In other terms, the standard text objects are being changed all over the place.

After years, it's not clear why a change was done, and what was the place where the change was intended to be done.  And the latter is quite important in an upgrade process, when code in the base product is being refactored: if the exact place of the posting of the Customer Entry is being redesigned to a separate number, the first thing I need to know, is that I did a certain change at the place: "where the posting of the Customer Entry starts".  The definition of that place, we call a "Hook"

Hooks Pattern follows the below process

First of all, name the places in the already existing code where customization is needed, All objects of the default applications that need to be changed

·        Second, place your business logic completely outside the already existing application code.

  On objects that should not hold any customized business logic (like tables, pages, XMLPorts)

Process:

Step1 - create your Hook code unit. This is always a code unit.  We apply the following rules to it:

• One Hook always hooks into one object.  Which basically means that I will only declare this new code unit in one other object (which is its parent object)
• The naming convention is: The_Original_Object_Name Hook.  Naming conventions are important, just to find your mapped object, and also to be able to group the Hooks.

Step2- you create the hook, which is basically a method (function) in your hook code unit.  The naming is important:

• The naming of the hook should NOT describe what it is going to do (So, examples like CheckMandatoryFields, FillCustomFields should not be used as a hook)
• The naming of the hook should describe WHERE the hook is placed, not what the hook will be doing (as nobody is able to look into the future 
• To help with the naming, it is a good convention to use the "On"-prefix for these triggers.  This way, it's very clear what are hooks, and what aren't.

Step 3- it's time to hook it to its corresponding object and right place in the business logic of that object.  You do this by declaring your code unit as a global in your object, and using the created hook function on its place in the business logic.  This way, these one-liners apply:

• A Hook code unit is only used once in one object only (its corresponding object)
• A Hook (function) is used only once in that object.  As a consequence, changing the parameters has no consequence: you only need to change one function-call
• The code unit is declared as a global.  That exact global is the only custom declarations in the existing object. Everything else is pushed to the hook-code unit.

Step 4- implements your business logic in the hook.  Do this in the most atomic way, as there is a good chance that this same hook is going to be used for other business logic as well.  Best is to use a one-line-function-call to business logic, so that the Hook Function itself stays readable.

Example

Suppose, we want to add business logic just before posting a sales document.  In that case, we have to look for the most relevant place, which is somewhere in the "Sales-Post" codeunit.  So:

Step 1: create code unit Sales-Post Hook:

 

 Step 2: create the hook function OnBeforePostDocument:

Step 3: declare a global in the "Sales-Post"-codeunit, called "SalesPostHook".  Then, call the Hook Function that you created in Step 2 in the right place.

 

Step 4: implement the business logic, by calling out to a new function.  And implement the test-code unit. 

Pros:

·        It will make the upgrade process much easier.

·        It will be very readable what is being customized on a certain place in an existing part of the application.

·        It is specially help full in handling large customization.

·        It easy, elegant, and very manageable.

·        Some time we can re-use the code.

Cons:

·        We have to keep Standard function and hook functions in sync always.

·        It is mostly not useable, when most changes happen in the more places in one object.

·        Making tricks and putting code in different places is making code hard to read and un-necessary complex.

·        It may increase the add-on cost because of extra objects and extra time(Assumption)

·        We can’t use in-built functions in Hook code units like currPage.xxx,

·        Test the whole functionality.

·        More investment and less benefit, because Microsoft already released merge tool from NAV2015, it saves our merging time.

Risks: 

·        Time factor, only the add-on is already developed without hook

·        Cost, it needs to more time for refactoring the whole logic.

·        Hook code unit naming conventions might collide when the application is using more than one add-on from different partners. Suppose three different partners are using the same hook name like sales post-hook with three different ID, system doesn’t allow while importing the objects.

·        Hook functions naming conventions is always as challenge. It must be easily understandable when we have multiple hooks in the same trigger.

·        It is always complex while merging\upgrading objects when the source version doesn’t have the hook pattern and destination version has, in case of customer has more add-ons from different partners.

·        Current record fields return variable and global variable handling is very complex.