Friday, 19 February 2016

Export and Split Navision objects using Windows PowerShell in NAV 2015

We are following some simple steps for export the Nav application objects:

1.  Open Windows PowerShell ISE and Load Nav Development Shell Command.
In Start Menu Just search Windows PowerShell ISE and open it.
Now you will see below screen.












Now to load Nav development shell command into Windows PowerShell ISE just run below command then you access all function and command which are available in Microsoft.Dynamics.Nav.Model.Tools.Module.

Command :- Import-Module “C:\Program Files (x86)\Microsoft Dynamics NAV\80\RoleTailored Client\Microsoft.Dynamics.Nav.Model.Tools.psd1" -force







Run its command by using Run Scipt(F5) {Tools - Run/Continue}.

Now we will see  how to Export and Split Nav Objects.

2 Export Nav Object using Windows PowerShell.

We used below cmdlet for export.
Command -  Export-NAVApplicationObject

Syntax  - Export-NAVApplicationObject FilePathwithName -DatabaseServer 'ServerName' -DatabaseName 'DatabaseName' -Filter filters -LogPath 'Log File Path'

FilePathWithName - The path and file name to which you export objects. This parameter is required.
 Exp  - D:\SP\Source\Tables.txt

ServerName :- The Name of  the database server on which you want to run the command.
Exp:- dell-pc (Localhost)

DatabaseName :- The database in which the objects that you want to export.

filters :- A filter on the Objects Table. following table shows the fields in the objects table on which you can filter,

Object table field
Values
Example
Type
Table, Codeunit, Page, Report, XMLport, Query
Type=Table|Page
ID
Integer or range of integers
ID=60000..60010
Name
Any valid object name
"Name=Purchase Header"
Modified
yes, no, 1, 0
Modified=yes
Compiled
yes, no, 1, 0
Compiled=0
Date
Date, in the numeric format determined by your regional settings
"Date=01/12/2012"
Time
Time, in the numeric format determined by your regional settings
"Time=12:00:00"
Version List
Any valid version
"Version List=NAVW17.00"
Caption
Any valid caption
"Caption=Company Information"
Locked
Yes, no, 1, 0
Locked=1
Locked By
Domain\username
"Locked By=dell-PC\sonu.panwar"

Log File Path :- The path  for the file that contain error  messages that result from the command, if  there are no  error come at run time then log file not created.
By Default, the file named naverrolog.txt  and is located in same location as finsql.exe.

Example:-  
Now run the below command for export objects form specified Database.

Export-NAVApplicationObject D:\SP\Source\test\Table.txt -DatabaseServer 'dell-pc' -DatabaseName 'ePuma-LocalDev' -Filter 'Type=Table;ID=1..10' -LogPath 'D:\SP\Source\test\' | Split-NAVApplicationObjectFile -Destination D:\SP\Dest\



After run it command show the below screen.








Now can check specified path "D:\SP\Source\test\"  in command then you found a txt file with specified name (Table.exe)








3- Split

Splits a  text file that contain two or more objects into separate text file for each objects

Syntax

Split-NAVApplicationObjectFle [-Source] <String> [[-Destiation] <String>] [-Force] [-PassThru][-PreserveFormatting]

Use that command  to split a text file that contains more then 2 Nav application Objects into  separate text file for Nav application objects. for example if you are export table 36  and table 37 to a single text file such as Table.txt., you can use the split command to create separate text file like as TAB36.txt and TAB37.txt.

---------
Now  we want to export the some filtered objects with the separate txt file, then we can use the both command with "|".
Export Comand |Split Command
Syntax
.- Export-NAVApplicationObject FilePathwithName -DatabaseServer 'ServerName' -DatabaseName 'DatabaseName' -Filter filters | Split-NAVApplicationObjectFle [[-Destiation] <String>]

It's command first export the objects in source location and after then splits and save in destination Location.

Example -  Now we want to export Tables 1 to 10 with separate txt file.

Command-
Export-NAVApplicationObject D:\SP\Source\test\Table.txt -DatabaseServer 'dell-pc' -DatabaseName 'ePuma-LocalDev' -Filter 'Type=Table;ID=1..10' -LogPath 'D:\SP\Source\test\' | Split-NAVApplicationObjectFile -Destination D:\SP\Dest\










