Saturday, January 2, 2016

Visual elements are displayed incorrectly on Windows Server 2008/2012 R2

Hello Friends,
In NAV 2013 R2 , if you run the Windows Client via a Terminal Server or Remote Desktop Connection (RDC) or some common 3rd party application host technology, reports may be difficult to read when you print them. Preview looks fine.

A report could look like this in preview (OK): 
















But in Print Layout rendering or when printing to paper it would look like this - notice it looks like the fonts have been scrunched and it's hard to read:














Solution:


Detail: Visual elements are displayed incorrectly when you connect to a computer that is running Windows 7 or Windows Server 2008 R2 by using the Remote Desktop Protocol


Database Conversion Issue to NAV 2013 - (Invalid object name 'Session')

Hello Friends,

On last week i am working on upgrade project from NAV 2009 R2 to NAV 2013 R2 (IN Version).
It is heavy customize database there are lots of store procedure created in database to get the data using sql query.

I have got the error in database conversion from NAV 2009 R2 to NAV 2013
Error Detail: Invalid object name 'session'





















Database is corrupted after this error. You will not able to open this database either using NAV 09 R2 or NAV 2013

Error Reason:
·         There are some non-compiled store procedure is created in SQL database. It cause the error in database conversion.
























Solution:
·         There are 2 possible solution are available

1)      Restore the NAV 09 R2 data backup (*.bak) file using SQL and Take the backup from NAV in fbk file and restore it is using NAV.
Note:  After backup and restore the database from NAV. It will remove all the store procedure and other modification done on SQL database. It will solve the issue of non-compile SP

2)      Delete all the non-compiled store procedure from NAV 2009 R2 database and try again. (if it is possible than delete all the customize SP. You can add it later after database upgrade)


I hope it will help you in future.

Keep sharing.....keep growing !!!!!

Thank you


Friday, January 1, 2016

New DataType FILTERPAGEBUILDER in NAV 2016

Hello Friends,

There is a new datatype introduced in NAV 2016 and it is called FilterPageBuilder, this datatype can be used to create a filter page that enables users to set filters on multiple tables. The page can have multiple controls i.e. multiple records and you can use that to capture filters on the records.
The following are the functions that are available for this datatype. 
  • ADDTABLE
  • ADDRECORD
  • ADDRECORDREF
  • ADDFIELD
  • GETVIEW
  • SETVIEW
  • RUNMODAL
  • COUNT
  • NAME


Following is an example how it can be used
FilterPageBuilder ---- SubType of FILTERPAGEBUILDER
FilterPageBuilder.ADDTABLE(‘Customer Table’,DATABASE::Customer);
FilterPageBuilder.ADDTABLE(‘Item Table’,DATABASE::Item);
FilterPageBuilder.RUNMODAL;



The above code will open the following page





















You can also specify the field like we do using ReqFilterFields property on the request page of the report using ADDFIELD function below is another example
Customer is the record variable of Customer Table 
FilterPageBuilder.ADDRECORD('Customer Table',Customer);
FilterPageBuilder.ADDFIELD('Customer Table',Customer."No.");
FilterPageBuilder.RUNMODAL;

As you can see the first parameter should match with ADDRECORD to let the control know which field to show.


















Once the user sets the filter on the page, we can get the filters from the page using GETVIEW function of the FilterPageBuilder and then set that to another record.
Ex.
Customer2.SETVIEW(FilterPageBuilder.GETVIEW(Customer));
Where Customer2 is another customer record variable.

Go-Live Tool (Trunk Table data using SQL) - Dynamics NAV

Hello Friends,

On last week i am working on upgrade come (re-implementation project).
It is has size around 120GB. Upgrading from NAV 2009 R2 to NAV 2013 R2

We have decided that we will only carry forward the master data and open source document like (sales, purchase, service, production, etc) and rest of other transaction and ledger entry table data will delete.

G/L Entry table has more than 2 Crore entries and rest of other traction table have more than 50 lakes entries.

