The Query object type was introduced in Microsoft Dynamics NAV 2013 for use in a limited number of places:

• In the programming similar to a record variable
• As source table in a Generic Chart
• As an oData web service

This was a bit disappointing to me. I had hoped that the Query could function as a source table for other objects as well. I have therefor been experimenting with how to use Query objects as sources for both Reports and Pages. This is what I found out.

Firstly we need to create a Query object.

Creating the Query

So I create a Query Object that will calculate the customer top 10 sales, enabling filtering on Posting Date.

The dataset looks like this:

Notice that I create two filters:

• Document Type to be hardcoded in the Query as Filter: “Invoice|Credit Memo”
• Posting Date to be set when calling the Query from outside.

Also, notice that I made Sales LCY into a total.

I change the Query properties to sort by Sales LCY and to show only the first 10 rows:

Now I am ready to test the Query by running it from the designer and compare it against the Customer Top 10 Report:

This looks good, so now onto the report.

Creating the Report

Since the query object cannot be used directly as the dataset, an integer table will be used as dataset:

Then creating the query as a Global variable:

The Date variables is to be used on the Request Page:

And then a little bit of code:

So:

• Set the filters
• open the query in the OnPreDataItem
• Read for each Query record
• Break after the last record
• Close the query

Then now we need to insert the Query Columns in the Dataset:

Using the Query Object the column: “Include Captions” cannot be used and custom captions need to be created in the Labels Designer:

Now the dataset is ready to test by running the report, previewing it and pressing Ctrl+Alt+F1 (About this Report) showing the dataset:

And now the Layout can be created in visual studio:

And now previewing the report:

Not pretty, but let’s see if a bit of “Pimping” can do the trick:

Conducting courses in Development of Microsoft Dynamics NAV 2009 and 2013, I hear a lot of complaining about the RTC report generator in and the link between NAV and Visual Studio. This report all included took me two hours to make and next time it’ll only take me one hour, so stop whining and learn using the almost endless possibilities there are instead.

Creating the page

If I want to use the query in a page instead, It’s almost simpler. Create the page again using the integer table. There is no use of the wizard unless FactBoxes are needed. Then Create the Query as a global variable:

Use the columns from the query as fields on the page:

And a little bit of code to:

• Find the number of rows
• Set the filter on the Integer table
• Read the Query in the main loop

It looks like this:

And the result:

This could also be “Pimped” with FactBoxes, Charts and others.

But you can do that yourself.

Touring with my “What’s New Microsoft Dynamics NAV 2013 Installation & Development” course, one of the frequent topics are upgrading reports from earlier versions of Navision or Dynamics NAV to Dynamics NAV 2013. On demonstrating the upgrade process, I have stumbled upon several pitfalls. Actually there are so many shortcomings, I personally find, that if the report was originally based on a standard Dynamics NAV report, it will be faster to move the changes to the new standard Dynamics NAV 2013 report. However many reports exists in customer solutions that are 100% tailor made, and here we have only one option: upgrading.

Our problems origin from one of the following issues:

• The form variable type is discontinued replaced by the page variable type.
• The FORM object is discontinued replaced by the PAGE object. Meaning that all FORM.RUN commands must be replaced by PAGE.RUN commands.
• The Request Form is discontinued replaced by the Request Page.
• Posted dimensions and ledger entry dimensions are stored differently in Dynamics NAV 2013.
• Code is no longer executed when placed in a hidden text field in the Page Header area of Visual Studio. This was possible in 2009 and very often used especially with CODE.GETDATA and CODE.SETDATA. Just to confuse matters; this only applies to printing the report. Previewing the report will look nicely (and you can actually print the report from preview – also invoices).

Upgrading to Dynamics NAV 2013 we have two situations:

• We upgrade from a report run from the Classic Client, In this case the function from Dynamics NAV 2009 “Create Layout Suggestion” if performed automatically on upgrading.
• We upgrade from a report run from an RTC Client in Dynamics NAV 2009. Here the Dynamics NAV 2009 RTC Layout is used.
  

  Let us take an example with two reports that originated from a 5.0 Classic:

1. Report 25 Account Schedule
2. Report 206 Sales Invoice