come the below message after successfully run the command.













Now check you source path and destination path now created the Table.txt at source path and separate file are created at  destination path.












Thank You.

Wednesday, 6 May 2015

Basic SQL - Creating Extended Stored Procedure / xp_ndo.dll

Microsoft Dynamics NAV requires two extended stored procedures from xp_ndo.dll to exist on SQL Server if Windows logins are used. If these extended stored procedures do not exist, you will get this error when trying to log on using Windows Authentication:
---
The extended stored procedure xp_ndo_enumusersids in the library file xp_ndo.dll, is not available on the LOHNDORF1 server.  Until this procedure and library have been added, it will not be possible to connect to this server from Microsoft Dynamics NAV with Windows Authentication, but you will still be able to connect with Database Server Authentication.  You can read more about adding this extended stored procedure in the help pages on the product CD. Follow the hyperlink to the readme.txt file on the Servers page under the Documentation section of SQL Server.

---


When this happens you have two options: Use Database Authentication instead, or create the extended stored procedures on the SQL Server. a normal installation of a NAV client will create these procedures automatically, which is why in most cases you don’t need to do it. But if for some reason it was not created, then this is how to do it manually:
1)    From the product DVD, open the folder sql_esp, and run the file xp_ndo.exe to extract the file xp_ndo.dll somewhere you your disk. Then copy it into this folder on your SQL Server (on SQL 2008): C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\. Remember, copying files into the “Program Files”-folder requires administrative privileges on Vista and Windows2008, which is why you should extract the file to a different folder first, and then copy it into this location.

2)     Open SQL Server Management Studio. Extend Databases -> System Databases -> master -> Programmability. Right click on “Extended stored procedures”, and select “New extended stored procedure”.


3)    Name it xp_ndo_enumusersids, and in the DLL field, select the full file and path for xp_ndo.dll (on SQL Server 2008 C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\xp_ndo.dll):


4)     Assign permissions to the public role: In the Extended Stored Procedures properties, click on “Permissions”, then “Search”. Type in “Public”, and click OK. Then assign Execute permissions to Public:

5)     Finally, repeat the previous steps and make another extended stored procedure which is exactly identical, except this one must be called xp_ndo_enumusergroups instead of xp_ndo_enumusersids.
You should now have two extended stored procedures, and should be able to use Windows login from NAV.
......
Sonu Panwar
Microsoft Dynamics Consultant

Navision 2009 R2 installation on production environment


Navision installation on production environment
System Requirements for Microsoft Dynamics NAV 2009 R2
Navision RTC client works on Window login so, before installing anything a domain server is required. Navision three-tier architecture requires three systems (machine/servers)

1.             NAVSQL (Database tier)

2.             NAVSERV (Application Server (middle) tier)

3.             NAVCLIENT (User systems where client to be installed)

Note: - Above machine should be on domain.

A.   Hardware requirements

1.             NAVSQL (Database tier)

·         8 Core, 128 GB RAM, Storage - 2 TB,
·         Server should have three drive

a.       One for Window (400 GB)

b.       Second one for database (Storage - 1 TB)

c.        Third one for backup (600 GB)

2.             NAVSERV (Application Server (middle) tier)

·         8-Core, 64 GB RAM, Storage - 500 GB 

3.             NAVCLIENT (User systems where client to be installed)

·         Client Server (RTC Client) - 8-Core, 64 GB RAM, Storage – I TB space. 
4.             Domain server :-Basic configuration of active directory.

B.   Software requirements
1.      Domain server
a.       All Nav and other integration users should be created in domain  
b.       Require a User that has Administrator permission (eg. Navadmin )
2.      NAVSQL (Database tier)
a.       Microsoft Windows Server 2012 R2 (64 bit)
b.       SQL SERVER 2014
c.        SQL business intelligence development studio
d.       Microsoft .NET Framework 3.5
e.       Microsoft Core XML Services (MSXML) 6.0 Service Pack 1
f.         Microsoft Report Viewer 2008
g.       Microsoft Visual C++ 2008
h.       Microsoft Visual J# .NET 2.0
i.         Navision License