To delete the data from transaction table i have create a tool by using the SQL query to trunk the table data instead of delete query.

I have got the success. It is trunk the G/L entry table in just 2 second. Data deletion task is just completed in 2-3 minutes.

So, I would like to share the SQL Store Procedure and NAV objects which has call that store procedure for one by one table to trunk table data.

Following 3 object create for NAV 2009 R2:
Type
ID
Name
Remarks
Table
50090
Table Delete List
Contain the list of table to trunk data
Report
90505
Delete Table Data
Call the SP to trunk data for one by one table
Dataport
90501
Delete Table Import
To import the data in Table 50090

Note: Disable and Enable the table secondary keys after data trunked from tables.

***************** Store Procedure *******************

/****** Object:  StoredProcedure [dbo].[usp_TruncateTable]    Script Date: 12/31/2015 11:24:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_TruncateTable]
(
@flg VARCHAR(10) --BACKUP DELETE RESTORE
,@tableName NVARCHAR(250) -- Table name
,@excludedColumn NVARCHAR(250) = 'timestamp' -- Works with flg = RESTORE
,@flgBackup BIT = 1 -- Works with flg = DELETE
)
AS
BEGIN
/*
EXEC usp_TruncateTable
@flg = 'RESTORE'  --'DELETE','BACKUP'
,@tableName = 'CRONUS India Ltd_$Activity Step'
,@excludedColumn = 'timestamp' --default
,@flgBackup = 1 --default --0 = Shift + Delete

SELECT * FROM [CRONUS India Ltd_$Activity Step]

*/
SET NOCOUNT ON

DECLARE @sql NVARCHAR(MAX)
DECLARE @i INT
DECLARE @colnames VARCHAR(MAX)

IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = @tableName)
BEGIN
PRINT 'No tables found with name ' +  QUOTENAME(@tableName)
SELECT name as [RelatedTables] FROM sys.tables WHERE name LIKE '%' + @tableName + '%'
RETURN
END

IF @flg = 'DELETE'
BEGIN
IF OBJECT_ID('tempdb..#object_backup') IS NOT NULL DROP TABLE #object_backup
CREATE TABLE #object_backup (id INT IDENTITY(1,1), objectname NVARCHAR(128), objtype nvarchar(100), objectsql VARCHAR(MAX), created_dt DATETIME DEFAULT (GETDATE()), hostid NVARCHAR(100) DEFAULT(HOST_NAME()))

DECLARE @tDependnts TABLE (ID INT IDENTITY(1,1), objetcname nvarchar(250), objtype nvarchar(100))


--Taking backup of the table to tempdb
IF @flgBackup = 1
BEGIN
PRINT '=======Table backup started========'
SET @sql = '
IF OBJECT_ID(''tempdb..'+QUOTENAME(@tableName + '_' + REPLACE(CONVERT(VARCHAR(20), CAST(GETDATE() AS DATE), 121), '-', ''))+''') IS NOT NULL DROP TABLE tempdb..' +QUOTENAME(@tableName + '_' + REPLACE(CONVERT(VARCHAR(20), CAST(GETDATE() AS DATE), 121), '-', '')) + '
SELECT * INTO tempdb..'+QUOTENAME(@tableName + '_' + REPLACE(CONVERT(VARCHAR(20), CAST(GETDATE() AS DATE), 121), '-', '')) +' FROM ' + QUOTENAME(@tableName)
PRINT @sql
EXEC sp_executesql @sql
PRINT 'Processed Record:' + CAST(@@rowcount AS VARCHAR(10))
PRINT '=======Table backup completed========'
END
--Finding dependencies

INSERT INTO @tDependnts
(
    objetcname,
    objtype
)
EXEC sp_depends @tableName