Importing the Reports into a Dynamics NAV 2013 database, the reports are not compiled. Trying to compile the report will give you the first error found by the compiler. However it is not possible to design the report. We therefor need to import the objects to a Dynamics NAV 2009 database, where all commands for both the RoleTailored Client and the Classic Client exists.

On upgrading from a report run from the Classic Client there are a number of things we need to prepare:

As mentioned, the first thing is to import the objects to a Dynamics NAV 2009 Database. The reason for this is that here we can change all variable defined as form to the page type as well as changing all FORM.RUN commands to PAGE.RUN. Then we need to remove all commands that will not work in 2013, which again means all commands referring to RequestOptionsForm.

Starting with the 25:

• the first error message refers to a variable named DimValList which has the Form type. This must changed to the page type.
• Second Error is the RequestOptionsForm control. This must be removed and replaced with an equivalent page command if necessary.

Every time, the object must be exported from the 2009 database and imported to the 2013 database for testing. Whenever the object can be imported to the 2013 database in compiled state, it is ready to be upgraded and further design.

Upgrading the report to Dynamics NAV 2013 is performed with a new function Tools/”Upgrade Reports”.

It will:

1. Upgrade to the Dynamics NAV 2013 Dataset Designer. Which means that it takes all fields from the Section Designer and create them as columns in the Dataset Designer.
2. Delete the Request Form
3. Generate a Layout, using the Create Layout Suggestion.

After this, you must:

1. Adjust the dataset to correspond to Dynamics NAV 2013, especially regarding dimensions.
2. Move all labels from the dataset to the Label Designer. This is not imperative but it will upgrade the report to Dynamics NAV 2013 standards.
3. Manually adjust the Layout.
4. Create the Request Page.

Trying the same with report 206:

• The RequestOptionsForm control must be removed and replaced with an equivalent page command.
• Remove all dimension loops (Lines and header) from the dataset AND from the section. Remember the Variables pointing to Table 359. Alternatively, table 359 Posted Document Dimensions must be imported in the Dynamics NAV 2013 database, only to be removed again, when the dimension functionality has been replaced with Dynamics NAV 2013 functionality.
• Import and Upgrade the Report

The upgrade function now try to fit everything into the page header. However without using the CODE.SETDATA and CODE.GETDATA. It therefore defined a large number of textboxes, named as variables, to be used as report items. They look something like this:

Which is not very elegant. These fields can be moved onto another Tablix control, which again can be hidden. However it is not possible to omit it and address the Field!Variable.Value from the dataset directly. This is due to the fact that it will always translate to the command first(field!Variable.Value), which will make absolutely no sense if you are printing more than one invoice at a time, because it will then repeat the customer from the first invoice on all invoices.

This boils down to what I wrote earlier: Start over using a standard Dynamics NAV 2013 report and move or add the customer adaptions again.

Using the same example with the same two reports this time originating from a 2009 RTC:

All the same issues described above also applies to the Dynamics NAV 2009 RTC reports. So on it again:

• Remove all variables with the form variable type.
• Replace all FORM.RUN by PAGE.RUN commands.
• Delete all references to RequestOptionsForm.
• Remove all references to Posted Document dimensions or Ledger Entry Dimensions.

So now we can Import the objects and upgrade them. Since the layout is already defined in the objects the upgrade process will reuse the existing layouts. And it does so quite well.

Only one thing need to be changed: Since Code is no longer executed when placed in a hidden text field in the Page Header area of Visual Studio, we now need to move the execution of the code from the value expression of the field to the hidden expression of the field:

Before:

After:

The strange thing is that it will show nicely on the preview, just not on the printed invoice.

Happy Reporting.

Many people approach me with questions about the Inventory Batch Posting in Microsoft Dynamics NAV 2009 (or 4.0 or 5.0 for that matter). It’s not only customers, but also consultants, and there seem to be some confusion about the subject. So here’s how I usually present the matter on my courses and for my customers:

Firstly we need to address how a sale or purchase is posted in Navision. For that we need some flyers and a little explanation. To avoid confusion I omit everything else than the raw item posting (VAT, discounts etc.)

(1) When an Item is purchased one posting is made:

1. Credit Vendor
2. Debit Purchase