d.       Database backup file

e.       Navision Setup (Dynamics.NAV60R2.AUNZ.1097437.DVD) file

f.         Build 33639 file.

g.       Install Classic Client of Navision (See 4B)

2A  Installing the Microsoft Dynamics NAV Database Components

Run Microsoft Dynamics NAV 2009 R2 Setup and select the Database Components option to configure SQL Server to work with Microsoft Dynamics NAV 2009. This option also installs the Microsoft Dynamics NAV demo database, which contains the CRONUS International Ltd. company.
To install the Microsoft Dynamics NAV database components and the demo database
1.       Insert the Microsoft Dynamics NAV 2009 DVD in the drive of NAVSQL, which is the server where SQL          Server is already installed.
2.       On the startup page, under Install, click Microsoft Dynamics NAV.
3.       On the Welcome page, click Next.
4.       To accept the license terms, click I accept.
5.       On the Microsoft Dynamics NAV 2009 Installer page, click Choose an installation option.
6.       On the Choose an installation option page, click Database Components.
      The demo database is included as part of this option.
7.       On the Specify parameters page, click Install.
8.       After the installation is complete, click Close to exit Setup.

2B  Restore Database Backup on SQL server

1.       Restore Database Backup on SQL server
2.       Database Name should be same as current production server
3.       Create all NAV and other integration user in SQL
4.       Give all User SQL Permission same as current production server

2C  Creating Extended Stored Procedure / xp_ndo.dll


3.      Application Server

a.       Navision Setup (Dynamics.NAV60R2.AUNZ.1097437.DVD) file

b.       Build 33639 file.

c.        Microsoft .NET Framework 3.5

d.       Microsoft Core XML Services (MSXML) 6.0 Service Pack 1

e.       Microsoft Report Viewer 2008

f.         Microsoft Visual C++ 2008

g.       Microsoft Visual J# .NET 2.0

h.       Navision License

i.         Barcode Code Setup (barcode128)

j.         Print To pdf (if you required)

k.       Microsoft Office.

l.         Default Printer (For Pick Report Printing)

3A.            Installing Microsoft Dynamics NAV Server

The next step is to install Microsoft Dynamics NAV Server on NAVSERV, which is the second server computer. This is a different server computer from the one where you installed SQL Server and the Microsoft Dynamics NAV database components.
To install Microsoft Dynamics NAV Server
1.       Insert the Microsoft Dynamics NAV DVD into the drive of NAVSERV.
2.       On the startup page, under Install, click Microsoft Dynamics NAV.
3.       On the Welcome page, click Next.
4.       To accept the license terms, click I accept.
5.       On the Microsoft Dynamics NAV 2009 Installer page, click Choose an installation option.
6.       On the Choose an installation option page, click Server.
7.       On the Specify parameters page, click Server to open the Installation Parameters pane.
8.       In the SQL Server field, type NAVSQL, which is the name of the computer running SQL Server.
9.       In the SQL Database field, type Demo Database NAV (6-0).
10.    This is the Microsoft Dynamics NAV demo database, which contains the CRONUS International,      Ltd., demonstration company.
11.    Click Apply to save the Microsoft Dynamics NAV Server settings.
12.    Click Install to start installing software.
13.    After the installation is complete, click Close to exit Setup.


3B.            Configuring for a Domain User Account

The procedures in this section are necessary only when the logon for Microsoft Dynamics NAV Server is a domain user account instead of the Network Service account. You can only perform these actions if you have domain administrator privileges.
The steps that are involved in creating a domain user account by using the Active Directory Users and Computers utility (dsa.msc) are part of the Active Directory documentation, which is included in the Windows Server documentation.
Raising the Domain Functional Level
After you create the domain user account, you must verify that the Domain Functional Level or your domain is at least Windows Server 2003 level.To determine if the functional level of your domain is at least Windows Server 2003 level and to raise it if it is not, follow these steps:
1.    Choose Run from the Start menu in Windows, type dsa.msc, and then press ENTER.
This opens the Active Directory Users and Computers utility. This utility is part of Windows Server 2003 or Windows Server 2008.
2.    Right-click the domain where Microsoft Dynamics NAV is installed, and then click Raise Domain Functional Level.
If the level is at least Windows Server 2003, then you can close the utility. Otherwise, continue to the final step.
3.    Under Select an available domain functional level, click Windows Server 2003, and then click Raise.

