How to shrink a Navision DB Log file in a SQL 2008 Server

alter database "Demo Database NAV (6-0)" set recovery simple
go

checkpoint
go

alter database "Demo Database NAV (6-0)" set recovery full
go


dbcc shrinkfile ("Demo Database NAV (6-0)_Log" , 1)
go

Navision Code Coverage

The Code Coverage tool differs from the debugger tool that is included in Microsoft Navision. However, the Code Coverage tool contains the same limitations as the debugger tool. You require Granule 7110 (Report & Dataport Designer) to run the Code Coverage tool.

Note If you cannot perform the following procedure, contact your local certified developer partner for help.

To determine the objects and the code that are used during a function, such as posting a sales order, follow these steps:

Step 1:Create a sales order, but do not post it.
Step 2:On the Tools menu, click Debugger, and then click Code Coverage.
Step 3:In the Code Coverage window, click Start.
Step 4:Return to the Sales Order window, and then post the sales order that you created in
step 1.

Step 5:Return to the Code Coverage window, and then click Stop.

Step 6:You see a list of all the objects that were used when the sales order was posted.
On the Tools menu, click Object Designer.
Step 7:Click Codeunit, click Codeunit 1 (ApplicationManagement), and then click Code to open the Code Overview window.

Codeunit 1 (ApplicationManagement) is one of the code units that are listed in the Code Coverage window.

The lines of code that have been run appear as black text. The lines of code that have not been run appear as red text.
In the Code Overview window, the lines of code appear just like the lines of code that appear in the debugger tool. For example, the Code Overview window uses the following formatting:
Bullets appear on each line that contains executable code.
The lines that are marked with a bullet are the only lines in which the information that is displayed is correct.
The lines of code that are not marked with a bullet appear in the same colors as the adjacent lines of code.

To clear the lines that are displayed in the Code Coverage window, click Start. To stop the Code Coverage routine, click Stop.

Note When you run the Code Coverage tool, a large log file may be generated. To reduce the size of the log file, perform steps 1 to 6, and then click Start in the Code Coverage window. After the Code Coverage tool starts, perform step 7.

How to use CAST function in the definition of Linked Tables in NAV

Example 1

The original SQL script for the Linked Table

USE [NavisionTEST]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[Cronus$UC_CustomTableName]
AS
SELECT DeviceId,
(SELECT DATEADD(hour,
(SELECT DATEDIFF(hour, GETDATE(), getutcdate()) AS EXPR1), CustomTableName.ScanTime) AS EXPR1) AS ScanTime, DisplayLine1
FROM [2ndDATABASE].pfo.dbo.metric_CustomTableName AS CustomTableName
WHERE (DisplayLine1 LIKE '%toner%') OR
(DisplayLine1 LIKE '%cartridge%') ;



Example 2

The same SQL script used to define the Linked Table, being modified with the CAST function

USE [NavisionTEST]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[Cronus$UC_CustomTableName]
AS
SELECT DeviceId,
(SELECT DATEADD(hour,
(SELECT DATEDIFF(hour, GETDATE(), getutcdate()) AS EXPR1), CustomTableName.ScanTime) AS EXPR1) AS ScanTime,
CAST(DisplayLine1 AS NVARCHAR(250)) AS DisplayLine1
FROM 2ndDATABASE.pfo.dbo.metric_CustomTableName AS CustomTableName
WHERE (DisplayLine1 LIKE '%toner%') OR
(DisplayLine1 LIKE '%cartridge%')

How to set up NAV 2009 SP1 and NAV 2009 R2 in the same server

It is possible to setup both NAV 2009 SP1 and NAV 2009 R2 in a single server. If you have already setup a NAV 2009 SP1 server and you are in need of setting up the NAV 2009 R2 server also in the same server (without uninstalling the NAV 2009 SP1), you could do it by following the below steps.

STEP 1: Do not un-install your existing NAV 2009 SP1 server installation. Instead, copy the following folders from you installation CD to your Program Files folder;

- Classic
- Role Tailored Client

It is obvious that you cannot make the folder names as ‘Classic’ and ‘Role Tailored Client’ as they are already available in the Program Files folder for running NAV 2009 SP1. Therefore, create them as ‘Classic R2’ or ‘Role Tailored Client R2’ as shown here.




STEP 1: Do not un-install your existing NAV 2009 SP1 server installation. Instead, copy the following folders from you installation CD to your Program Files folder;

STEP 2: Now it is time to create a service for NAV 2009 R2 – for the NAV Service Tier Application. You may be able to create a service by using below commands. To run this, you will have to use the command prompt of Windows.

