November, 2008

  • Microsoft Dynamics NAV Team Blog

    Basic SQL - Installing SQL Server 2005 / 2008

    • 0 Comments

    This post is part of "Overview of NAV-specific SQL features for application consultants".

    It describes the simplest way to get SQL Server installed in a test environment. It only mentions the changes to the default installation options that you need to make to get SQL Server running for NAV. Needless to say that for installing SQL Server in a live environment, there are further considerations to make like security, performance, etc, which are not described here.

    Which edition:

    If you have a choice of editions, then for a test system choose Developer Edition unless you have specific reasons to chose something else.

    Installation options:

    The following options are for installing SQL Server 2008, but the same options exist when installing SQL Server 2005, even though the menus may look slightly different.


    The minimum features that you need to install are:
      “Database Engine Services”
      “Management Tools”
    But I would also recommend installing “SQL Server Books Online”:

    InstallOptions

    Another decision you have to make, is which account the SQL Server Services run under. When prompted for this, for a test system, set all SQL Server Services to use the “NT Authority\SYSTEM” account (just specify the account, and leave password blank). When installing SQL Server 2008 it looks like this – for SQL Server 2005 it looks slightly different:

    SelectAccounts

    The final place where you should deviate from the default installation options, is to set Authentication Mode to “Mixed Mode”. As default, SQL Server will only use Windows Authentication. Setting it to “Mixed Mode” also gives you a database login, which can be useful in some cases:

    MixedMode


    Remember the password you specify here! This password is for the sa account, so you will always be able to log in to SQL Server as System Administrator with user ID "sa" and the password you decide here.

    Use default options for the rest of the installation.

    Once you have installed SQL Server, then you will find “SQL Server Management Studio” in the start menu, which is where you can do all the admin tasks that you need.

     

     

    Lars Lohndorf-Larsen (Lohndorf )

    Microsoft Dynamics UK

  • Microsoft Dynamics NAV Team Blog

    Basic SQL - Overview of NAV specific SQL features for application consultants

    • 0 Comments

    As the title says, this post is not really for NAV developers, but for application consultants who don't use SQL Server every day but still need to know enough about SQL Server to at least get a Microsoft Dynamics NAV database to run on it.

    The challenge is, that even a basic SQL course or book will cover material which is 85% unrelated to NAV, and cover SQL features which you may never use when you only need to run NAV on a SQL server in a test environment. So the point of this post is to explain the basic steps needed to get NAV up and running on SQL Server (2005 / 2008) in the simplest and shortest way possible.

     

    I will assume that the instructions here will only be used to get NAV to run in a test environment, and not cover any features like security and performance which should be considered for a live system. Also, I will only explain the minimum steps needed to get NAV to run - not any background of why this or that needs to be done.

    This post contains a very general overview, and then links to a number of seperate posts for each individual area. So you can use this for an overview / table of content, and then link to the specific topic that you need.

    These are the areas planned for now - more may follow:

    1. Instaling SQL Server
    2. Create stored procedure from xp_ndo.dll for Windows authentication
    3. Enable trace flag 4616
    4. Create a NAV database
    5. Attach a NAV database
    6. Restore a SQL backup of a NAV database
    7. Creating users on SQL Server for use in NAV

    All the posts are tagged "Basic SQL", so you can list them from here.

    1.  Installing SQL Server

    When installing a NAV client you have the option to install the SQL Server engine as well, and the installation will automatically attach a NAV database ready to use. As long as this works for you you don't have to install SQL Server as well. But if something needs changing or troubleshooting, or if you need to attach a different SQL database, then you need the admin tool (SQL Server Management Studio), which is when you need to install SQL Server.

    The installation is fairly straigthforward. You should just change two of the default options:

    •   Set all SQL Server services to use the System account, and
    •   change the authentication mode from "Windows Authentication" to "Mixed Mode"

    For further details, go to this post:

      Installing SQL Server

     

    2.  Create stored procedure from xp_ndo.dll for Windows authentication

    NAV requires two extended stored procedures called xp_ndo_enumusergroups and xp_ndo_enumusersids, both based on xp_ndo.dll (can be found in the sql_esp folder on the product DVD). If these extended stored procedures are not present, and you try to log in with Windows Authentication, you will get this error message:

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

    For details on how to create these two extended stored procedures, follow this post:

    Creating extended stored procedures in xp_ndo.dll

     

    3.  Enable trace flag 4616

    Since NAV version 4 Sp3 the NAV client requires this trace flag to be enabled on SQL Server. If it's not, then you will get this error when trying to connect to a SQL database from NAV:

    ====
    The trace flag 4616 is not set on the server (local). You must set this flag and restart the server before you can connect using Microsoft Dynamics NAV.

    ====

    Enable the trace flag by adding ;-t4616 to the end of startup parameters, and then restarting the SQL Server service. For furhter details how to do this, follow this post:

    Enabling trace flag 4616

     

    4.  How to create a NAV database

    Creating a new database on SQL Server is almost identical to creating a new database on native: File -> Database -> New, then enter name etc. And, from there restore a NAV backup (.fbk file). More details - if needed - here:

    Creating a NAV database from a NAV backup (.fbk file)

     

    5.  Attach a SQL database

    Attaching a SQL database is done entirely from the SQL Server environment (SQL Server Management Studio). It's not often you would need to do this. But for example if you want to attach the demo database from the product DVD, then this is how to do that:

    Attaching a SQL database

     

    6.  Restore a SQL backup of a NAV database

    With NAV on SQL Server you can either use the native backup or SQL Server backups. If a backup was done with SQL Server, you also need SQL Server to restore it. SQL Server backups are generally quicker than a NAV backup, but also often larger. Your SQL Server backup strtegy can be as complex as you like, but restoring a singe, complete backup is quite simple:

    Restoring a SQL backup of a NAV database

     

    7.  Creating users on SQL Server for use in NAV

    When setting up a user for NAV, this user first has to exist on SQL Server. So you have to create the user under Security -> Logins in SQL Server Management Studio before creating them in NAV:

    Creating users on SQL Server for use in NAV

     

     

     

    Lars Lohndorf-Larsen (Lohndorf)

    Microsoft Dynamics UK


    Microsoft Customer Service and Support (CSS) EMEA

  • Microsoft Dynamics NAV Team Blog

    Tip 2: Fixing language issues when trying to setup the Outlook Synchronization in Dynamics NAV 5.0, Dynamics NAV 5.0 SP1 and Dynamics NAV 2009

    • 0 Comments

    If you are working in a localized release of Dynamics NAV with the redesigned Outlook Synchronization, I bet that you were dealing with a
    language issue. Perhaps you forgot to set the Regional Options for the NAS user account, perhaps you did not follow the Localization Tips
    of the Installation & Setup Whitepaper. Who does not know about the famous page 55 of that guide, please get familiar with this guide
    and download your copy of it today:
    Installation & Setup Whitepaper

    When you are running Dynamics NAV 5.0 SP1 or Dynamics NAV 2009 that will be released later this year, then this guide also applies as well.
    Everything that is written in this guide, applies to later releases of Dynamics NAV. Only NST (Navision Service Tier) and RTC (Role Tailored
    Client) is new and is not part of that Installation & Setup Whitepaper.

    If you start up a localized release of Dynamics NAV, it might well be that the language has been set to ENU. If you do not pay attention to
    that and click on Outlook Synch User Setup or Outlook Synch. Entities, then it might well be that the entities are configured for ENU
    language. Later on, after getting errors in the Outlook Addin, you would think about it and you change it. At that point, you will have
    mixed entities and the Outlook Synchronization will never occur without errors.

    Let me show you a screenshot of a small issue that can cause much troubles though the actual ToDo's are synchronized to Outlook:

    image 

    In another blog I already discussed how to fix this issue. Here is an extract of that blog:

    <snip>

    As per this writing, this bug also exists in all local releases of Dynamics NAV 2009 CTP4 release:
    - select Administration / Application Setup / Microsoft Office Outlook Integration / Outlook Synch. Entities
    - press F5 and select APP and press OK
    - under Outlook Collection, we see Recipients and a local value for Links (in Dutch release it is called Koppelingen)
    - select the local value for Links and press F6
    - manually select Links and press OK
    - press F5 and select TASK and press OK
    - under Outlook Collection, we see Recipients and a local value for Links (in Dutch release it is called Koppelingen)
    - select the local value for Links and press F6
    - manually select Links and press OK
    6. now enable the Marco Mels user with the 5 demo entities via Administration / Application Setup / Microsoft Office Outlook
    Integration / Outlook Synch. User Setup

    <snip>

    Now, let's assume you have started the wrong way and the you configured the Outlook Synch. User Setup with entities for a specific user
    "while the language of the CSIDE Client was set to English".

    image
    In ENU language this is Microsoft Dynamics NAV - Meetings, but in localized release, Meetings is translated to local value!

    There is an easy way to fix this, though it is a bit of work. You will have to clear everything out of the Outlook Synch. User Setup and you
    will have to reset all entities to default. E.g.:
     

    image

    After changing language and after clearing everything from the Outlook Synch. User Setup, the description will change to a local description.
    This will have to be done this in order of CONT_SP, CONT_COMP, CONT_PERS, TASK and finally APP. Of course, in the next screen you must
    select the correct choice:"

    image 

    After this has been done, you need to add the user and configure the required entities, the number of Elements, the conditions, etc. At the
    end, register the changes in the Change Log via:

    image

    Now the Outlook Synchronization has been correctly setup for any localized release of the CSIDE Client.

     

    --  mmels

  • Microsoft Dynamics NAV Team Blog

    Tip 1: The related item for the synchronized Outlook item is not found in the specified folders. The following values were expected

    • 0 Comments

    When working with the Outlook Synchronization using Dynamics NAV 5.0, Dynamics NAV 5.0 SP1 and Dynamics NAV 2009 (present release: CTP4)
    in an Exchange Server environment, the following very annoying error may show up in the Outlook Addin.

    The related item for the synchronized Outlook item is not found in the specified folders. The following values were expected: /o=CRONUS/
    ou=First Administrative Group/cn=Recipients/cn=marcomels

    The Installation and Setup guide that was compiled for the Partnersource Update gives information about this error on page 41. Here is an extract of that paper:

    <snip>

    When working in Microsoft Exchange environment make sure that the e-mail for the salesperson is defined in Outlook, and not in Microsoft Dynamics NAV. In this
    case, the e-mail will be converted into Exchange format, for example c=us;a= ;p=Cronus;o=Exchange;s=Hill;g=Annette;i=ah.

    · When a contact being synchronized is registered in Microsoft Exchange environment, make sure that the e-mail for this contact is defined in Outlook, and
    not in Microsoft Dynamics NAV. In this case, the e-mail will be converted into Exchange format and the synchronization will work properly.

    · The name and e-mail of the to-do organizer must be filled in and be identical to the name and e-mail of the current Outlook user in order for the to-do
    to be synchronized correctly
    .

    · Choose related contacts and recipients for appointments or tasks exclusively from the folders defined in the synchronization setup

    When creating an appointment or task, choose related contacts and recipients from the folders defined in the synchronization setup. If contacts
    and recipients were selected from other folders, these appointments and tasks will not be synchronized.

    <snip>
    This issue can be easily reproduced in the following scenario:
    1. Windows 2003 or later hosting Active Directory and Exchange 200x Server or later hosting a mailbox enabled user called "Marco Mels"
    2. Windows 2003 or later hosting SQL 2005 or later
    3. Windows XP or Windows Vista hosting Outlook 2003 or later and hosting Dynamics NAV client 5.0 + Outlook Addin 25874 and later releases of
    Dynamics NAV and hosting the Dynamics NAV 5.0 NAS
    To exclude possible permissions error we may run against to, we assign the following roles / permissions to the Marco Mels user:
    1. AD: Domain Admin / Administrator Group
    2. Client: Local Administrator / SUPER user role in Dynamics NAV
    Steps to be taken in Dynamics NAV to finish the scenario:
    1. Synch permissions to SQL
    2. follow localization steps that are written on page 50 of the Installation and Setup guide (alligning system and license files)
    3. enable Change Log Activated via Administration / Application Setup / General / Change Log Setup
    4. if using a localized release of NAV, make sure the language matches the system text files and license files via Tools (menu) / Language, select localized language
    and press OK
    5. fix a small bug that exists in all releases of Dynamics NAV including the 25874 build and the Dynamics NAV 5.0 SP1 build.
    As per this writing, this bug also exists in all local releases of Dynamics NAV 2009 CTP4 release:
    - select Administration / Application Setup / Microsoft Office Outlook Integration / Outlook Synch. Entities
    - press F5 and select APP and press OK
    - under Outlook Collection, we see Recipients and a local value for Links (in Dutch release it is called Koppelingen)
    - select the local value for Links and press F6
    - manually select Links and press OK
    - press F5 and select TASK and press OK
    - under Outlook Collection, we see Recipients and a local value for Links (in Dutch release it is called Koppelingen)
    - select the local value for Links and press F6
    - manually select Links and press OK
    6. now enable the Marco Mels user with the 5 demo entities via Administration / Application Setup / Microsoft Office Outlook Integration / Outlook
    Synch. User Setup

    Outlook Synch User SetupJPG

    Setting conditions is not mandatory but should be done to prevent mass data transfer to a local mailbox or a Public Folder:

    NOTE: if large data is being send to either a mailbox or a public folder in an Exchange environment, an error can occur referring you to the log file which is
    or is not enabled by the adminisrator. It can well be that Exchange is being triggered by a limitation. To workaround that issue, please do see the following
    KB article:
    830829 Your Exchange Server 2003 computer may stop responding after a MAPI client opens more than the default value of certain server objects
    http://support.microsoft.com/default.aspx?scid=kb;EN-US;830829
    7. start up Outlook, create the Outlook profile, create 5 new folders dedicated for the 5 entites and configure the Outlook Addin
    8. perform a Full Synchronization

    Result:
    A new contact item is created for the salesperson
    9. start up Dynamics NAV client and create a simple ToDo of type Meeting and a simple ToDo of type Phone Call for salesperson Marco Mels
    10. using Outlook, perform a synchronization
    Result:
    A new task is being created and a new meeting is being created
    An error occured in the Outlook Addin Troubleshooting window:

    The related item for the synchronized Outlook item is not found in the specified folders. The following values were expected: /o=CRONUS/ou=First
    Administrative Group/cn=Recipients/cn=marcomels

    The reason for this error is that in table 13 Salesperson/Purchaser, the field E-mail 2 is fileld with marcomels@cronus.com. The Outlook Addin however
    expected the following value: /o=CRONUS/ou=First Administrative Group/cn=Recipients/cn=marcomels

    To work around this issue, please do the following:
    From Outlook, open the contact entry and update the E-mail address with the correct values: marcomels@cronus.com. Synchronize back to Dynamics NAV.
    After you have configured the other entities and after you have done the same tests again with the two ToDo's, you sometimes see that the error still occurs.
    The reason for this is that you did not actually select the correct mailbox user out of the Exchange Global Address list.

    To correctly update the E-mail address in Outlook so that E-mail 2 can be filled with the correct values, you will have to do the following. After you have
    synchronized the salesperson to Outlook, open the contact entry in Outlook. Press the E-mail... button next to the E-mail address field and select the correct
    user from the Exchange Global Address List. Save the contact entry and perform a synchronization. Table 13 will now contain two different entries for the
    E-mail and E-mail 2 fields:

    table13

    Now, if you synchronize the two ToDo's, the results will be that the Task and Meetings are created without any further error messages.

    -- mmels

Page 2 of 2 (19 items) 12