3C.            Change the Logon Account for the Microsoft Dynamics NAV Server Service and the SQL Server Service
Change the logon account for the Microsoft Dynamics NAV Server service and the SQL Server service to use your domain user account. For information about how to configure Windows Services, see How to: Configure Windows Services.

3D.          Enabling the Object Change Listener
The Object Change Listener (OCL) component of Microsoft Dynamics NAV Server monitors the database for changes that are made to application objects, such as adding a new field to a page. If OCL cannot start because of permissions errors, then you cannot connect clients to the server. When you try to start the RoleTailored client, you see the following message:
Cannot connect the Change Listener to SQL Server.
For more information, see Enabling the Object Change Listener.

To enable and assign minimum permissions for the Object Change Listener

1.    Open SQL Server Management Studio, and then connect to your SQL Server instance.
2.    On the File menu, point to New, and then click Query with Current Connection.
3.    Type the following SQL statements.
4.   USE MASTER
5.   CREATE LOGIN [ReplaceWithNAVServerAccount] FROM WINDOWS;
6.   GO
7.    Highlight the lines that you typed, and then on the Query menu, click Execute.
8.    Type these lines after the existing lines.
9.   USE [ReplaceWithYourDatabaseName]
10.  CREATE USER [ReplaceWithNAVServerAccount] FOR LOGIN [ReplaceWithNAVServerAccount];
11. Highlight the lines that you just typed, and then on the Query menu, click Execute.
12. Type these lines after the existing lines.
13.  CREATE SCHEMA [$ndo$navlistener] AUTHORIZATION [ReplaceWithNAVServerAccount];
14.  GO
15. Highlight the lines that you just typed, and then on the Query menu, click Execute.
You may see an error stating that the schema in question already exists. You can ignore this error.
16. Type these lines after the existing lines.
17.  ALTER USER [ReplaceWithNAVServerAccount] WITH DEFAULT_SCHEMA = [$ndo$navlistener];
18.  GRANT SELECT ON [Object Tracking] TO [ReplaceWithNAVServerAccount];
19.  GO
20. Highlight the lines that you just typed, and then on the Query menu, click Execute.
  1. Save your query to keep a record of these actions.
You can use these commands again when you create a new database or change the account that you use to run Microsoft Dynamics NAV Server.

3E.             Giving the Domain User Account Permissions for the Server Folder

The next step is to give the domain user account full permissions for the Microsoft Dynamics NAV Server folder on the computer where you installed Microsoft Dynamics NAV Server.
To grant the domain user account permissions on the Microsoft Dynamics NAV Server folder
  1. In Windows Explorer, navigate to the Microsoft Dynamics NAV Server folder on the computer where you have installed Microsoft Dynamics NAV Server. On Windows Server 2003, the default location is:
Documents and Settings\All Users\Application Data\Microsoft\Microsoft Dynamics NAV\60
On Windows Server 2008 or Windows Vista, the default location is:
ProgramData\Microsoft\Microsoft Dynamics NAV\60\
  1. Right-click the Service folder, and then click Properties to open the Service Properties dialog box.
  2. Click the Security tab.
  3. Select the domain user account from the list in the top half of the dialog box, and then, in the Permissions for section in the bottom half, select Allow next to the Full Control permission.
This grants the domain user account full control of the folder.
  1. Select the NETWORK SERVICE account in the top half, and then clear the Allow field next to the Full control permission in the bottom half.
This revokes permissions on the folder for the Network Service account. No account other than your domain user account should have access to the server folder.
  1. Click OK to close the Service Properties dialog box.

3F.                        Setting Up Delegation