(2) Then One Item Ledger entry is made type Purchase. Well it’s actually the corresponding Value Entry that is basis for the calculation.

Then when the Adjust Cost batch and Post inventory cost to G/L is run:

(3) The Purchase posting is reversed through the Adjustment Account and the amount is posted on the Inventory account instead.

Likewise in with the sale:

(4) On posting the sale, the sales posting is made:

1. Credit Sale
2. Debit Customer

(5) Then One Item Ledger entry is made type Sale. Again it’s the corresponding Value Entry that is basis for the calculation.

Then when the Adjust Cost batch and Post inventory cost to G/L is run. The Adjust Cost Batch will look up the original price, which was used when the Item was put on inventory. The Post Inventory to G/L will post this:

(6) The original Cost will be removed from the Inventory and put onto the COGS (Cost Of Goods Sold) Account.

Many people see this as one operation, but it’s not. This is three operations:

• Posting of the Purchase/Sale Document
• Adjust Cost Batch
• Post Inventory to G/L

And these operations can be separated and many do, mainly because of performance issues.

In the Inventory Setup we have 2 setup fields that determine this:

The Automatic Posting to G/L will determine if the Post Inventory to G/L should be run automatically on posting items to an item account. Even if this field is checked it’s still necessary to run the Adjust Cost – Item Entries Batch. However if the Automatic Cost Adjustment is set up this can help.

The Automatic Cost Adjustment Fields has these options.

The On-line Help shows the following table:

This basically means that you can adjust the accuracy of your inventory. This means, that if you set up Days, you get the least accurate inventory because it will only adjust entries that are made one day from the Work date. And in order to get an accurate inventory you will need to run the Adjust Cost – Item Entries Batch before reconciling the inventory with the G/L.

Does this mean that if I set Automatic Cost Adjustment to always, then I will never have to run the Adjust Cost – Item Entries Batch again? No, if you use the Manufacturing module it’s necessary to run the Adjust Cost – Item Entries Batch every month. And actually I would always run it before closing the books on an audited fiscal period.

Ok - So far so good – But WHAT Posting Date will be used for these postings?

Well I just set up that it should ALWAYS Adjust my Cost and Post Inventory to G/L. This means that it will try to post the adjustment ON THE SAME DATE AS THE ORIGINAL TRANSACTION.

Hmmmmm

Imagine that we received an Item June 15^th and we closed the books on June 30^th.

Then on July 25^th we received the invoice with the correct prices causing an adjustment to be made. However we are not very interested in changing our closed fiscal period.

How do we control this?

Well the problem implicates two different issues:

1. What is the earliest Posting Date we can accept for new fiscal transactions?
2. Which periods are open for users to post transactions in general and specific for each user?

This is controlled using three different setup tools. And not all of these are the obvious choices.

The first setup is the General Ledger Setup:

The fields “Allow Posting From” and “Allow Posting To” are the fields limiting the users to the period they are allowed to post in, right? Well almost. Firstly if you have setup another period in the User Setup, this will take precedence over the General Ledger Setup.

And something else, The “Allow Posting From” and “Allow Posting To” should NEVER be blank because that will enable all users to post in all periods, also old closed periods, back to 1753. And in Native Navision even back to when Jesus was born.

Secondly the “Allow Posting From” in the General Ledger Setup has an undocumented function, at least according to the on-line help. The “Allow Posting From” is also used by the Adjust Cost to define earliest acceptable posting date for the Post Inventory Cost to G/L batch.

Thirdly there’s one more setup that will interfere with our Inventory batch posting; The Inventory Periods. Ever wandered what they were?

If you setup an Inventory Period AND CLOSE IT, it will help defining the earliest possible date.

Ok so let’s see:

Here the earliest Posting Date will be July 1^st for the Adjust cost and Post Inventory cost to G/L.

Users can in general post from July 1^st to August 31^st

Individual users can post from May 1^st to August 31^st

The Inventory period is open and will not influence anything

Here the earliest Posting Date will be August 1^st for the Adjust cost and Post Inventory cost to G/L.

Users can in general post from July 1^st to August 31^st

Individual users can post from May 1^st to August 31^st

The Inventory period is closed and will therefor define the earliest possible Posting Date.