To create a NAV Service;
SC CREATE "MicrosoftDynamicsNAVServer$R2" binpath= "C:\Program Files\Microsoft Dynamics NAV\60\Service R2\Microsoft.Dynamics.Nav.Server.exe" start= auto DisplayName= "Microsoft Dynamics NAV Server R2"

To Delete Service:
SC DELETE "MicrosoftDynamicsNAVServer$R2"

To create a NAV Web Service;
SC CREATE "MicrosoftDynamicsNAVWS$SrvR2" binpath= "C:\Program Files (x86)\Microsoft Dynamics NAV\60\Service R2\Microsoft.Dynamics.Nav.Server.exe $SrvR2" start= auto obj= "NT Authority\NetworkService" DisplayName= "NAV2009R2 Web Services" type= share
netsh http add urlacl url=http://+:7049/DynamicsNAVR2 user="NT Authority\NetworkService"

STEP3: Once the above step is carried out, you may need to manually register some of the new DLL file only available with R2 version. This could be done by using the ‘regasm’ command on your console.
For example, for registering the DLL, Microsoft.Navision.Mail.dll, you can use the command as follows;

C:\windows\microsoft.net\framework\v2.0.50727\regasm /codebase /tlb:Microsoft.Navision.Mail.tlb Microsoft.Navision.Mail.dll
STEP4: Once you see that you can compile all objects in the database, you could start accessing R2 Client. One of the best practices among the options would be creating a separate shortcut for the R2 Client.

The above exercise may be very useful, when your users are in need of testing /checking the new features of the NAV 2009 R2 version. May be you are in a situation where you need users to see the new features of NAV 2009 R2. May be you are in need of users to check the system after upgrading from NAV 2009 SP1 to R2. Regardless of the intention, this will work for you!

How Do I Manually Register Type Libraries, DLLs / ActiveX Controls, and ActiveX Servers?

The following commands may be needed in the case of installing multiple NAV Service Tiers in single Server. For example, when you are in need of installing NAV 2009 SP1 and NAV 2009 R2 in the same server, the second instance needs to be installed and registered in the Windows Registry using below commands

Problem:
How do I manually register type libraries (.tlb), ActiveX controls (.ocx), and ActiveX servers

Solution:

Type Libraries
Type libraries are binary files that contain all type information needed to utilize procedures and classes in a DLL. To register a type library, you can use regtlib.exe as follows:
1. Select Start » Run.
2. Type the following in the dialog box that appears:
C:\WINDOWS\system32\URTTemp\regtlib.exe
Where is the location of your type library file.

ActiveX Controls

ActiveX controls are files with a .ocx extension. These controls come in 16-bit as well as 32-bit forms, with 32-bit being the most predominant. To register an ActiveX control, you can use regsvr32.exe as follows:

1. Select Start » Run.
2. Type the following in the dialog box that appears:

regsvr32 This could also be a OCX file

Where is the location of your ActiveX control.

If you have a 16-bit control, you can run regsvr instead of regsvr32. If your ActiveX control is registered incorrectly, you must first unregister it and then register it again. In both cases, you will get a dialog box prompting you that your control has been unregistered or registered successfully. To unregister a control, use the switch /u (i.e., regsvr32 /u ).

Note: For a complete listing of the switches available, just type regsvr32 from the Start » Run dialog box.

ActiveX Servers

ActiveX servers can be in the form of both DLLs and executables (EXEs). DLLs can be registered similar to an ActiveX control (e.g., regsvr32 C:\Teststand\BIN\TEAPI.DLL). However, when registering an EXE, you must use the switch /RegServer as follows:
1. Select Start » Run.
2. Type the following in the dialog box that appears:
/RegServer

Where is the location of your EXE server (e.g., C:\TestStand\Bin\REngine.exe /RegServer).

In the same manner, to unregister an EXE ActiveX Server use the switch /UnRegServer (e.g., C:\TestStand\Bin\REngine.exe /UnRegServer). RegServer and UnRegServer are switches whose functionality is built into the executable.

Note: If you find that you are dealing extensively with Type Libraries, ActiveX Controls, and ActiveX Servers, you may find the utility linked below useful. It is a very simple utility that allows you to quickly register and unregister Type Libraries, ActiveX Controls, and ActiveX Servers. The application is distributed freely by the Common Controls Replacement Project (CCRP), and the readme file included with the application explains how to use it. Also refer to KB 2XDAB1SJ for more inofrmation about .ocx files.

You may also try below commands in the case of an eror in the above commands for registering required DLL files,