When the RoleTailored client, Microsoft Dynamics NAV Server, and SQL Server are installed on separate computers, the client interacts with the database through an intermediate computer, which is running Microsoft Dynamics NAV Server. The server is performing actions on the client's behalf. This process is known as impersonation.
Delegation is when a front-end service forwards the client’s request to a back-end service so that the back-end service can also impersonate the client. Complete the procedures in this section to set up delegation on your Microsoft Dynamics NAV installation. For more information on delegation, see How To: Set Up Delegation.
Create Service Principal Names
The first step in setting up delegation is to create service principal names (SPN). To make delegation more secure, Active Directory uses Kerberos to authenticate services. An SPN is the name by which a client uniquely identifies an instance of a service, using the account under which the service runs. You must create one SPN for the Microsoft Dynamics NAV Server service and one SPN for the SQL Server service to make delegation work.
To create service principal names
  1. Open an elevated command prompt. To do this, click Start, and then in the search window, type Command Prompt. Right-click Command Prompt, and then click Run as administrator.
  2. At the command prompt, create an SPN for the Microsoft Dynamics NAV Server service. The syntax is:
3.  setspn -A InstanceName/FullyQualifiedDomainNameOfServer:Port Domain\User
Using NAVSERV, which is the computer running Microsoft Dynamics NAV Server, and DynamicsNAV, which is the default instance name for Microsoft Dynamics NAV Server, the actual command has the following format:
setspn -A DynamicsNAV/NAVSERV.yourDomain.yourCompany.com:7046 yourDomain\yourUser
Replace "yourDomain," "yourCompany," and "yourUser" with the appropriate values.
  1. Create an SPN for the SQL Server service. This service runs on the NAVSQL computer with a default instance name of MSSQLSvc. Type the following command:
5.  setspn -A MSSQLSvc/NAVSQL.yourDomain.yourCompany.com:1433 yourDomain\yourUser
Again, replace "yourDomain," "yourCompany," and "yourUser" with the appropriate values.
Delegating Access to the SQL Server Service
Configuring delegation means explicitly configuring the Microsoft Dynamics NAV Server service on NAVSERV to delegate its access to the database server on behalf of the RoleTailored client. To make the access more secure, you specify delegation to a specific service on a specific server. In this walkthrough, you specify delegation on the SQL Server database service (MSSQLSERVER). This is known as constrained delegation.
You must run the following procedure on a computer where the Active Directory Users and Computers utility (dsa.msc) is available.
To delegate access to the SQL Server service
  1. Click Start, and then click Run.
  2. In the Open field, type dsa.msc.
This opens the Active Directory Users and Computers utility.
  1. Right-click the node for the domain where you have installed Microsoft Dynamics NAV, and then click Find.
  2. In the Find Users, Contacts, and Group dialog box, type the name of the domain user in the Name field, and then press ENTER.
  3. In the Search results area, right-click the domain user, and then click Properties.
  4. On the Delegation tab, click Trust this user for delegation to specified services only, and then click Use Kerberos only.
There is also the option to not restrict authorization to Kerberos, although the environment is not as secure when you are less restrictive. Your decision must be reflected in the value that you assign to the AllowNtlm setting in the RoleTailored client configuration file (ClientUserSettings.config). For details, see Configuring the RoleTailored Client.
  1. Click Add to open the Add Services dialog box.
  2. Click Users or Computers, and then specify the domain user.
  3. In the list of services for the domain user, click MSSQLSvc, which is the SQL Server service.
  4. Click OK to close the Add Services dialog box. Continue clicking OK to close all open dialog boxes.
Delegation from the domain user to the SQL Server service on a separate computer is now enabled.

3G. Install Classic Client of Navision (See 4B)
3H. Install RTC Client of Navision (See 4A)


4.      Client (Role Tailored Client & Classic Client)

a.    Microsoft Windows Server 2012 R2 (64 bit)

b.    Navision Setup (Dynamics.NAV60R2.AUNZ.1097437.DVD) file

c.     Build 33639 file.

d.    Microsoft .NET Framework 3.5

e.    Microsoft Core XML Services (MSXML) 6.0 Service Pack 1

f.      Microsoft Report Viewer 2008

g.    Microsoft Visual C++ 2008

