Browse by Tags

Tagged Content List
  • Blog Post: How using SQLIndex property can affect which index NAV uses

    The usual rule for specifying a key in NAV is, that it will chose the first key that matches all the fields specified by SETCURRENTKEY, RunformLink, etc. Example: Table 21 "Cust. Ledger Entry" has the following keys (not all of them listed): - Entry No. - Customer No.,Posting Date,Currency Code -...
  • Blog Post: Table Information including Index information (Usage, Blocks and Reads)

    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...
  • Blog Post: Index Usage Information Tool

    Attached at the end of this post is a set of NAV objects that collects index usage information, and displays it in a NAV client so that you can sort tables by no. of Indexes / Index Reads / (Index) Block wait time, etc.: The tool is using these three SQL Queries / DMVs: sys.indexes: Basic...
  • Blog Post: How to read a SQL Profiler trace file into a SQL table

    Just a small tip that I use often when I have a large SQL Profiler trace. Rather than reading through trace files line by line, you can read it into a table and then query it. This syntax creates a new table in SQL Server and loads your trace file into it: SELECT * INTO MyTraceTemp FROM :: fn_trace_gettable...
  • Blog Post: SQL Query to show tables, their indexes and index usage

    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...
  • Blog Post: Beware the SQL Index property on NAV 5.0 SP1

    The discussion of the changes starting with Microsoft Dynamics NAV version 5 regarding the use of Dynamic cursors has already been addressed in the article titled "Cursor Types" on the SE Blog - http://blogs.msdn.com/microsoft_dynamics_nav_sustained_engineering/ However, we are seeing more and more...
  • Blog Post: Basic SQL - Restoring a SQL Server backup

    This post is part of " Overview of NAV-specific SQL features for application consultants ". You can back up your Microsoft Dynamics NAV database either from a NAV client or from SQL Server Management Studio. To restore a backup made from SQL Server, follow these steps: 1) Open SQL Server Management...
  • Blog Post: Basic SQL - Creating users in NAV on SQL

    This post is part of " Overview of NAV-specific SQL features for application consultants ". Before you can set up a user in Microsoft Dynamics NAV on SQL Server, you must create the user on SQL Server first. If you try to create a user which does not exist on SQL Server, you will get this error message...
  • Blog Post: Basic SQL - How to attach a database to SQL Server

    This post is part of " Overview of NAV-specific SQL features for application consultants ". If you receive a SQL Server database file (.MDF), then you must attach this to your SQL Server before you can access it. This is how to do that: 1) Copy the Database file (.MDF), for example “Demo Database...
  • Blog Post: Basic SQL - How to create a new NAV database for restoring a NAV backup

    This post is part of " Overview of NAV-specific SQL features for application consultants ". This post describes the steps needed to create a new NAV database from a NAV backup (.fbk) file. All the steps are done from a NAV client: 1) Start a NAV client (finsql.exe), then go to File -> Database...
  • Blog Post: Basic SQL - Enable trace flag 4616 on SQL Server

    This post is part of " Overview of NAV-specific SQL features for application consultants ". The newest versions of Microsoft Dynamics NAV require trace flag 4616 to be enabled on SQL Server. If not, then you will get this error message when you try to connect: === The trace flag 4616 is not...
  • Blog Post: Basic SQL - Creating Extended Stored Procedure / xp_ndo.dll

    This post is part of " Overview of NAV-specific SQL features for application consultants ". Microsoft Dynamics NAV requires two extended stored procedures from xp_ndo.dll to exist on SQL Server if Windows logins are used. If these extended stored procedures do not exist, you will get this error...
  • Blog Post: Basic SQL - Installing SQL Server 2005 / 2008

    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...
  • Blog Post: Basic SQL - Overview of NAV specific SQL features for application consultants

    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...
  • Blog Post: Error - Cannot create more than one clustered index on table

    This post describes one scenario in which this error occurs. There may be others. In the late 4.0 versions of the NAV executables, all tables in NAV had a clustered index created on SQL for the primary key (Index 0). However, the table objects in 4.0 versions did not have the Clustered property set...
  • Blog Post: List of released updates for SQL Server 2005 SP2

    The link below lists build numbers, KB numbers and links to cumulative updates updates 1 to 9 for SQL Server 2005: List of build numbers and cumulative updates 1-9 for SQL Server 2005 Also see this post for relevance of SQL Updates to Microsoft Dynamics NAV: Overview of updates for SQL Server...
  • Blog Post: What exactly does COUNT count?

    The command COUNT is pretty simple. But on the SQL option there are a number of different ways to count records, and each can give a different result. This is how the different ways of counting works in NAV on SQL: There are two commands you can use: COUNT or COUNTAPPROX. As the name suggests,...
Page 1 of 1 (17 items)