Microsoft Dynamics NAV

Team Blog

  • Microsoft Dynamics NAV Team Blog

    Strange errors come during change SourceExpr value in NAV report designer...

    • 0 Comments

    Our colleague raised problem: why he receives strange error during report compile, after he changed SourceExpr value for some control in report designer in NAV 2009.

    Error message was:

    Error while validating RDL content:
    The Value expression for textbox “CompanyInfo” refers to the field ‘PricesInclVAT_YesNo’. Report Item expressions can only refers to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

    Error itself looks very strange because:
    1. It comes from validating RDL content, but we changed nothing in it.
    2. Changed was SourceExpr in NAV classic client report designer>sections>control property, and it has no relations to field mentioned in error. Actually what was changed: report 204 – Sales-Quote; control 116; SourceExpr had value "Sales Header"."Prices Including VAT" and now it has value FORMAT("Sales Header"."Prices Including VAT").

    If we change back value – error disappears.

    After deeper analyze we found that RDL dataset has field ‘PricesInclVAT-YesNo’, but after report designer change SourceExpr value by adding FORMAT – this field disappears from dataset. So problem really is in the SourceExpr value, however value itself is correct. And after few more research we found there is control 167 – name PriceIncVAT_YesNo which has exactly the same SourceExpr value - FORMAT("Sales Header"."Prices Including VAT").

    Conclusion: only one source expression is pushed to RDL, if there are few controls with the same SourceExpr, only first will be in RDL, other will not appears there.

     

    I asked NAV architects about this issue and they answered:

    The field names that we show in the RDL dataset do NOT come from the SourceExpr property "directly". Each control on a report has a DatasetFieldName property, which is what we use to build the field names in the RDL dataset.

     

    The DatasetFieldName property is then derived from the SourceExpr property the first time you open Visual Studio for a report using the View --> Layout menu item. After this, if you change the SourceExpr, the DatasetFieldName does not get changed. (The whole purpose of this property is to fix the dataset field name so even if you change your SourceExpr, you won’t need to redesign your RDL). This should cover most of the user scenarios.

     

    In a few scenarios, you are not covered though: one is, if you move sections or controls in the layout, and if two controls have the same SourceExpr value, the control we choose to export to the report dataset may change with another one with the same SourceExpr. Since they have different DatasetFieldName properties, your RDL will break. We don’t cover this scenario and we don’t believe that we could fix this easily.

     

    In another scenario: if you change the SourceExpr, and the data type of the new field is different than the old one, RDL compilation may fail due to the expressions used on that field - as these are data type dependent.

    So, during report design be very careful with modifying 2 properties: SourceExpr and DataSetFieldName. Changes could impact RDL very much and result could be not you are expecting. Of course this is important if you already have RDL report prepared, if you will create new layout by using "Laoyout suggestion", problems will not come, because new RDl laoyout will be created regarding earlier described rules.

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

    Special thanks to John Vestergaard from Logica Danmark A/S for pointing us to this feature.

    Gedas Busniauskas (gediminb)

    Microsoft Lithuania
    Microsoft Customer Service and Support (CSS) EMEA

  • Microsoft Dynamics NAV Team Blog

    Where is the zup file again?

    • 0 Comments

    I keep forgetting where the CustomSettings.config, ClientUserSettings.Config etc files are. So I keep looking it up again in "Where is the zup file". This time, I collected the location of these files in a NAV form so I don't have to look them up anymore. In addition to this you can edit the files from the form directly. It looks like this:

     

    ZupForm 

     

    The first time you run the form it will insert a record with the default settings. If your installation is in different folders, then you can of course just modify this. You can also create new records in case you have multiple NAV2009 installations / config files.

    Note: If you run on Vista / Win2008 and want to update the config files from here, then you may have to start your NAV client as Administrator, or you may get "access denied" when clicking the update button.

     

    RunasAdmin

     

    I hope this makes it simpler to change setup settings. Objects attached are:

    Type ID Name
    Table 74250 NAV2009 Config Files
    Form 74250 NAV2009 Config Files Card

     

    Instructions for use: Run form 74250. 

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

    Lars Lohndorf-Larsen

    Microsoft Dynamics UK

    Microsoft Customer Service and Support (CSS) EMEA

  • Microsoft Dynamics NAV Team Blog

    SPN name Changes

    • 0 Comments

    It should be a common installation scenario to have NAV 2009 components installed on three different machines, one for the database hosted by SQL Server, one for the Microsoft Dynamics NAV Server (middle tier server) and the corresponding client machines. This installation is explained in MSDN “How To: Set Up Delegation”, however, what I wanted to talk about is the difference in SPN naming from 6.0 RTM and 6.0 SP1 (or hotfixes which are build 28795 or higher).

    The names used before were in the format “NAVSERV_DynamicsNAV/NAVSERV.yourDomain.yourCompany.com:7046”, the new name conventions are:

    DynamicsNAV/NAVSERV.yourDomain.yourCompany.com:7046”, and

    DynamicsNAV/NAVSERV:7046

     

    Both names should be registered, to minimize possible issues.

    This change should simplify the “Three tiers” installation in relatively complex name networks, for instance, when it contains multiple A or CNAME records.

    -- jtorres

  • Microsoft Dynamics NAV Team Blog

    Style Sheet Tool 2.0 now available

    • 6 Comments

    A new version of the Style Sheet Tool for Microsoft Dynamics NAV is now available. This will also support Microsoft Dynamics NAV 2009 and our upcoming SP1 release.

    Please find it here:

    CustomerSource: https://mbs.microsoft.com/customersource/downloads/servicepacks/NAVStyleSheetTool

    PartnerSource: https://mbs.microsoft.com/partnersource/deployment/resources/supplements/NAVStyleSheetTool

    Thanks to Robert Miller for making this happen

    /Claus

  • Microsoft Dynamics NAV Team Blog

    Error 1935 when installing Dynamics NAV 5.0 SP1

    • 5 Comments

    When I tried to install the NAV 5.0 SP1 client I ran into an error 1935. This may happen on Vista and Windows 2008.

    Error 1935; an error occurred during the installation of
    assembly component {98CB24AD-52FB-DB5F-A01F-C8B3B9A1E18E}. HRESULT:0×800736CC.

    The solution is simpler then it seems. You need to install Microsoft Visual C++ 20005 SP1 Redistributable pack. Remember if you are running on a 64 bit system you need to install both x86 and x64 package.

    x86
    x64

  • Microsoft Dynamics NAV Team Blog

    How to manage NAV DBCS data using C#

    • 0 Comments

    It is known that NAV CC (Classic Client) does not support natively DBCS (Double-Byte Character Set) and so does the new NAV 2009 RTC (Role Tailored Client).

    Due to differences in how the CC and RTC check the code page isn’t still possible to enable DBCS in the RTC but this is possible for the CC by implementing new fin.stx file.

    This last sentence is duly written and explained in KB 915374.

    How to enable and to display double-byte character sets in Microsoft Dynamics NAV
    https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb;EN-US;915374

    Following  the KB link is possible to enable DBCS for several NAV versions included NAV  2009.

    … But what if we want to develop a simple WinForm project in VC# that access the database in order to retrieve and write information in the same way NAV does?

    The following example will guide you through the creation of a simple Form using VS (Visual Studio ) and transforming Japanese characters in their “NAV equivalent” in order to deal correctly with the DBCS data management inside the NAV database.

    The example proposed has been developed using the following scenario:

    1.       Windows Server 2008

    2.       SQL Server 2008

    3.       Visual Studio 2008 SP1

    4.       NAV 2009 RTM W1

    ENABLE DBCS IN NAV 2009

    a.       Download and install a copy of NAV 2009 W1 RTM.

    Install the sample database and the Classic Client.

    The SQL sample database should use Windows Collation for Afrikaans, Basque, Catalan, Dutch, English, Faeroese, German, Indonesian, Italian, Portuguese.

    This can be easily checked in NAV by checking the collation tab in

    File > Database > Alter

    b.      In Windows Server 2008 select Japanese as language for non-Unicode programs.

    Control Panel > Regional and Language Options > Administrative tab

    Change the current language for non-Unicode programs to: Japanese (Japan)

    (you will be prompted to reboot the machine)

    c.       Download the zip file contained in KB 915374

    d.      Extract the fin.stx file from the folder NAV 2009

    e.      Substitute the fin.stx file that is located in the Classic client folder with the brand new one extracted.

    E.g. Path for Classic Client folder:

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

    f.        Substitute the fin.stx file that is located in the ENU folder of the Classic Client with the brand new one extracted.

    E.g. Path for Classic Client folder:

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

    Now you should be able to manage Japanese characters like those ones…

     

    NAV with DBCS enabled

     

     

    WHAT TYPE OF ENCODING?

    Roughly speaking regarding what we have done is merely enabling the Japanese characters to be stored into SQL Server but… not directly!

    The Japanese code page is 932 (Shift-JIS) while we are using the  Latin1 code page 1252 to store the data. To know more about Windows Collation Designators you can check:

    http://msdn.microsoft.com/en-us/library/aa176553.aspx

    This means that while we are typing Japanese chars in the NAV classic Client (using code page 932), this have been reverted (translation encoded) in their respective characters using code page 1252 and stored in this way in the SQL Server field.

    This means that while we are seeing this in NAV :

    ラドクリフ、マラソン五輪代表に1m出場にも含み

    … those are the characters effectively stored in SQL Server …

    ƒƒhƒNƒŠƒtAƒ}ƒ‰ƒ\ƒ“ŒÜ—Ö‘ã•\‚É1–œmoê‚É‚àŠÜ‚Ý

    Then the solution can come out clearly: use C# encoding function to manage the ‘translation’ between one set of character to another.

    LET’S BUILD WinFormNavDBCS

    1.       Open Visual Studio 2008

    2.       Create a brand new Visual C# project

    Go to File > New > Project

    Select project types: Visual C# > Windows > Windows Forms Application

    Give the project a Name: WinFormDBCS

    Click OK

    3.       Design your form like this (2 TextBox, 1 Button)

    clip_image004

    4.       Add this code to your form:

    using System;

    using System.Collections.Generic;

    using System.ComponentModel;

    using System.Data;

    using System.Drawing;

    using System.Linq;

    using System.Text;

    using System.Windows.Forms;

     

    namespace WinFormDBCS

    {

        public partial class Form1 : Form

        {

            public Form1()

            {

                InitializeComponent();

            }

     

            private void button1_Click(object sender, EventArgs e)

            {

                //Create a new variable of type Encoding and

                // assign it the Latin

         //  code page 1252

                //To know more about it

                // http://msdn.microsoft.com/en-us/library/system.text.encoding_members.aspx

                Encoding nav = Encoding.GetEncoding(1252);

               

                //Create a new variable of type Encoding and assign it the Japanese

         //  code page 932

                Encoding unicode = Encoding.GetEncoding(932);

     

                //Populate a byte variables withe the value of the first TextBox written

         //  in Japanese characters using the Japanese code page 932

                byte[] unicodeBytes = unicode.GetBytes(textBox1.Text);

     

                //Display in the second TextBox the result of the encoding conversion of

         //  the byte stored in Japanese directly in the Latin code page equivalent

                //Tip: you can substite the mere text display with some simple code that

         //  writes directly this value into a NAV Text field

                textBox2.Text = nav.GetString(unicodeBytes);

            }

        }

    }

     

    5.       Press F5 to start debugging your project.

    6.       Insert this Japanese string (or whatever Japanese sentence you want to) in the first TextBox:

    ラドクリフ、マラソン五輪代表に1m出場にも含み

    7.       Press the Transform Data button and… see the results:

    clip_image006

    8.       To check if NAV ‘digest’ those newly created string you have to select the whole string converted in the second TextBox and copy it:

     clip_image008

    9.       Open SQL Server Management Studio and paste it into the “Address 2” field of customer 01121212.

    Expand “Demo Database NAV (6-0)”

    Expand “Tables”

    Search for “CRONUS International Ltd_$Customer” table

    Right click on “CRONUS International Ltd_$Customer” and click on “Edit top 200 rows”

    Search for customer 01121212

    Place the cursor in the “Address 2” field and paste the content in this field

    clip_image010

    10.   Browse to the next record and close SQL Server Management Studio.

    CHECK THE FINAL RESULT

    Open NAV and… go to customer 01121212.

    clip_image012

     

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

    Best Regards,

    Duilio Tacconi (dtacconi)

    Microsoft Dynamics Italy

    Microsoft Customer Service and Support (CSS) EMEA

     

    Special thanks to Kenny Vaes of Helios-IT

  • Microsoft Dynamics NAV Team Blog

    What do SPNs actually do - NAV 2009

    • 0 Comments

    Three terms keep coming up when setting up NAV 2009 when NAV-server (middle tier) runs on a different machine than SQL Server:
      - Delegation / Impersonation
      - Kerberos
      - SPN

    Delegation / Impersonation is what it says it is. Kerberos is handled more or less automatically by Windows. But what do SPNs actually do....

    (un)fortunately SPN can't be explained without first explaining Delegation and Kerberos.

     


    Delegation / Impersonation:
    The end-user (RTC) logs on to the NAV-Server which then logs on to the SQL Server - using the end-user's credentials (impersonating the end-

    user). All logins here are Windows logins. Impersonation would also be a nice way for a hacker to gain access, so therefore Windows requires

    that a domain administrator specifically allows the NAV-server to impersonate users on the SQL Server.

    This is only when the NAV-Server and SQL Server runs on two different machines. When they run on the same machine, then Windows has already

    authenticated the end-user when they connected to the NAV-Server. So when the NAV-Server logs on to the SQL Server (on the same machine),

    Windows has already knows the end-user. So in this case there is no need to set up delegation, and hence no need to worry about Kerberos and

    SPNs.

    But with two machines, delegation is needed. Delegation requires Kerberos.

     

    Kerberos:
    Connections between two users happen all the time. What Kerberos adds to such a connection is a certificate (ticket) which ensures that each

    of the two users can trust the identity of the other user. This is actually a kind of countermeasure against a user (hacker) impersonating

    another user. Windows requires that before it allows delegation, that the connection being delegated is a Kerberos connection. In this way,

    Windows has assurance that the user credentials being forwarded are valid. So when RTC connects to the NAV-server, it has to do this with a

    Kerberos connection.

    A Kerberos connection is between two Windows user accounts. A user doesn't know (shoulnd't know) what user account the NAV-server runs under.

    This is where SPNs come in. So, finally we get to the SPNs:

     

    SPN:
    SPN (Service Principal Name) is a simple table that maps a service to a user account. Think of it as a table with two fields: Service-name,

    and Windows User name. When the NAV client wants to start a Kerberos connection to the NAV-server, it will connect to for example

    DynamicsNAV\Nav-Server:7046. Kerberos requiring a user name will then look this up in the SPN table and find the user name there. So, only if

    an SPN has been created for the account that runs the NAV-service, will Windows be able to then start a Kerberos connection. And the user will

    never need to know which that account the NAV-server is running under.

    When setting up SPNs, make sure that:
      1) The account that runs the NAV-service has an SPN that contains what the NAV client will connect to (Server name in the right format),and
      2) Quite a common probem: Make sure that there are no duplicate SPNs. If you change the NAV-service to run under a different account you must set up SPNs for the new account. Then make sure to also remove the SPNs for the old account.

     


    So in short: Delegation / Impersonation requires Kerberos. Kerberos connection to a service running an unknown account, requires SPN. And,

    visa versa, if SPNs have not been set up correctly, then Kerberos won't work so then Delegation wont work either.


     

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

     

    Best regards


    Lars Lohndorf-Larsen (Lohndorf)

    Microsoft Dynamics UK


    Microsoft Customer Service and Support (CSS) EMEA

  • Microsoft Dynamics NAV Team Blog

    Failed to open key when Installing Dynamics NAV 2009

    • 0 Comments

    When installing Dynamics NAV 2009 one of my friend's rune into this problem:

    --------------------------
    Microsoft Dynamics NAV 2009 Build 27808
    Error Report
    RoleTailored client
    Microsoft Dynamics NAV 2009 RoleTailored client
    Fatal error during installation.

    Could not open key: UNKNOWN\MSComDlg.CommonDialog.1\CLSID. Verify that you have sufficient access to that key, or contact your support personnel.
    --------------------------

    The problem here is that we have a mismatch of .NET. The simplest solution is to install latest version of a .NET and run the installer again.

    .NET Framework 3.5 SP1

  • Microsoft Dynamics NAV Team Blog

    Setting up NAV 2009 on Three machines

    • 1 Comments

    Setting up delegation and SPNs for NAV 2009 on three machines (when the NAV Server and SQL Server are on two seperate machines) is described in "Walkthrough: Installing the Three Tiers on Three Computers". But in addition to this, also be aware of the following:

    • The syntax of SPNs has changed since KB 968189
    • You may need to set UserAccountControl flag in Active Directory

    Also thanks to our German colleagues for already posting this information here and here.

      

    Syntax of SPNs has changed since KB 968189

    After installing KB 968189 for NAV 2009 (Build 6.0.28795.0) the SPNs now have this format:
    <Instance>/<server host>:<port>
    <Instance>/<server FQDN>:<port>


    So for example if the SPNs used to be set like this:

    NAV-SERVER_DynamicsNAV/NAV-SERVER.Domain.com:7046
    NAV-SERVER_DynamicsNAV/NAV-SERVER:7046

    From this build and later they have to be set like this:
    DynamicsNAV/NAV-SERVER.Domain.com:7046
    DynamicsNAV/NAV-SERVER:7046


    About KB 968189 itself, it solves a very specific problem where it may not be possible to connect if the domain has multiple DNS Suffixes, which is quite unusual. Most installations are not affected by this. You can check whether you are affected by running ipconfig from a command prompt, and see if it lists more than one domain name under "Connection-specific DNS Suffix".

     

    Setting UserAccountControl flag

    In some cases you have to set UserAccountControl as described in KB 305144. You set this flag by running ADSIEDIT.msc, go to properties of the user account running your NAV Server, and then select UserAccountControl. Setting it to 17301504 means TRUSTED_FOR_DELEGATION+TRUSTED_TO_AUTH_FOR_DELEGATION

     

     

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

     

    Best regards


    Lars Lohndorf-Larsen (Lohndorf )

    Microsoft Dynamics UK


    Microsoft Customer Service and Support (CSS) EMEA

  • Microsoft Dynamics NAV Team Blog

    Trace flag in SQL Server for Dynamics NAV 2009

    • 0 Comments

    When starting the RTC client or open the database in classic client you will get a warning if you have not set the trace flag correct. Because of changed security in SQL Server 2005 Dynamics NAV 2009 will only connect to the SQL Server if the trace flat ‘4616' is set.

    How to:

    1. Open SQL Server Configuration Manager
    2. Right click on MSSQLSERVER and select properties
    3. In properties click advanced tab
    4. Click to the right of Setup Parameters and go to the end of the line
    5. Add this string ";-T4616"
    6. Click ok and restart your SQL Server.
  • Microsoft Dynamics NAV Team Blog

    SQL Query to show tables, their indexes and index usage

    • 1 Comments

    The SQL query below shows a list of tables sorted by the largest tables first. For each table, the indexes in the table are shown. For each index it shows when the index was last used. The query is designed to collect various pieces of information in one place, and give an overview to help supporting decisions when doing index tuning.

    The left hand columns show data for the table (No. of receords, data- and index size) to have a view of the impact of having indexes on the table.

    The right hand columns show data for each index, including Updates (costs) and Reads (benefits) and when it was last used sine the last time SQL Server was restarted.

    Further comments:

    • The query only works on SQL Server 2005 and later.
    • The numbers in the query are reset every time SQL Server restarts.
    • The query may take up to a few minutes to run.
    • The query is provided "as is", with no warranties and confers no rights. You assume all risk for your use.

    If you have comments or feedback, please feel free to post them here.

     

    Best regards

    Lars Lohndorf-Larsen (Lohndorf)
    Microsoft Dynamics UK
    Microsoft Customer Service and Support (CSS) EMEA

     

     

    --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] [int] NOT NULL,

    [F_Obj_ID] [int] NOT NULL,

    [F_Schema_Name] [nvarchar] (128) NULL,

    [F_Table_Name] [nvarchar] (128) NOT NULL,

    [F_Row_Count] [int] NULL,

    [F_Reserved] [int] NULL,

    [F_Data] [int] NULL,

    [F_Index_Size] [int] NULL,

    [F_UnUsed] [int] NULL,

    [F_Index_Name] [nvarchar] (128) NOT NULL,

    [F_Index_ID] [int] NOT NULL,

    [F_Column_Name] [nvarchar] (128) NOT NULL,

    [F_User_Updates] [int] NULL,

    [F_User_Reads] [int] NULL,

    [F_Last_Used] [datetime] NULL,

    [F_Index_Type] [nvarchar] (128) NOT NULL,

    [F_Index_Column_ID] [int] 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,

    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)

    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

    [F_Table_Name] TableName,

    [F_Row_Count] No_Of_Records,

    [F_Data] Data_Size,

    [F_Index_Size] Index_Size,

    [F_UnUsed] UnUsed_Space,

    [F_Index_Name] Index_Name,

    [F_User_Updates] Index_Updates,

    [F_User_Reads] Index_Reads,

    [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]

     

     

     

  • Microsoft Dynamics NAV Team Blog

    Failed to execute SQL string when Installing Dynamics NAV 2009 #2

    • 0 Comments

    When reinstalling Dynamics NAV 2009 or if you have attached a Demo Database manually you will probably run into this problem:

    --------------------------
    Microsoft Dynamics NAV 2009 Build 27808
    Error Report
    SQL Server option
    Cronus Sample Database for Microsoft Dynamics NAV 2009
    Fatal error during installation.

    Error -2147467259: failed to execute SQL string, error detail: A database with name Demo Database NAV (6-0) already exists., SQL key: StopIfDatabaseExist SQL string: IF EXISTS (SELECT * FROM [master].[sys].[databases] WHERE name = ‘Demo Database NAV (6-0)') RAISERROR (N'A database with name %s already exists.',20,1,'Demo Database NAV (6-0)') WITH LOG
    --------------------------

    The problem is here that a database already exists with from a previous installation. The problem can be solved by starting SQL Server Management Studio and deleting the database that already exists. After you have deleted the database start the installation program again and select repair. In Microsoft Dynamics NAV 2009 SP1 you will have one new option saying replace database that you can set to override existing database. If you need to save the existing database for some reason simple do a backup of it before deleting it or read my previous post about how to have more than one Demo Database installed at the same time.

  • Microsoft Dynamics NAV Team Blog

    Installing Stored Procedures for Dynamics NAV 2009 Manually

    • 0 Comments

    It’s recommended that you use the installer on the product DVD to install the stored procedures. But if you are running more than one name instance of SQL server on the same machine that approach will not work. Then you need to do it manually:
    1. Copy the xp_ndo.dll file to the location you want store it. The file can be found under “D:\SQLDatabase\PFiles\Microsoft Dynamics NAV\60\Database” on the product DVD.
    2. Run the following SQL command to add the stored procedures.

    USE master
    EXEC sp_addextendedproc xp_ndo_enumusersids, ‘C:\[Location of file]\xp_ndo.dll’
    GO

    GRANT EXECUTE
    ON [xp_ndo_enumusersids]
    TO PUBLIC
    GO

    USE master
    EXEC sp_addextendedproc xp_ndo_enumusergroups, ‘C:\ [Location of file]\xp_ndo.dll’
    GO

    GRANT EXECUTE
    ON [xp_ndo_enumusergroups]
    TO PUBLIC
    GO

  • Microsoft Dynamics NAV Team Blog

    New Statement of Direction Document for Microsoft Dynamics NAV

    • 2 Comments

    We have released a new Statement of Direction document for partners. We know preparation and product roadmap knowledge is a key factor in building a successful business. With the release of the new Statement of Direction we are able to share our vision and plans for the future direction of Microsoft Dynamics NAV. This will bring partners ahead of most competitors, who struggle to provide customers with product roadmaps. Microsoft Dynamics NAV partners can now speak confidently to customers about the future strategy, aspirations and roadmap for the next major releases of Microsoft Dynamics NAV through to 2017, including NAV "7", "8" and "9".

    PartnerSource: https://mbs.microsoft.com/partnersource/marketing/statementofdirection/NavProdStratToolkit.htm

    CustomerSource: https://mbs.microsoft.com/customersource/documentation/whitepapers/navsod.htm

    -Lotte Cordt Ihlemann

  • Microsoft Dynamics NAV Team Blog

    How to use WCF Performance Counters with NAV 2009 Server

    • 1 Comments

    NAV Server (middle tier) utilises Windows Communication Foundations (WCF), which includes a number of performance counters that you can use to monitor NAV Server performance in real-time. This example shows how to monitor "Calls Per Second" for NAV Server to get some idea of how busy it is over a working day / over time. Many other counters are available.

    Enable Counters

    First, enable WFC Performance Counters as described in this article WCF Performance Counters:

    1)  Use Notepad to edit "Microsoft.Dynamics.Nav.Server.exe.config" in the NAV Service folder (default is C:\Program Files\Microsoft Dynamics NAV\60\Service).

     

    2)  Add the section below, marked with Red:

    <?xml version="1.0" encoding="utf-8" ?>
    <configuration>
      <appSettings file="CustomSettings.config" />
      <system.diagnostics>
        <assert assertuienabled="false" />
      </system.diagnostics>

      <system.serviceModel>
          <diagnostics performanceCounters="All" />
      </system.serviceModel>

    </configuration>

     

    3)  Restart NAV Server.

     

    Watch Counters

    Use Windows Performance Monitor to select and view counters from NAV Server:

    1)  Start Windows Performance Monitor (Start -> Run -> Perfmon.exe)

     

    2)  Add a Counter, then from available categories, select ServiceModelService. This will list the NAV Server, so select this. Then add the counter(s) you are interested in - in this example "Calls Per Second":

    Perfmon

     

    I found that in a stand-alone situation, the only counter that really shows anything is "Calls Per Second". But in a multi user system, this could well be very different - please share your experiences here.

     

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

     

    Best regards


    Lars Lohndorf-Larsen (Lohndorf )

    Microsoft Dynamics UK


    Microsoft Customer Service and Support (CSS) EMEA

Page 37 of 48 (717 items) «3536373839»