h.    Microsoft Visual J# .NET 2.0

i.      Barcode Code Setup (barcode128)

j.      Microsoft Office 

k.    Print To pdf (if you required)

l.      RDP (Terminal Licenses)

4A.             Installing the RoleTailored Client

The third and final tier is the client tier. The first task is to install the RoleTailored client to a workstation computer.
To install the RoleTailored client
1.    Insert the Microsoft Dynamics NAV DVD into the drive of NAVCLIENT, which is your Microsoft Dynamics NAV client computer.
2.    On the startup page, under Install, click Microsoft Dynamics NAV.
3.    On the Welcome page, click Next.
4.    To accept the license terms, click I accept.
5.    On the Microsoft Dynamics NAV 2009 Installer page, click Choose an installation option.
6.    On the Choose an installation option page, click Client to install the RoleTailored client.
7.    On the Specify parameters page, click RoleTailored client to configure the component.
8.    In the Installation Parameters dialog box, type NAVSERV, which is the name of the computer running Microsoft Dynamics NAV Server, in the Server Name field.
You should fully qualify the domain name in this field (in the form YourServer.YourDomain.YourCompany.com).
  1. Click Apply, and then click Apply on the Specify parameters page to start installing software.
  2. After installation is complete, click Close to exit.

4B.             Installing the Classic Client

To install the Classic client

1.    Insert the Microsoft Dynamics NAV 2009 DVD into the drive of your computer.
     The Startup pane presents a set of options in sections entitled Prepare, Install, and Join the Community.
2.    Under Install, click Microsoft Dynamics NAV.
     If you have already installed one or more Microsoft Dynamics NAV components on the computer, you will see the Maintenance pane instead of the Welcome pane. In this case, click Add or remove components and go to step 7.
3.    Click Next to move past the Welcome pane.
4.    To indicate acceptance of the license terms, click I accept.
5.    On the Microsoft Dynamics NAV 2009 Installer pane, click Choose an installation option.
6.    On the Choose an Installation Option pane, click Custom Components.
7.    On the Customize the Installation pane, click Classic client to display a menu of three options; from this menu, select Run from My Computer.
8.    Click Next to proceed to the Specify Parameters pane, where you see Classic client listed.
9.    Click Install.
10. When Setup is finished, click Close.

5.      NAS (Classic Application Server).

Application Server is a component of the Classic Architecture in Microsoft Dynamics NAV 2009.
While you run Microsoft Dynamics NAV 2009 Setup, you can provide configuration information for Application Server.
Configuring Application Server in Setup
You can configure Application Server while running Microsoft Dynamics NAV 2009 Setup. You must first select the Application Server as a component to install. To do this, select the installation option that includes the NAV Server: the Classic Server Components Option. For more information, see Installation Options.
You can also customize your installation and customize the list of components to install. For more information, see How to: Choose Components to Install.
After you choose the Classic Server Components option, or customize your component list to include Application Server, the Specify parameters pane is displayed in Setup. ClickApplication Server (the underscore indicates that this component is configurable) to open an Installation Parameters pane.
Settings
Parameter
Description
Service name
Specifies the service name for the Classic Database Server that Application Server connects to.
Service name SQL
Specifies the service name for the SQL Server database server that Application Server connects to.
The list of parameters that you see in the Specify parameters pane varies, depending on which components you have selected for configuration. The following parameters pertain to Application Server.
Click Apply when you have finished entering values to return to the Specify parameters pane in Setup.
Only one of these parameters is meaningful, depending on which type of database server Application Server connects to. Use Application Server Manager (described below) to specify whether Application Server connects to a Classic database server or an SQL Server database server.

Configuring Application Server after Installing

After you install Application Server, use Application Server Manager to modify its properties. With Application Server Manager you can modify the two properties that you configured with Setup, or any additional properties.
Configuration information about Application Server is available in w1w1atas.pdf ("Installation & System Management: Application Server for Microsoft Dynamics NAV"). To obtain this manual, go to Microsoft Dynamics NAV 5.0 Developer and Installation Guides page in the Microsoft Download Center and download w1w1atas.pdf.