Examples:

c:\Windows\system32>
gacutil/i "C:\Program Files (x86)\Common Files\Microsoft Dynamics NAV\Dynamics Online Payment Client\Microsoft.Dynamics.NAV.DO.ClientProxyWrapper.dll"

C:\windows\microsoft.net\framework\v4.0.30319\regasm /codebase /tlb:Microsoft.Navision.Mail.tlb Microsoft.Navision.Mail.dll

c:\windows\microsoft.net\framework\v2.0.50727\regasm /unregister /tlb:microsoft.navision.mail.tlb microsoft.navision.mail.dll

C:\windows\microsoft.net\framework\v2.0.50727\regasm /codebase /tlb:Microsoft.Navision.Mail.tlb Microsoft.Navision.Mail.dll

Creating SQL Server VIEWs to show the Ledger Entry Table values including Flow fields

I am using two examples to explain the logical background in regard to retrieving values of the Flow Fields.

Example 1 – SQL View for Customer Table

In Customer (No 18 of Dynamics NAV DB) Table, ‘Balance’ and the ‘Balance LCY’ fields have been defined as Flow Field, therefore during the run time it will pull records from the Detail Customer Ledger Entry table (No 379 of Dynamic NAV DB ) to show the ‘Balance’ and ‘Balance LCY’ on the Customer Card. When you create a SQL view for customer table, you will also have to apply the same principle. In other words, you have to join ‘Detail Customer Ledger Entry’ with Customer table to retrieve the corresponding values.

USE [Demo Database NAV (6-0)]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[View_Customer]
AS
SELECT TOP (100) PERCENT dbo.[CRONUS Australia Pty_ Ltd_$Customer].No_, dbo.[CRONUS Australia Pty_ Ltd_$Customer].Name,
SUM(dbo.[CRONUS Australia Pty_ Ltd_$Detailed Cust_ Ledg_ Entry].Amount) AS AMOUNT,
SUM(dbo.[CRONUS Australia Pty_ Ltd_$Detailed Cust_ Ledg_ Entry].[Amount (LCY)]) AS AMOUNT_LCY
FROM dbo.[CRONUS Australia Pty_ Ltd_$Customer] LEFT OUTER JOIN
dbo.[CRONUS Australia Pty_ Ltd_$Detailed Cust_ Ledg_ Entry] ON
dbo.[CRONUS Australia Pty_ Ltd_$Customer].No_ = dbo.[CRONUS Australia Pty_ Ltd_$Detailed Cust_ Ledg_ Entry].[Customer No_]
GROUP BY dbo.[CRONUS Australia Pty_ Ltd_$Customer].No_, dbo.[CRONUS Australia Pty_ Ltd_$Customer].Name
ORDER BY dbo.[CRONUS Australia Pty_ Ltd_$Customer].No_

GO