Here the earliest Posting Date will be May 1^st for the Adjust cost and Post Inventory cost to G/L.

Users can in general post from May 1^st to August 31^st

Individual users can post from July 1^st to August 31^st

The Inventory period is open and will not influence anything

THIS will give endless problems and error messages to the users. Not to mention problems with having to open closed periods in order to run the Post Inventory to G/L.

I Hope this has shed some light on the issue.

Over the last couple of months, I’ve added my webservice examples to my own webserver, for all of you to see. For each example I’ll explain it graphically, what it takes in NAV and what it takes in Visual Studio.

The first example is VERY simple. I created it to time, just how long it took to add an extra website that utilizes webservices in NAV. Bear in mind that I had already the whole infrastructure in place: NAV server, Demo Database, the Business Webservices service and the IIS, so the project was basically this:

Employee Phone Directory

Many companies struggle with phone lists for their employees. More or less outdated Excel spreadsheets are lying around everywhere. Imagine if all Employees were maintained in one single database (NAV off-course), and everybody would just connect to a web page, that again dug into NAV showing the content of the Employee table from the Human Resource module.

Graphically it would look like this:

What would that take:

• We need to create a new page, 50000 in this case, since I only need limited amount of information, and I feel that I can control the permissions better with a new object instead of using the standard page.
• We need to publish the page as webservice.
• Lastly we need to create a Web page in Visual studio using the DataGrid control

Let’s test it:

New page 50000 - Check - 5 Minutes.

New Webservice: Page 50000 Employee – Check – 5 minutes.

Web Page in Visual studio using the DataGrid Control – Check - 2 hours.

Total 2 hours and 10 minutes.

And here it is:     http://ba-consult.dk/employees

Fun eh?

Ok let’s try something a little more advanced: My customers should be able to log on to my system, see a list of all their invoices and have the invoice printed in the web page. Sounds familiar? Well the last part about showing the invoice within the web page, you can see in one of my earlier posts. But how about the rest? Because of my natural born laziness I decided to skip the login part and just let the customers filter for their own Customer No.

Invoice List

Graphically it would look like this:

What would that take:

• We need to create a new page, Invoice List, again since I only need limited amount of information, and I feel that I can control the permissions better with a new object instead of using the standard page.
• We need a codeunit with the GetInvoice Function.
• We need to publish the page and the codeunit as webservices.
• Lastly we need to create a Web page in Visual studio using the DataGrid control.
• And we need to create the function that streams out the selected invoice to the web page.

Did this take 2 hours - No, not even close.

But it sure was fun.

Here it is: http://ba-consult.dk/GetInvoice

And another little thing: If you add this to your Home Screen on your A**le device like your iP** or your iP**** then you will see a neat little icon as well.

Throughout my courses I have always stated, that the Navigate page type is only used in page 344 Navigate. This is not entirely true. It has come to my attention, that it is being used for creating wizard pages in Microsoft Dynamics NAV 2009. E.g. the page 5077 Create Interaction is of the navigate type.

Here we also find one of the rarely used properties for actions: inFooterBar

As it shows the action is showed alongside with the OK and Cancel button in the Footer Bar.

Great one more mystery solved

Sometimes it’s fun just to experiment with making functions just to see if it is possible.

During one of my recent Microsoft Dynamics NAV 2009 development courses, I was challenged, if it was possible to create a webpage, where customers could print their invoices just by typing in the invoice number.

How can we do that?

• Well first we need a webservice in Microsoft Dynamics NAV 2009, created as a function in a Codeunit, which would take one parameter and return the invoice.
• Next we need to publish the webservice
• Lastly we need to create a website that utilizes the webservice, has one field and one button.

One of the challenges is: How do we get the invoice from Microsoft Dynamics NAV 2009?

I see two different solutions:

• Print the invoice as pdf on a public location and send the path back to the website as a string. Here the Website can present the pdf for download or view. The problem is, that our invoices will be available on the internet, and that is not an option, no matter how short it may live there.
• The second solution is to stream the invoice back to the website. This is a bit more complicated, but it will never compromise my data. Not to mention, that it is more funJ

So how do start?