--Backing up dependencies
INSERT INTO #object_backup (objectname, objectsql, objtype, created_dt, hostid)
SELECT c.objetcname, [definition], objtype, GETDATE(), HOST_NAME() FROM @tDependnts c
INNER JOIN sys.sql_modules a ON 'dbo.' + OBJECT_NAME(a.object_id) = objetcname
INNER JOIN sys.objects b ON a.[object_id] = b.[object_id]
WHERE is_ms_shipped = 0
PRINT 'Total dependent objects:' + CAST(@@rowcount AS VARCHAR(10))

SELECT @i = COUNT(1) FROM #object_backup

--Deleting dependencies schema binding views
PRINT '=======Table dependent delete started========'
WHILE @i >= 1
BEGIN
SELECT @sql = 'DROP VIEW ' + QUOTENAME(REPLACE(ob.objectname,'dbo.','')) FROM #object_backup ob WHERE ob.id = @i
AND objtype LIKE '%view%'
print @sql
EXEC (@sql)

SET @sql = ''
SET @i -= 1
END
PRINT '=======Table dependent delete completed========'

--truncating the original table
PRINT '=======Table truncate started========'
SET @sql = N'TRUNCATE TABLE ' + QUOTENAME(@tableName)
PRINT @sql
EXEC (@sql)
PRINT '=======Table truncate completed========'

PRINT '=======Table dependent creation started========'
SELECT @i = COUNT(1) FROM #object_backup
--Creating back the dependencies
WHILE @i >= 1
BEGIN
SELECT @sql = ob.objectsql FROM #object_backup ob WHERE ob.id = @i
AND objtype LIKE '%view%'

EXEC (@sql)

SET @sql = ''
SET @i -= 1
END
PRINT '=======Table dependent creation completed========'
END
ELSE IF @flg = 'RESTORE'
BEGIN
SET @colnames = STUFF((SELECT ',' + QUOTENAME(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @tableName AND COLUMN_NAME <> @excludedColumn FOR XML PATH('')), 1, 1, '')

PRINT '=======Restoring table data started========'
SET @sql = '
IF OBJECT_ID(''tempdb..'+QUOTENAME(@tableName + '_' + REPLACE(CONVERT(VARCHAR(20), CAST(GETDATE() AS DATE), 121), '-', ''))+''') IS NULL
BEGIN
PRINT ''NO BACKUP FOUND FOR SPECIFIC TABLE''
RETURN
END
INSERT INTO ' + QUOTENAME(@tableName) + '(' + @colnames + ')
SELECT '+@colnames+' FROM tempdb..' + QUOTENAME(@tableName + '_' + REPLACE(CONVERT(VARCHAR(20), CAST(GETDATE() AS DATE), 121), '-', ''))
--PRINT @sql
EXEC (@sql)
PRINT 'Record Inserted:' + CAST(@@rowcount AS VARCHAR(10))

PRINT '=======Restoring table data completed========'
END
ELSE IF @flg = 'BACKUP'
BEGIN
PRINT '=======Table backup started========'
SET @sql = '
IF OBJECT_ID(''tempdb..'+QUOTENAME(@tableName + '_' + REPLACE(CONVERT(VARCHAR(20), CAST(GETDATE() AS DATE), 121), '-', ''))+''') IS NOT NULL DROP TABLE tempdb..' +QUOTENAME(@tableName + '_' + REPLACE(CONVERT(VARCHAR(20), CAST(GETDATE() AS DATE), 121), '-', '')) + '
SELECT * INTO tempdb..'+QUOTENAME(@tableName + '_' + REPLACE(CONVERT(VARCHAR(20), CAST(GETDATE() AS DATE), 121), '-', '')) +' FROM ' + QUOTENAME(@tableName)
PRINT @sql
EXEC sp_executesql @sql
PRINT 'Processed Record:' + CAST(@@rowcount AS VARCHAR(10))
PRINT '=======Table backup completed========'
--Finding dependencies
END
ELSE
PRINT 'INVALID ARGUMENT PASSED. Valid arguments are BACKUP or DELETE or RESTORE'
END

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

Download the objects and store procedure from following link: