Microsoft Dynamics NAV

Team Blog

August, 2009

  • Microsoft Dynamics NAV Team Blog

    What to do when the setup.exe doesn’t start in Dynamics NAV 2009

    • 8 Comments

    The reason that setup.exe doesn't start can be many. But I will try to give you ha checklist that you can work through if you end up in this situation.

    1. Ensure that the user account running setup.exe have administrator rights.
    2. Try the DVD/download one another computer. If this work you have eliminated that it's any wrong on the installation media.
    3. Start the task manager and check if any setup.exe is running. Sometimes under very special circumstances the setup.exe crash and will prevent any other attempt to start it again by staying running. If you find any setup.exe process kill them. And try to run the setup.exe again.
    4. Start the task manager and check if any msiexec.exe is running. This may happen when a windows update is running or that another installation process is running or have crashed. The best solution in this case is to restart the machine and check again and try to run the setup.exe again. In most cases the msiexec.exe process will be gone.
    5. If msiexe.exe are still there it probably mean that windows installer are running something. You can on your own risk try to kill all of those processes. The risk you take here is that you may end up with something half installed from windows update or other source. So it's recommended to run windows update after this to ensure its ok.
  • Microsoft Dynamics NAV Team Blog

    Building a Matrix Report in Microsoft Dynamics NAV 2009 SP1

    • 6 Comments

    The final Service Pack 1 for Dynamics NAV 2009 is now just around the corner, so with that I would like to share with you one of the new possibilities in the reporting area, Matrix reports.
    First I will go through how to build a Matrix Report in NAV 2009 SP1, then I will demo some Matrix reports done by two of our MVPs of Dynamics NAV and one of our partners. In the end of this blog I will share all the Matrix reports. So if you are not interested in how to build a Matrix Report in NAV 2009 SP1 or already know how to do this in NAV 2009 SP1, this is the time for you to scroll to the bottom of this blog, if you just want to download the objects. :-)

    If you are still here and have not scrolled to the bottom, let us get started building a Matrix Report.

    Building Matrix Report in NAV 2009 SP1

    In this walkthrough we will be creating a Matrix Report sorted with Items by Location.
    When you have finished this Walkthrough you will understand how to build a Matrix Report in NAV 2009 SP1.

    1. Select “New” in Object Designer with Report object selected

    2. Add “Location” as Table

    image

    3. Select “Create a blank report“ and click “OK”

    4. Insert “Location” as the first DataItem

    image

    5. Insert “Item” as the second DataItem, make sure to indent under the “Location” DataItem.

    image

    6. Save Report and give an “ID” and a “Name”

    image

    7. Now we have created the DataItems for this report, so now we need to create the Data Source for us to be able to create the Layout of the Report in Visual Studio. For this we need to go to the Section Designer. Open Section Designer

    image

    8. For now the Section Designer is Empty, so we need to add some Fields. We would need these fields:

    · Items by Location – Title

    · Code – Location

    · Name – Location

    · No. – Item

    · Description – Item

    · Inventory – Item

    9. Make more space to have more fields in each of the Sections.

    10. Add a Label with Caption=Items by Location

    image

    11. With “Location” Section selected, open Field Menu:

    12. Open Field menu and select “Code” and “Name”

    image

    13. Add these 2 fields to the “Location, Body”

    image

    14. Navigate to the “Item , Body” and select “Field Menu” and select "No.", "Description" and "Inventory"

    15. Add these 3 fields to the “Item, Body”

    image

    16. We have now created the Data Source for the report. Now we need to create the layout for this report.

    Select “View / Layout”. Visual Studio will open.

    17. In Visual Studio you will now see these elements in Data Source we can work with:

    image

    18. Lets us now design the layout.

    19. In the toolbox select the Matrix control and drag this to the Report Body

    image

    20. Drag “Items_by_LocationCaption” to the top left cell in the Matrix control

    image

    21. In the “Rows” just below I want to add 2 rows directly under “Items by Location”, so we need to add a new group. Right click the “Rows” cell and select “Insert Group”

    image 

    22. “Grouping and Sorting properties” dialog box will come up.

    Select to group on “=Fields!Item_Description.Value” and click “OK”

    image

    23. In the new cell to the left of “=Fields!Item_Description.Value”, right click and select “Edit Group”

    24. Select to group on “=Fields!Item__No__.Value” and select “OK”

    Your report should now look like this:

    image

    25. It is now time to add the column above the data, and in this report we want to see in how many items are stored in each warehouse. Add “=Fields!Location_Name.Value”

    image

    26. And at last let us add the data cell in the bottom right corner of the matrix control. Add “=Sum(Fields!Item_Inventory.Value)” to the Data cell.

    image

    27. Now before viewing the report let us already do some resizing of the layout, for this to readable on one page. I choose to narrow the columns, so the report now looks like this:

    image

    28. Also to make this report more readable friendly, let us add a Solid Border style. Select all cells in the Matrix control, and select “Solid” for the “BorderStyle” property

    image

    29. Now let us view the report, save, import, compile and run the report. We now get this output.

    image

    Hmm, something is wrong. I.e. we have 32 bicycles on stock in each of the warehouses. Let us fix this issue.

    30. Go to the DataItem “Item” and set the property “DataItemLink” to “Location Filter=FIELD(Code)”

    image

    31. Now let’s run the report again, but now we set the following filter on the Request Page: Inventory is >0

    image

    We do this to avoid all entries with 0.

    32. Now select “Preview” to see the report.

    image

    33. We now have a working Matrix Report which is sorting Items by Location.

    34. Now let us imagine you would like to know the Inventory Availability on the 52 “PARIS Guest Chair, black” in the “Blue Warehouse” or on the 55 “AMSTERDAM Lamp” in the “Red Warehouse”. So let use the Drill Through to Report feature in Dynamics NAV SP1 to drill through to our existing “Inventory Availability” report 705. Open Visual Studio again by selecting “View / Layout”

    35. Right click the Data field in the Matrix control and select “Properties”

    image

    36. Navigate to the “Navigation” tab

    image

    37. Select “Jump to URL” and add the following expression:

    ="DynamicsNAV:////runreport?Report=705&Filter=Item.%22Location Filter%22:"+Fields!Location_Code.Value+"&Filter=Item.%22No.%22:"+Fields!Item__No__.Value

    image

    What we are doing here is that we open Report 705(Inventory Availability) filtered on Location and Item No., when we click data field in the Matrix report.

    38. Before we save the report, let us make it visible in the report that we have a link to another report.

    With the Data field selected set these properties:

    Color=Blue

    TextDecoration=Underline

    39. Save, Import and Compile the report. Note you might this error:

    image

    Set the EnableHyperlinks=TRUE, and compile again

    40. Run the report. It will now look like this:

    image

    41. Now when we click on the 52 “PARIS Guest Chair, black” in the “Blue Warehouse”, Inventory Availability report will now open based on the our Blue Warehouse and our Paris Guest chair.

    image

    And when we click on the 55 “AMSTERDAM Lamp” in the “Red Warehouse”, Inventory Availability report will now open based on the our Red Warehouse and our Amsterdam Lamp.

    image

    Now let us look at some other Matrix Reports

    1. Items by Location done by Andrey Panko, MVP for Dynamics NAV. This report is very similar to the above walkthrough, it has extra logic on the data part and more advanced coloring and a Totaling to the very right. It does not contain any Drill Through to report 705, but this could easily be added following the above steps(35-37) on how to that.

    image

    2. Items by Location done by Rene Gayer, MVP for Dynamics NAV. This report is also similar to the above reports but here Rene is using the new Drill Through to report, so if click on the Item, left most column, you open a Item Dashboard, with Key Performance Indicators, Stock level illustrated and Sales History.

    image

    image 

    3. GL Entries per Account per Month, done by GAC Business Solutions. This report gives a overview of the entries in GL Account pr month. Notice when clicking the GL Account you will Drill Through to GL Account card, and when clicking the amount in the Matrix you will Drill Through to the Detail Trial Balance Report filtered on month and GL Account

    image

    Please find all the mentioned reports in the attached zip file.

    Thanks, to Andrey Panko, Rene Gayer and GAC Business Solutions for sharing their Matrix Reports.

    Thanks,
    Claus Lundstrøm, Program Manager, Microsoft Dynamics NAV

  • Microsoft Dynamics NAV Team Blog

    How to install more than one Dynamics NAV 2009 Demo Database alternative 2

    • 0 Comments

    Earlier I did describe how to duplicate an installed and existing database in post "How to install more than one Dynamics NAV 2009 Demo Database". This time I will attached the DB directly from DVD.
    The steps is as follow

    1. Create the directory where you would like to have the new database.
    2. Copy the database from \DVD\SQLDemoDatabase\PFiles\Microsoft Dynamics NAV\60\Database\ Demo Database NAV (6-0)_Data.mdf to the new directory.
    3. Run the following command in SQL manager studio and replace the @physname path with the new created on and set @dbname to the database name you would like to use.
      sp_attach_single_file_db @dbname= ‘new_Demo Database NAV (6-0)', @physname= ‘C:\Program Files (x86)\Microsoft Dynamics NAV\60\Database - Copy\Demo Database NAV (6-0)_Data.mdf'

    You need to ensure that the SQL server user have correct permissions to ‘C:\Program Files (x86)\Microsoft Dynamics NAV\60\Database - Copy\' or where you put the db files. I'm using ‘NETWORK SERVICE" as the SQL server user account, the NAV will use this account by default when doing demo installation.

    To set permission

    1. Open properties for the directory.
    2. Click security tab
    3. Click advance
    4. Click edit
    5. If the SQL server user is not present press add and pick the user else select the SQL server user and click edit
    6. Change the permission so that the following permission is checked
      1. Read attributes
      2. Read extended attributes
      3. Create files / write data
      4. Create folders / append data
      5. Write attributes
      6. Write extended attributes
      7. Delete subfolders and files
      8. Delete
    7. Click OK
    8. Click Ok
    9. Click OK
    10. Click OK
  • Microsoft Dynamics NAV Team Blog

    Table Information including Index information (Usage, Blocks and Reads)

    • 0 Comments

    == Nov 9 update ==

    In some cases we got integer overflow when running this query. So the table definitions in the query have now changed from using int to bigint to avoid this.

    == end of update == 

     

    The query below combines these three queries into one:
    Index Usage Query
    Recent Bocking History
    Table Information Query

    It can be used to just see the number of records in each table. But also by just changing "ORDER BY", it can be used to see which index cause most blocking / wait time / updates or locks. Or to compare Index Updates with Index Reads to get an idea of cost versus benefit for each index for the purpose of index tuning.

    So in short, one query gives you:
      - Index / Table Information
      - Index usage (benefits and costs information for each index)
      - Index locks, blocks, wait time and updates per read (cost/benefit).

    The query must be run in your NAV database. It will create a table called z_IUQ2_Temp_Index_Keys and use various Dynamic Management Views to collect information for each index into this table. First time you run it, or if you want to refresh data, you must run the whole query which may take up to a minute of two for each company in the database. After that if you just want to change sorting / get the results again, then you only need to run the last part of the query, beginning with:

    -- Select results


    The last lines suggest various "ORDER BY"s that might be useful to enable instead of the default one, which is by Table Name.

     

    Lars Lohndorf-Larsen (Lohndorf )

    Microsoft Dynamics UK

    Microsoft Customer Service and Support (CSS) EMEA

    These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    --use NavisionDatabase

    IF

     

     

     

     

     

    OBJECT_ID ('z_IUQ2_Temp_Index_Keys', 'U') IS NOT

    NULL

    DROP

     

     

     

     

     

    TABLE z_IUQ2_Temp_Index_Keys

    ;

    -- Generate list of indexes with key list

    create

     

     

     

     

     

    table z_IUQ2_Temp_Index_Keys

    (

    [l1] [bigint]

     

     

    NOT

    NULL,

    [F_Obj_ID] [bigint]

     

     

    NOT

    NULL,

    [F_Schema_Name] [nvarchar]

     

     

    (128)

    NULL,

    [F_Table_Name] [nvarchar]

     

     

    (128) NOT

    NULL,

    [F_Row_Count] [bigint]

     

     

    NULL,

    [F_Reserved] [bigint]

     

     

    NULL,

    [F_Data] [bigint]

     

     

    NULL,

    [F_Index_Size] [bigint]

     

     

    NULL,

    [F_UnUsed] [bigint]

     

     

    NULL,

    [F_Index_Name] [nvarchar]

     

     

    (128)

    NULL,

    [F_Index_ID] [bigint]

     

     

    NOT

    NULL,

    [F_Column_Name] [nvarchar]

     

     

    (128)

    NULL,

    [F_User_Updates] [bigint]

     

     

    NULL,

    [F_User_Reads] [bigint]

     

     

    NULL,

    [F_Locks] [bigint]

     

     

    NULL,

    [F_Blocks] [bigint]

     

     

    NULL,

    [F_Block_Wait_Time] [bigint]

     

     

    NULL,

    [F_Last_Used] [datetime]

     

     

    NULL,

    [F_Index_Type] [nvarchar]

     

     

    (128) NOT

    NULL,

    [F_Index_Column_ID] [bigint]

     

     

    NOT

    NULL,

    [F_Last_Seek] [datetime]

     

     

    NULL,

    [F_Last_Scan] [datetime]

     

     

    NULL,

    [F_Last_Lookup] [datetime]

     

     

    NULL,

    [Index_Key_List] [nvarchar]

     

     

    (MAX)

    NULL

    )

    go

    CREATE

     

     

     

     

     

    NONCLUSTERED INDEX [Object_ID_Index] ON [dbo].

    [z_IUQ2_Temp_Index_Keys]

    (

     

     

     

     

     

    [F_Obj_ID]

    ASC

    )

    go

    CREATE

     

     

     

     

     

    NONCLUSTERED INDEX [Index_ID_Index] ON [dbo].

    [z_IUQ2_Temp_Index_Keys]

    (

     

     

     

     

     

    [F_Index_ID]

    ASC

    )

    go

    CREATE

     

     

     

     

     

    NONCLUSTERED INDEX [RowCount_ID_Index] ON [dbo].

    [z_IUQ2_Temp_Index_Keys]

    (

     

     

     

     

     

    [F_Row_Count]

    ASC

    )

    go

    insert

     

     

     

     

     

    into

    z_IUQ2_Temp_Index_Keys

    SELECT

    (

     

     

     

     

     

    row_number() over(order by a3.name, a2.name))%2 as l1

    ,

    a1

     

     

    .object_id

    ,

    a3

     

     

    .name AS [schemaname]

    ,

    a2

     

     

    .name AS [tablename]

    ,

    a1

     

     

    .rows as row_count

    ,

    (

     

     

     

     

     

    a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved,

    a1

     

     

    .data * 8 AS data

    ,

    (

     

     

     

     

     

    CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS index_size

    ,

    (

     

     

     

     

     

    CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS unused

    ,

    -- Index Description

    SI

     

     

    .name

    ,

    SI

     

     

    .Index_ID

    ,

    index_col

     

     

     

     

     

    (object_name(SIC.object_id),SIC.index_id,SIC.Index_Column_ID

    ),

    -- Index Stats

    US

     

     

    .user_updates

    ,

    US

     

     

    .user_seeks + US.user_scans + US.user_lookups User_Reads

    ,

    -- Index blocks

    IStats

     

     

    .row_lock_count + IStats.page_lock_count

    ,

    IStats

     

     

    .row_lock_wait_count + IStats.page_lock_wait_count

    ,

    IStats

     

     

    .row_lock_wait_in_ms + IStats.page_lock_wait_in_ms

    ,

    -- Dates

    case

    when

     

     

     

     

     

    (ISNULL(US.last_user_seek,'00:00:00.000') >= ISNULL(US.last_user_scan,'00:00:00.000')) and (ISNULL(US.last_user_seek,'00:00:00.000') >= ISNULL(US.last_user_lookup,'00:00:00.000')) then US.

    last_user_seek

    when

     

     

     

     

     

    (ISNULL(US.last_user_scan,'00:00:00.000') >= ISNULL(US.last_user_seek,'00:00:00.000')) and (ISNULL(US.last_user_scan,'00:00:00.000') >= ISNULL(US.last_user_lookup,'00:00:00.000')) then US.

    last_user_scan

    else

     

     

     

     

     

    US.

    last_user_lookup

    end

     

     

     

     

     

    as Last_Used_For_Reads

    ,

    SI

     

     

    .type_desc

    ,

    SIC

     

     

    .index_column_id

    ,

    US

     

     

    .last_user_seek

    ,

    US

     

     

    .last_user_scan

    ,

    US

     

     

    .last_user_lookup

    ,

    ''

    FROM

    (

     

     

     

     

     

    SELECT

    ps

     

     

    .object_id

    ,

    SUM

     

     

     

     

     

    (

    CASE

    WHEN

     

     

     

     

     

    (ps.index_id < 2) THEN

    row_count

    ELSE

     

     

     

     

     

    0

    END

    )

     

     

     

     

     

    AS [rows]

    ,

    SUM

     

     

     

     

     

    (ps.reserved_page_count) AS reserved

    ,

    SUM

     

     

     

     

     

    (

    CASE

    WHEN

     

     

     

     

     

    (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count

    )

    ELSE

     

     

     

     

     

    (ps.lob_used_page_count + ps.row_overflow_used_page_count

    )

    END

    )

     

     

     

     

     

    AS data

    ,

    SUM

     

     

     

     

     

    (ps.used_page_count) AS

    used

    FROM

     

     

     

     

     

    sys.dm_db_partition_stats

    ps

    GROUP

     

     

     

     

     

    BY ps.object_id) AS

    a1

    LEFT

     

     

     

     

     

    OUTER JOIN

    (

     

     

     

     

     

    SELECT

    it

     

     

    .parent_id

    ,

    SUM

     

     

     

     

     

    (ps.reserved_page_count) AS reserved

    ,

    SUM

     

     

     

     

     

    (ps.used_page_count) AS

    used

    FROM

     

     

     

     

     

    sys.dm_db_partition_stats

    ps

    INNER

     

     

     

     

     

    JOIN sys.internal_tables it ON (it.object_id = ps.object_id

    )

    WHERE

     

     

     

     

     

    it.internal_type IN (202,204

    )

    GROUP

     

     

     

     

     

    BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id

    )

    INNER

     

     

     

     

     

    JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id )

    INNER

     

     

     

     

     

    JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id

    )

    inner

     

     

     

     

     

    join sys.indexes SI ON (SI.object_id = a1."object_id"

    )

    inner

     

     

     

     

     

    join sys.index_columns SIC ON (SIC.object_id = SI.object_id and SIC.index_id = SI.index_id

    )

    left

     

     

     

     

     

    outer join sys.dm_db_index_usage_stats US ON (US.object_id = SI.object_id and US.index_id = SI.index_id and US.database_id = db_id

    ())

    left

     

     

     

     

     

    outer join sys.dm_db_index_operational_stats(NULL,NULL,NULL,NULL) IStats ON (IStats.object_id = SI.object_id and IStats.index_id = SI.index_id and IStats.database_id = db_id

    ())

    WHERE

     

     

     

     

     

    a2.type <> N'S' and a2.type <> N

    'IT'

    order

     

     

     

     

     

    by row_count

    desc

    go

    -- Populate key string

    declare

     

     

     

     

     

    IndexCursor cursor

    for

    select

     

     

     

     

     

    F_Obj_ID, F_Index_ID from

    z_IUQ2_Temp_Index_Keys

    for

     

     

     

     

     

    update of

    Index_Key_List

    declare

     

     

     

     

     

    @objID

    int

    declare

     

     

     

     

     

    @IndID

    int

    declare

     

     

     

     

     

    @KeyString VARCHAR(MAX

    )

    set

     

     

     

     

     

    @KeyString =

    NULL

    open

     

     

     

     

     

    IndexCursor

    set

     

     

     

     

     

    nocount

    on

    fetch

     

     

     

     

     

    next from IndexCursor into @ObjID,

    @IndID

    while

     

     

     

     

     

    @@fetch_status = 0

    begin

    set

     

     

     

     

     

    @KeyString =

    ''

    select

     

     

     

     

     

    @KeyString = COALESCE(@KeyString,'') + F_Column_Name +

    ', '

    from

     

     

     

     

     

    z_IUQ2_Temp_Index_Keys

    where

     

     

     

     

     

    F_Obj_ID = @ObjID and F_Index_ID =

    @IndID

    ORDER

     

     

     

     

     

    BY F_Index_ID,

    F_Index_Column_ID

    set

     

     

     

     

     

    @KeyString = LEFT(@KeyString,LEN(@KeyString) -2

    )

    update

     

     

     

     

     

    z_IUQ2_Temp_Index_Keys

    set

     

     

     

     

     

    Index_Key_List =

    @KeyString

    where

     

     

     

     

     

    current of

    IndexCursor

    fetch

     

     

     

     

     

    next from IndexCursor into @ObjID,

    @IndID

    end

     

     

     

     

     

    ;

    close

     

     

     

     

     

    IndexCursor

    deallocate

     

     

     

     

     

    IndexCursor

    go

    -- clean up table to one line per index

    delete

     

     

     

     

     

    from

    z_IUQ2_Temp_Index_Keys

    where

     

     

     

     

     

    [F_Index_Column_ID] >

    1

    go

    -- Select results

    select

    [F_Table_Name] TableName

     

     

    ,

    [F_Row_Count] No_Of_Records

     

     

    ,

    [F_Data] Data_Size

     

     

    ,

    [F_Index_Size] Index_Size

     

     

    ,

    [F_Index_Name] Index_Name

     

     

    ,

    [F_User_Updates] Index_Updates

     

     

    ,

    [F_User_Reads] Index_Reads

     

     

    ,

    case

    when

     

     

     

     

     

    F_User_Reads = 0 then

    F_User_Updates

    else

     

     

     

     

     

    F_User_Updates /

    F_User_Reads

    end

     

     

     

     

     

    as Updates_Per_Read

    ,

    [F_Locks] Locks

     

     

    ,

    [F_Blocks] Blocks

     

     

    ,

    [F_Block_Wait_Time] Block_Wait_Time

     

     

    ,

    [F_Last_Used] Index_Last_Used

     

     

    ,

    [F_Index_Type] Index_Type

     

     

    ,

    [Index_Key_List] Index_Fields

    from

     

     

     

     

     

    z_IUQ2_Temp_Index_Keys

    --order by F_Row_Count desc, F_Table_Name, [F_Index_ID]

    --order by F_User_Updates desc

    --order by Blocks desc

    --order by Block_Wait_Time desc

    --order by Updates_Per_Read desc

    order

     

     

     

     

     

    by

    F_Table_Name

  • Microsoft Dynamics NAV Team Blog

    Troubleshooting multi-machine installations of NAV 2009

    • 0 Comments

    The NAV 2009 documentation walkthroughs provide step-by-step instructions for installing NAV 2009 on 2 or 3 machines. However, we have found that some of the same configuration issues come up time after time after installation.

    When on calls with partners and customers, it seemed to me that this information was spread out all over the place, so I wanted to organize it in a different way for troubleshooting purposes so that I would have most everything in one place. Hopefully this will be helpful to others as well.

    The intention of this post is to provide a checklist of sorts for troubleshooting some of the areas where we frequently find errors or omissions in configuration after NAV 2009 has been installed. 

    Errors on the RTC resulting from configuration problems include but are not limited to...

    Login failed for user...

    A Server was not found at...

    The User ID and password are invalid

    *************************************

    Before anything else, check to see what accounts are running the NAV and SQL services. Make note of domain account names, machine names, and FQDN (Fully qualified domain name)

     

     

    Check for Incorrect SPN’s

    When the NAV Server and the database are on separate machines AND the NAV Service is running under a Domain Acct:

    2 SPN’s have to be set up for the NAV Service. SERVER will be different depending on your server name.

    i.e. Server_DynamicsNAV/Server.FQDN:7046

     

    Examples…

    SERVER_DynamicsNAV/SERVER.NAV2009DC.LAB:7046


    SERVER_DynamicsNAV/SERVER:7046


    SPN for SQL Service is not needed IF SQL is running under NetworkService.

    SPN for SQL IS needed if SQL is running under a Domain account – see "SQL SPN" at the end of this post

    When the NAV Server and the database are on separate machines, AND the NAV Service is running under NetworkService account:

    No SPN is needed for the NAV Service.

    SPN for SQL Service is not needed IF SQL is running under NetworkService.

    SPN for SQL IS needed if SQL is running under a Domain account – see "SQL SPN" at the end of this post

    Tools

    Tools are normally installed on Win 2008 by default (depending on features selected). For 2003, install the Windows Support Tools

    -          Windows 2003 Support Tools

    o   ADSIEdit.msc

    o   SETSPN.exe

     

    Setting the SPN :

    o   Run the ADSI Edit tool on any server computer in the domain. To do this, click Start, click Run, type Adsiedit.msc, and then click OK.

    In the ADSI Edit window, expand Domain, expand DC, expand CN=Users, right-click CN= AccountName, and then click Properties.

    Note: The AccountName placeholder represents the domain account you are using to start the NAV Server (and/or SQL) service.

    In the Properties dialog box, double-click the servicePrincipalName attribute to open the Multi-valued String Editor dialog box. (There are a few shortcuts to find the servicePrincipalName, you can check the ‘Show only attributes that have values’ to shorten the list or click in the Attributes box and type ‘ser’ to jump close to the attribute.)

    In the Value to add box, add a SPN for the NAV Server (or SQL Server), and then click Add, keeping in mind that the SERVER will be different depending on your server name.  For SQL SPN see Appendix A.

    SERVER_DynamicsNAV/FQDN:7046

    Note: Replace “SERVER” with the name of your server, and “FQDN” with the fully qualified domain name, such as “SERVER.MICROSOFT.COM”.

    In the Value to add box, add a SPN for the NAV Server, and then click Add. Keeping in mind that the SERVER will be different depending on your server name.

    SERVER_DynamicsNAV/SERVER:7046

    Note: Here for the “SERVER” value, only specify the name of the server.

    Click OK two times.
    Close the ADSI Edit window.

     

    Check Delegation

    When running the NAV Service under a Domain Account:

    Delegation has to be set up for the account running the NAV service.

    Note: The Delegation tab will only be present after adding the SPN to the domain user account.

    o   Click Start, then click Run.

    o   Type in dsa.msc and click OK.

    o   Expand the Domain and then click on Users.

    o   Locate the domain user account you are using , right click and select Properties.

    o   Under that Delegation tab, select the ‘Trust this user for delegation to any service (Kerberos only)’, then click OK. (This is not constrained delegation as mentioned in the Walkthrough, but this makes it a little easier to setup delegation. You can always come back after it is setup and working to implement constrained delegation.)

    o   Close the Active Directory Users and Computers window.

    o   Note: for Constrained delegation, select Trust this user for delegation to specified services only and then select MSSQLSvc.

     

     

    When running the NAV Service under NetworkService Account:

    Delegation has to be set up for the machine running the NAV service.

    o   Click Start, and then click Run.

    o   Type in dsa.msc and click OK.

    o   Expand the Domain and then click on Computers.

    o   Locate the computer name, right click and select Properties.

    o   Under that Delegation tab, select the ‘Trust this user for delegation to any service (Kerberos only)’, then click OK.

    o   Close the Active Directory Users and Computers window

     

    Check SQL Logins and OCL

    Adding the login(s) to SQL and setting up the Object Change Listener (OCL):

    OCL is NOT required if the NAV Server and SQL Server are on the same machine AND the NAV service is running under Network Service.

    If using a Domain User to run services, make sure the login has been added to both SQL and NAV. Also check that user has Full Control to the server folder.

    o   The account may already exist in SQL but the permissions must be manually set correctly):

    Open Microsoft SQL Server Management Studio.
    Click Security to expand the tree-view, right-click Logins, and then select New Login.
    This opens the Login - New dialog box.
    Add the domain user account in the Login name field, using the following format:

    domain\domainUser

    Click OK to exit the Login - New dialog box.
    Click Databases, Demo Database NAV (6-0) or other database name, and then click Security to expand the tree view.
    Under Security, right-click Users, and then select New User.
    This opens the Database User - New dialog box.
    Add the domain user account in the User name and Login name field, using the following format:

    domain\domainUser


    o   Add $ndo$navlistener in the Default schema field.
    Click the Securables page.
    Click Add, click OK, click Object Types, check Tables and then click OK. Click Browse, check the [dbo].[Object Tracking], click OK, click OK again.
    In the Explicit permissions check Grant on the Select permission.
    Click OK to exit the Database User - New dialog box.
    Close Microsoft SQL Server Management Studio.

     

    If the NAV server and SQL Server are on different machines AND the Network Service Account is running the NAV Service, then the Login and OCL must be set up using the same steps but for the machine account rather than the domain user…
    Use the above steps, but replace the domain account with the machine account, i.e. <domain>\<computername>$

     

    Check the configuration of Delegation for the RTC

    Change the ClientUserSettings.config on the computer running the RTC, under the current user's profile, to define that a domain user account is to be used when connecting to the NAV Service tier.

    On Windows Vista or Windows Server 2008, the default location is:

    X:\Users\\AppData\Local\Microsoft\Microsoft Dynamics NAV

    On Windows XP or Windows Server 2003, the default location is:

    X:\Documents and Settings\\Local Settings\Application Data\Microsoft\Microsoft Dynamics NAV

    Add the following key to the file:
    <add key="DelegationInfo" value="DomainUser"></add>

    There are two possible values: NetworkService and DomainUser. To enable delegation, set the parameter to DomainUser.


    This will need to be repeated for all workstations that will be using the RTC.

    After confirming all items above and making any changes, be sure to stop and start the NAV
    Server service before you attempt to re-connect using the RTC. If you still encounter the error message, remember that Kerberos tickets last for 10 hours, so if you add/change the SPN, you may either have to wait for any existing tickets to expire or download KerbTray and attempt to expire any existing tickets. This is found in the Windows Server 2003 Resource Kit, which can be downloaded from:

    http://www.microsoft.com/Downloads/details.aspx?FamilyID=9d467a69-57ff-4ae7-96ee-b18c4790cffd&displaylang=en

     

    Check the SQL SPN (if applicable)

    SPN must be added for SQL Server when running under a domain account.

    There are errors in the documentation walkthrough regarding how to create the SPN for SQL. The easiest way to check for the correct SPN syntax is to look at how it automatically generates the SPN when running with network service (switch the account running the SQL service to NetworkService, check the SPN and use that to set up the SPN for a Domain Acct.

    An example might be...

    MSSQLSvc/LALA1719334.NAV2009DC.LAB:1433

    where

    SQL Service = MSSQLSvc

    Server = LALA1719334

    Domain = NAV2009DC.LAB

    Port = 1433

     

    Laura K. Lake ( lalake)

    Microsoft Dynamics NA


    Microsoft Customer Service and Support (CSS) North America

  • Microsoft Dynamics NAV Team Blog

    How do Record Links encode their data in SQL server?

    • 5 Comments

    Recently I was asked about the encoding that it is done for record links in SQL.

    If you are not familiar with Record Links, which have been available since NAV version 5.0, just open any Card or List and then click Edit -> Links (Ctrl + L).

    Record Links enable users to add links to documents to any record in Microsoft Dynamics NAV, such as a sales order or purchase order. The document or order can be stored in Microsoft SharePoint or on a file server and the user can access the document from Microsoft SharePoint or give access to others.

    In SQL, these Record Links are saved under the “Record Link” table, and contains (among others) the Record ID, URLs, Description, etc.

    Basically, the Record ID should be able to uniquely identify to which record this link belongs, so…. Not surprisingly, this Record ID will contain the table number and corresponding key values (+ 2 null bytes).

    In principle, what I have just written should be enough to understand the format, but I will further explain with the help of some examples.

    Let’s start with Annette Hill, one of the employees that work at CRONUS International:

    Anette Hill

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Annette’s Record ID on the Record Link table (in SQL) will look as “0x501400000089FF4148000000”. As I wrote before, this should correspond to the table number, the keys and 2 null bytes… That is:

    The first 4 bytes correspond to the table number, in this case 0x50 14 00 00, which should correspond to table 5200.

    Now, you must be wondering, how comes 5200 can be converted to such a strange binary representation. Well, we will have to remember two things: 1) The number needs to be in hexadecimal format, and 2) It needs to be encoded in little endian as we are working with Intel processors (just use your search engine, if you don’t know/remember).

    So, we need 4 bytes for (table) 5200, that in hex is: 00 00 14 50, if we convert it to little endian, we will get: 50 14 00 00.

    The next 2 bytes correspond to the data type of the key. The type is CODE, which is type 137, which is only converted to hex: 00 89. At this point you must be thinking that I am pulling your leg, as this is supposed to be further converted using little endian, but it turns out that the keys data types are encoded in their binary representation, in order to reuse code when searching for them.

    The following 4 bytes correspond to the key itself. Interestingly enough, code data types will start with 1 byte that can tell you the length of the code (if numeric) or FF which would correspond to a character based code type. In this case, we have 0xFF, meaning that we have characters and those are 0x41 and 0x48 + null termination 0x00. Of course 0x41 corresponds to ‘A’ and 0x48 corresponds to ‘H’ (in “extended” ASCII), which is the key, as can be seen on the ‘No.’ field.

    The last 2 bytes will always be null characters.

    Let’s do something a little bit more interesting, now with Bill of Materials:

    Bill of Materials

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    This link will look as: 0x5A0000000089FF313932342D57000087102700000000

    The first 4 bytes correspond to the table number, in this case 0x5A 00 00 00, which should correspond to table 90.

    The next 2 bytes correspond to the data type of the first key. The type is CODE, which is type 137, which is only converted to hex: 00 89.

    The following 8 bytes correspond to the key itself. In this case, we have 0xFF, meaning that we have characters and those are ‘1924-W’ (+ null).

    The next 2 bytes correspond to the data type of the next key. The type is Integer, which is 135, that is converted to hex: 00 87.

    The following 4 bytes correspond to the key itself. In this case, we have 0x10270000, converting this to an integer, corresponds to 10000 (using the little endian rule).

    The last 2 bytes will always be null characters.

    If you are wondering where these values came from, Open Table 90, and verify that the Key is in fact ‘Parent Item No.,Line No.’ and that the values that we have decoded, correspond to the data we have added the links to.

    Of course, you should always remember that this format is subject to change and there is no guarantee that it will be preserved on future versions, also remember the standard following claim.

     

    -- jtorres

  • Microsoft Dynamics NAV Team Blog

    Supply Planning in Microsoft Dynamics NAV 2009 SP1

    • 0 Comments

    The Supply Planning white paper for Microsoft Dynamics NAV 2009 SP1 has been updated on both PartnerSource and CustomerSource. The Supply Planning white paper is intended for planning experts who are involved in the implementation or support of supply planning functionality in Microsoft Dynamics NAV installations and for those who need to make modifications within the area. It gives an overview of the concepts and principles that are used within the supply planning area of Microsoft Dynamics NAV 2009 SP1.

    In this version, the following issue is addressed:

    When using reorder policies Maximum Qty and Fixed Reorder Qty, the planning system focuses on the projected inventory in the given time-bucket only. This means that the planning system may suggest superfluous supply when negative demand or positive supply changes occur outside of the given time bucket.

    The issue is fixed as follows:

    A warning is displayed when superfluous supply causes the projected inventory to exceed the highest projected inventory in an ideal supply plan, defined as the "overflow level". The warning message displays the overflow level that the planner must adjust to if he wants to avoid the superfluous supply.

    To download the white paper, see the following links.

    PartnerSource:

    https://mbs.microsoft.com/partnersource/deployment/documentation/whitepapers/supplyplanninginmicrosoftdynamicsnav2009.htm

    CustomerSource

    https://mbs.microsoft.com/customersource/documentation/whitepapers/supplyplanninginmicrosoftdynamicsnav2009.htm

Page 1 of 1 (7 items)