In Microsoft Dynamics NAV 2009 I start by creating a new Codeunit: 50099 Test Web Services. In this I create one function PrintInvoice. This function takes one parameter inInvoiceNoFilter.

But how can we get the invoice back to the website as a stream?

Well we cheat.

I make another parameter called outFile with the type of BigText and make it “pass by reference”, in this way I can return values back to the website. And how do I get a pdf into a text? The answer is Base64. Base64 is a dll that can be registered on my Microsoft Dynamics NAV server, and gives you the possibility to convert a file into a string value. Where can I find it? Well check the internet, there are many free versions available.

So now my function looks like this:

I include a return value as Boolean, because we need that for webservices, and because we can signal if the function failed back to the web site.

Then to the function. First I create a number of variables:

After this I make the code:

After this has been made, I want to make a test from the RoleTailored Client

First I add two lines of code and a local variable (locOutFile is BigText) in the onRun trigger of the Codeunit:

Then add an action to the Role Center and run it. The result does not make any sense, but it will show me, that something has been made. It looks something like this:

Ok – So far so good. On to the webservice. First I need to publish my webservice in Microsoft Dynamics NAV 2009:

I name it WSFunc and to check if it has been published I check my browser:

http://<Machine Name>:<Port No>/<Instance Name>/WS/<Company Name>/Services

Remember that all spaces should be left as spaces; they will automatically be converted to %20. Some might suggest using localhost, but that might lead me into a log-on nightmare, meaning that I will need to log on all the time. Instead for testing, use the machine name and make sure that the machine is created as Local Intranet in the Internet Options’ security tab. In a live solution we talk much more security (SSL and all), that will be covered in a later post.

Ok – We’re done in Navision. Let’s go to visual Studio

First I make a new web site using the C#

In the Designer of the Default.aspx

Drag in a label, a Textbox and a button from the Toolbox, It will look something like this:

Now create a Web Reference to your webservice, sometimes you need to go through Add Service Reference then Advanced then Add Web reference:

Giving you this window where you can paste in the end point address you found in your browser. Add a reference name and click Add Reference. Do not use the same name as your webservice. C# can’t handle that namespaces and variables have the same name.

After this you can see the Web Reference in the solution explorer:

Now on to the code lines:

In the Designer, double click the button. That will take you to the code:

In the top include the Web Reference

Now to the code:

Press F5 to test the page, Type an known invoice No in the Text field and Click the Button. It should look like this:

After Clicking the Button:

And here you go.

Have fun.

The “Directions EMEA 2012″ conference in Rome last week was a massive event this year. There were 924 attendees from 40 countries. The central theme was Microsoft Dynamics NAV 2013 and the Cloud. I believe that the word Cloud or Azure must have been said more than a thousand times during these presentations J

First of all I wish to give a big “Thank You” to Torben, Daya and the whole organization behind The “Directions EMEA 2012″ conference. It was a fantastic event and everything ran smoothly (off course not mentioning the poor wireless internet and the fact, that they clearly underestimated the amount of coffee that Microsoft consultants drink J).

The tracks on the conference were split into 9 tracks:

• Keynotes
• A business Track
• A technical track
• A technical track for Dynamics NAV 2013
• A consultants Track
• A “hands on” track
• An ISV track
• Roundtable discussions
• And then there was the Self Pacing lab during the whole event

All sessions are available on the Directions EMEA 2013 homepage here.

Since these tracks run parallel, obviously I was only able to cover some of them, so I will go though some of them in the upcoming blogs.

As an MCT conducting the “Installation & Configuration in Microsoft Dynamics NAV 2009” and the “SQL Server Installation and Optimization for Microsoft Dynamics NAV 2009” course, I see lots of struggling  in order to get Microsoft Dynamics NAV 2009 running; Especially when it comes to running the SQL server, the NAV server and the RTC client on three different machines.

This is a guide on “How to install Dynamics NAV in different configurations”.

This topic has been covered in many forums and blogs, and here is a link to the different sites, where I have found inspiration:

• Walkthrough: Installing the Three Tiers on Three Computers
• Walkthrough: Accessing Multiple Microsoft Dynamics NAV …
• Best Practices Analyzer for Microsoft Dynamics NAV … - MSDN Blogs
• How to: Create or Load a Setup Configuration File.

So, let’s line up the different configurations:

Running all elements on ONE computer using NETWORK SERVICE as the service user id.

Running all elements on ONE computer using a domain user as the service user id.

Running  the NAV server and the SQL Server on ONE computer and the RTC client from a different computer, using NETWORK SERVICE as the service user id.

Running  the NAV server and the SQL Server on ONE computer and the RTC client from a different computer, using a domain user as the service user id.

Running  the SQL Server on ONE computer, the NAV server on another computer and the RTC client from a third computer, using a domain user as the service user id.

Running  the SQL Server on ONE computer, TWO NAV servers on another computer and the RTC client from a third computer, using a domain user as the service user id.

Running  the SQL Server on ONE computer, TWO NAV servers on TWO different computers and the RTC client from a forth computer, using a domain user as the service user id.

Running all elements on ONE computer using NETWORK SERVICE as the service user id.

This is the easy part. Run the setup program from the Microsoft Dynamics NAV 2009 DVD.

Do not install the demo version but choose the select an installation option.

Select the Developer Environment.

This will install ALL needed component for you to run Microsoft Dynamics NAV 2009.

If SQL server is not installed on the machine, the installation program will install SQL Server Express edition. This has some limitations (1 processor/ 1GB RAM and maximum 3 GB database).  If this does not fulfill your needs, make sure to install SQL server before installing Microsoft Dynamics NAV 2009 on this machine.

Only one element is missing: The Visual Studio for creating Reports for the RoleTailored Client has not been installed. Here you can download and install the Visual Studio Web Developer Edition, which is free.

Running all elements on ONE computer using a domain user as the service user id.

 This is another easy task. Follow the instructions from Running all elements on ONE computer using NETWORK SERVICE as the service user id.

After this you need to:

• Create a domain user account in the active directory and assign it to run the Microsoft Dynamics NAV 2009 server.
• Grant the user rights to the object listener
• Check/Change the firewall settings on the NAV Server
• Delegate domain\user for the NAV Server
• Change the ClientUserSettings.config on the RTC

Running  the NAV server and the SQL Server on ONE computer and the RTC client from a different computer, using NETWORK SERVICE as the service user id.

On the combined SQL server and NAV Server run the setup program from the Microsoft Dynamics NAV 2009 DVD.

Do not install the demo version but choose the select an installation option.

Here select the Database Component, but click the Customize option. The Database Component will install the SQL Server (if needed) the SQL Database and the extended stored procedures:

If the installation of the extended stored procedures failed, please check the link: Installing the Extended Stores Procedures from xp_ndo.dll on SQL Server

On the customize window select the Server to be run from my computer.

Note that if the Demo Database NAV (6-0) is already installed on the SQL Server, you will get an error, unless you specify to overwrite the existing database in the parameters window.

On the computer running the RoleTailored Client run the setup program from the Microsoft Dynamics NAV 2009 DVD as described HERE.

Running  the NAV server and the SQL Server on ONE computer and the RTC client from a different computer, using a domain user as the service user id.

Follow the instructions from Running  the NAV server and the SQL Server on ONE computer and the RTC client from a different computer, using NETWORK SERVICE as the service user id.

After this you need to:

• Create a domain user account in the active directory and assign it to run the Microsoft Dynamics NAV 2009 server.
• Grant the user rights to the object listener
• Check/Change the firewall settings on the NAV Server
• Delegate domain\user for the NAV Server
• Change the ClientUserSettings.config on the RTC

Running  the SQL Server on ONE computer, the NAV server on another computer and the RTC client from a third computer, using a domain user as the service user id.

• Install the SQL Server Option on the SQL Server
• Install the Server option on the NAV Server
• Change the instance name in the NAV server if necessary
• Install the RoleTailored Client on the client machines

After this you need to:

• Create a domain user account in the active directory and assign it to run the Microsoft Dynamics NAV 2009 server.
• Grant the user rights to the object listener
• Check/Change the firewall settings on the NAV Server
• Delegate domain\user for the NAV Server
• Change the ClientUserSettings.config on the RTC

Running  the SQL Server on ONE computer, TWO NAV servers on another computer and the RTC client from a third computer, using a domain user as the service user id.