Please note that I am using Dynamics NAV 2009 SP1 Asutralian & New Zealand version for my examples. Therefore when you test this example, you may be required to replace the compnay name ‘CRONUS Australia Pty_ Ltd’ with ‘CRONUS International Pvt Ltd.’ As appropriately. Further if you’re testing this scripts with a different database file, instead of ‘Demo Database NAV (6-0’, please replace the database name correctly.

 
Example 2 – SQL View for Customer Ledger Entry Table

In the second example, I am creating a SQL view for Customer Ledger Entry Table. Again, you know that ‘Amount’ and ‘Remaining Amount’ are defined as Flow Fields by placing a look up to the Detail Customer Ledger Entry Table. Therefore in the SQL View definition, you would notice that a JOIN command is used to establish the link between the Customer r Ledger Entry (18) & Detail Customer Ledger Entry Table (379). More importantly, JOIN command has been used for two times in order for calculating ‘Amount’ and ‘Remanning Amount’ respectively. In the first case, Detail Customer Leger Entry is joined only based on the Customer Ledger Entry No. field, whereas in the second case Detail Customer Leger Entry is joined based on both Entry No & Entry Type fields.

USE [Demo Database NAV (6-0)]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[View_Cust_LedgerEntry]
AS
SELECT dbo.[CRONUS Australia Pty_ Ltd_$Cust_ Ledger Entry].[Entry No_], dbo.[CRONUS Australia Pty_ Ltd_$Cust_ Ledger Entry].[Customer No_],
dbo.[CRONUS Australia Pty_ Ltd_$Cust_ Ledger Entry].[Posting Date], dbo.[CRONUS Australia Pty_ Ltd_$Cust_ Ledger Entry].[Document Type],
dbo.[CRONUS Australia Pty_ Ltd_$Cust_ Ledger Entry].[Document No_], dbo.[CRONUS Australia Pty_ Ltd_$Cust_ Ledger Entry].Description,
SUM(dbo.[CRONUS Australia Pty_ Ltd_$Detailed Cust_ Ledg_ Entry].Amount) AS Amount, SUM([CRONUS Australia Pty_ Ltd_$Detailed Cust_ Ledg_ Entry_1].Amount)
AS Remaning_Amount


FROM dbo.[CRONUS Australia Pty_ Ltd_$Cust_ Ledger Entry] LEFT OUTER JOIN
dbo.[CRONUS Australia Pty_ Ltd_$Detailed Cust_ Ledg_ Entry] ON
dbo.[CRONUS Australia Pty_ Ltd_$Cust_ Ledger Entry].[Entry No_] = dbo.[CRONUS Australia Pty_ Ltd_$Detailed Cust_ Ledg_ Entry].[Cust_ Ledger Entry No_] AND
dbo.[CRONUS Australia Pty_ Ltd_$Detailed Cust_ Ledg_ Entry].[Entry Type] = 1 LEFT OUTER JOIN
dbo.[CRONUS Australia Pty_ Ltd_$Detailed Cust_ Ledg_ Entry] AS [CRONUS Australia Pty_ Ltd_$Detailed Cust_ Ledg_ Entry_1] ON
dbo.[CRONUS Australia Pty_ Ltd_$Cust_ Ledger Entry].[Entry No_] = [CRONUS Australia Pty_ Ltd_$Detailed Cust_ Ledg_ Entry_1].[Cust_ Ledger Entry No_]


GROUP BY dbo.[CRONUS Australia Pty_ Ltd_$Cust_ Ledger Entry].[Entry No_], dbo.[CRONUS Australia Pty_ Ltd_$Cust_ Ledger Entry].[Customer No_],
dbo.[CRONUS Australia Pty_ Ltd_$Cust_ Ledger Entry].[Posting Date], dbo.[CRONUS Australia Pty_ Ltd_$Cust_ Ledger Entry].[Document Type],
dbo.[CRONUS Australia Pty_ Ltd_$Cust_ Ledger Entry].[Document No_], dbo.[CRONUS Australia Pty_ Ltd_$Cust_ Ledger Entry].Description

GO

When you need to create SQL views for other ledger entry tables, you will have to follow the same principles as explained above. For example, in the case of creating a SQL view for Item or Item Ledger Entry Tables, you will have to link either ‘Item Leger Entry’ or ‘Value Entry’ or both as appropriately depending on the required fields available in the SQL view.

More Training Titles Released for Microsoft Dynamics NAV 2009

Brand new training titles have been released and are available on PartnerSource and CustomerSource (account required). The trainings are available as downloads and as instructor-led training (ILT).

Upgrading Microsoft Dynamics NAV 2009 Native Database to Microsoft SQL Server

This training is designed for individuals preparing to upgrade Microsoft Dynamics NAV 2009 Native database to Microsoft SQL Server. This training covers the upgrade process from preparing existing data, configuring Microsoft SQL Server, troubleshooting data issues, to validating data after upgrade.

PartnerSource: https://mbs.microsoft.com/partnersource/communities/training/trainingmaterials/student/course80298.htm?printpage=false

CustomerSource: https://mbs.microsoft.com/customersource/training/trainingmaterials/student/course80298.htm?printpage=false

Microsoft Learning: http://www.microsoft.com/learning/en/us/Course.aspx?ID=80298A&Locale=en-us

Microsoft SharePoint Technologies and Microsoft Dynamics NAV 2009

This training is designed for individuals who want to integrate Microsoft Dynamics NAV 2009 with Microsoft SharePoint technologies.

PartnerSource: https://mbs.microsoft.com/partnersource/communities/training/trainingmaterials/student/course80297.htm?printpage=false

CustomerSource: https://mbs.microsoft.com/customersource/training/trainingmaterials/student/course80297.htm?printpage=false

Microsoft Learning: http://www.microsoft.com/learning/en/us/Course.aspx?ID=80297A&Locale=en-us

Microsoft Dynamics NAV and SQL Server Database Compression

Microsoft SQL Server 2008 introduces two new compression features (available in Enterprise Edition) that are of interest to users with large databases. Data compression applies to individual tables and indexes within a database and comes in two options: ROW and PAGE. Backup compression is a new option when backing up a database to external storage. Both compression techniques can be used to reduce the amount of online storage required for databases. Data compression can be configured for an entire heap (table with no clustered index), clustered index, non-clustered index, or indexed view. For partitioned tables and indexes, the compression option can be configured separately (and differently) for each partition. Compression can also reduce disk utilization and sql memory utilization as dead is stores on disk in a compressed state and also reads in the SQL cache in a compressed state. Compression can add 10 - 30% percent increased CPU utilization depending on what tables and indexes are compressed and what level of compression is used.

For Dynamics NAV we recommend only compressing tables and indexes that have a read to write ration of 80%/20% (This is a conservative threshold) or higher as compressing tables with a higher write ratio can actually decrease performance. We also recommend using ROW compression if the space saving between ROW and PAGE level compression is less that 10%; if the difference is over 10% then we recommend PAGE compression. This is because if the space savings from PAGE compression is close to or similar to ROW compression then it is not recommended to incur the additional overhead associated with PAGE compression. An example of NAV tables that would benefit greatly from compression are the large "Entry" tables such as G/L Entry, Value Entry, and Item Ledger Entry. An example for NAV tables that would not benefit from compression and where performance may actually decrease due to compression are "Line" tables where the data is temporary in nature such as Sales Line, Purchase Line, and Warehouse Activity Line. SQL Server compression is completely transparent to the Dynamics NAV application.

Compression is done on Table or Index basis. On tables with clustered indexes the clustered index is the table so compressing the clustered index is equal to compressing the table. Running the ALTER TABLE.. WITH COMPRESSION is only necessary on HEAPS for all tables with Clustered Indexes you can use the ALTER INDEX.. WITH COMPRESSION.

How to determine the read/write ratio of an index? Luckily SQL keeps track of this for us and all we need to do is extract this data. SQL Server stores this information in the sys.dm_db_index_operational_stats DMV. Remember DMV's are "recycled" each time the SQL Server service is restarted so if SQL has only been up and running for a day or a week this information will be of minimal use. Ideally you would want the server to be up any running for several weeks and through a month end close to get a true idea of actual data access patterns of your database.

The following query will tell you how long the SQL Server instance has been up and running:

select 'Sql Server Service has been running for about '
+ cast((datediff(hh, create_date, getdate()))/24 as varchar(3)) + ' days and '
+ cast((datediff(hh, create_date, getdate())) % 24 as varchar(2)) + ' hours'
from sys.databases where name = 'tempdb'

The following query will give the approximate read write balance of all the used indexes in the database.

SELECT o.id,
o.name,
x.name,
i.index_id,
x.type_desc,
s.rowcnt,
i.leaf_update_count * 100.0 /
(i.range_scan_count + i.leaf_insert_count
+ i.leaf_delete_count + i.leaf_update_count
+ i.leaf_page_merge_count + i.singleton_lookup_count
) as Writes,
i.range_scan_count * 100.0 /
(i.range_scan_count + i.leaf_insert_count
+ i.leaf_delete_count + i.leaf_update_count
+ i.leaf_page_merge_count + i.singleton_lookup_count
) as Reads,
FROM sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL) i
JOIN sys.sysobjects o ON o.id = i.object_id
JOIN sys.indexes x ON x.object_id = i.object_id AND x.index_id =
i.index_id
JOIN sys.sysindexes s ON s.id = x.object_id and s.indid = x.index_id
WHERE (i.range_scan_count + i.leaf_insert_count
+ i.leaf_delete_count + leaf_update_count
+ i.leaf_page_merge_count + i.singleton_lookup_count) <> 0
AND objectproperty(i.object_id,'IsUserTable') = 1


You can also run a simulation with the sp_estimate_data_compression_savings stored procedure in SQL with will run a sample of the data through the chose compression level and give you the estimate of the space saved by enabling compression.

I strongly recommend reading the following MSDN article before engaging in database compression activities.

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

Note: You can use the following query to check to see if there are currently any compressed indexes in the database.

SELECT o.name Table_Name, p.index_id as Index_ID, i.name as Index_Name,
CASE
WHEN p.data_compression = 1 THEN 'ROW Compression'
WHEN p.data_compression = 2 THEN 'PAGE Compression'
ELSE 'ERROR'
END Compression_Type
FROM sys.partitions p
JOIN sys.objects o ON p.object_id = o.object_id
JOIN sys.sysindexes i ON o.object_id = i.id AND p.index_id = i.indid
AND p.data_compression in (1,2)
ORDER BY o.name, p.data_compression, p.index_id


Source:
http://blogs.msdn.com/b/nav/archive/2011/02/11/microsoft-dynamics-nav-and-sql-server-database-compression.aspx