Follow the instructions from Running  the SQL Server on ONE computer, the NAV server on another computer and the RTC client from a third computer, using a domain user as the service user id.

After this you need to:

• Change the NAV server to support TcpPortSharing
• Create a NEW NAV server instance supporting TcpPortSharing
• Change the Instance name of the NEW NAV server
• Create the corresponding SPN to the DynamicsTEST instance as described in: Delegate Domain\USER for NAV Server.

Now test if the new NAV Server instance is accessible from the Roletailored Client

Running  the SQL Server on ONE computer, TWO NAV servers on TWO different computers and the RTC client from a forth computer, using a domain user as the service user id.

Follow the instructions from Running  the SQL Server on ONE computer, the NAV server on another computer and the RTC client from a third computer, using a domain user as the service user id

After this you need to:

• Install the Server option on the NAV Server on the new machine
• Change the instance name in the NAV server if necessary
• Delegate domain\user for the NAV Server2

.

Creating and assigning a domain user for running the Microsoft Dynamics NAV server:

• Create Domain\USER in Active Directory
• Grant Domain\USER Member of Domain Users
• Grant Domain\USER Access to the Dynamics NAV Database
• Grant Domain\USER rights as ALL in Dynamics NAV
• Grant Domain\USER rights as public in SQL Server and public on SQL database
• Add Domain\USER to the Administrators group locally on the NAV Server. This is only neccesary if You intend to activate TcpPortSharing
• Grant Domain\USER full access to the Folder of the Microsoft Dynamics NAV Server service:
• If 32 bit operating system C:\Program Files\Microsoft Dynamics NAV\60\Service
• If 64 bit operating system C:\Program Files (x86)\Microsoft Dynamics NAV\60\Service
• Change NAV Server Service with log on as Domain\USER

Restart service

The following scripts can be run in the SQL Server Management Studio for creating the user:

USE [ReplaceWithYourDatabaseName]

CREATE USER [ReplaceWithNAVServerAccount] FOR LOGIN [ReplaceWithNAVServerAccount];

GO

Grant the domain user access to the Object listener:

Create a new Scheme: ndo$navlistner (If the Scheme exists already, it should be dropped first):

CREATE SCHEMA [$ndo$navlistener] AUTHORIZATION [ReplaceWithNAVServerAccount];

GO

ALTER USER [ReplaceWithNAVServerAccount] WITH DEFAULT_SCHEMA = [$ndo$navlistener];

GRANT SELECT ON [Object Tracking] TO [ReplaceWithNAVServerAccount];

GO

This could also be done through the SQL Server Management Studio

Change Firewall settings:

Open port 7046 In Firewall of the NAV Server (this is done automatically on installing the NAV server from the setup program. Remember to open port 7047 In Firewall of the NAV Server if it is running Web-services as well.

Delegate Domain\USER for NAV Server:

First set the SPN for the tcp name of the NAV server

>setspn -S DynamicsNAV/NavServer.contoso.com:7046 Domain\USER

Secondly set the SPN for the netbios name of the NAV server

>setspn -S DynamicsNAV/NavServer:7046 Domain\USER

Lastly set the SPN for the of the SQL server

>setspn -S MSSQLSvc /SQLServer.contoso.com:1433 Domain\USER

Make sure that there are NO other SPNs for the SQL server.

Using the -A parameter would also do the job, but the -S will check for duplicate SPNs as well.

A list of all delegations for this user can be seen by using the command:

>setspn -L Domain\USER

This setup applies if  the SQL Server service runs under the same Domain\USER as the NAV Server service.

If a separate user has been created to run the SQL Server service then separate delegations must be made:

Kerberos tickets that have been made live in the AD for 10 hours, therefor it might be necessary to delete the old ones.

To see all active Kerberos tickets use the command:

>klist

If any tickets exist, delete all tickets on all machines.

Deleting all active Kerberos tickets:

>klist purge

Lastly go to Active directory and add constrained delegations from the NAVService user.

The delegation tab will only be available after creating the SPN with the above commands.

The “Trust this user for delegation to any service (Kerberos Only)” can be used for testing, but should not be used in a live environment.

Change the settings in the RTC setupfile:

Add keys in ClientUserSettings.config for the RTC. Found in C:\ProgramData\Microsoft\Microsoft Dynamics NAV:

<add key=”DelegateInfo” value=”DomainUser”>

</add> <add key=”Allowntlm” value=”false”></add>

Installing the Extended Stores Procedures from xp_ndo.dll or xp_ndo_x64.dll on SQL Server

If the extended stored procedures have not been installed correctly, there are two ways to install them manually:

Firstly locate the file xp_ndo.dll on the Microsoft Dynamics NAV installation CD. If you are running  a 64 bit operating system locate the file xp_ndo_x64.dll.

Store the file in “C:\Program Files\Microsoft SQL Server\MSSQL\Binn\xp_ndo.dll” or anywhere convenient for you (then you will just need to change the path below).

From SQL Server Management Studio create a new query and paste the following

Alternatively you can install it directly in SQL Server Management Studio:

And do the same for xp_ndo_enumusersids.

Install the RoleTailored Client on the client machine.

On the computer running the RoleTailored Client, run the setup program from the Microsoft Dynamics NAV 2009 DVD.

Again do not install the demo version but choose the select an installation option.

Select the Client option

And enter the SQL server Name in the parameters window.

Install the SQL Server Option

On the SQL server run the setup program from the Microsoft Dynamics NAV 2009 DVD.

Do not install the demo version but choose the select an installation option.

Here select the Database Component. The Database Component will install the SQL Server (if needed) the SQL Database and the extended stored procedures:

If the installation of the extended stored procedures failed, please check the link: Installing the Extended Stores Procedures from xp_ndo.dll on SQL Server

Note that if the Demo Database NAV (6-0) is already installed on the SQL Server, you will get an error, unless you specify to overwrite the existing database in the parameters window.

.

Install the Server Option as an instance of the Microsoft Dynamics NAV Server, which is a part of the middle tier.

On the selected server run the setup program from the Microsoft Dynamics NAV 2009 DVD.

Do not install the demo version but choose the select an installation option.

Select the correct port, SQL server and Database. Here you cannot change the instance name. This must be done later, if necessary.

Changing the instance name of an installed Microsoft Dynamics NAV server

The only way to change the instance name of an installed Microsoft Dynamics NAV server is in the CustomSettings.config file. This will usually be placed in the service folder:

C:\Program Files (x86)\Microsoft Dynamics NAV\60\Service

Change the instance name and restart the NAV Server service

Change an existing NAV server to support TcpPortSharing

This topic is also covered in the Walkthrough: Accessing Multiple Microsoft Dynamics NAV Databases from a Single Microsoft Dynamics NAV Server Computer

In order to change an existing service the SC command is used.

The SC command is a little bit special because any parameters must be written in a special way e.g.: Start= auto Note the space after the =. THIS MUST NOT BE OMITTED. First we need to find the service name, so go to services and find the NAV Server and view properties for this:

Note the Service name.

Now go to a cmd.exe raised to administrator level and type the following command:

You must write the computername in the \\<computername>  NEVER localhost.

After hitting enter you get

Now go to the properties of the service again and go to the dependencies tab:

Here you can see that tcp port sharing is now enabled.

Restart the service.

Check that you can still access the NAV Server from the RoleTailored Client

Create an additional NAV Server on the computer of the existing NAV Server

This topic is also covered in the Walkthrough: Accessing Multiple Microsoft Dynamics NAV Databases from a Single Microsoft Dynamics NAV Server Computer

Firstly ensure that the database you want to connect to is installed on the SQL server.

Any other SQL server can be used as well as long as it has been prepared for running Microsoft Dynamics NAV.

Then go to and find the existing service folder. Usually somewhere like here:

C:\Program Files (x86)\Microsoft Dynamics NAV\60

Copy the whole service folder into a new folder called service2

Go to service2 and edit the CustomSettings.config file

In order to create a new service the SC command is used.

The SC command is a little bit special because any parameters must be written in a special way e.g.:

Start= auto

Note the space after the =. THIS MUST NOT BE OMITTED.

After you get [SC] CreateService SUCCESS The Service is installed and must be configured and started:

Set the domain\user to run the service, enter the password, press apply and start the service