System.IO.IOException - The file exists --- Dyamics NAV

Hello Experts,

I have face many time below error and it is very hard to find solution first time.

Now i have solution for below issue so though to share with you. 

Error details in event log:

==============================================================

Server instance: DynamisNAV90

Tenant ID: <ii>default</ii>

<ii>Session type: Background

Session ID: 18694

User:

Type: System.IO.IOException

Message:

  <ii>The file exists.

  </ii>

StackTrace:

     at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)

     at System.IO.Path.InternalGetTempFileName(Boolean checkHost)

     at Microsoft.Dynamics.Nav.Runtime.NavReport.GetSaveAsRenderer(String fileName, ReportFormat format, String& tempFilePath)

     at Microsoft.Dynamics.Nav.Runtime.NavReport.SaveAs(DataError errorLevel, Int32 reportId, String fileName, NavRecord record, ReportFormat format)

     at Microsoft.Dynamics.Nav.BusinessApplication.Record77.SaveReportAsHTML_Scope.OnRun()

     at Filter.InvokeWithFilter(NavMethodScope )

     at Microsoft.Dynamics.Nav.Runtime.NavMethodScope.Run()

     at Microsoft.Dynamics.Nav.BusinessApplication.Record77.SaveReportAsHTML(Int32 reportID, NavVariant recordVariant, NavCode layoutCode)

     at Microsoft.Dynamics.Nav.BusinessApplication.Record77.GetEmailBody_Scope.OnRun()

     at Filter.InvokeWithFilter(NavMethodScope )

     at Microsoft.Dynamics.Nav.Runtime.NavMethodScope.Run()

     at Microsoft.Dynamics.Nav.BusinessApplication.Record77.GetEmailBody(ByRef`1 serverEmailBodyFilePath, Int32 reportUsage, NavVariant recordVariant, NavCode custNo, ByRef`1 custEmailAddress, NavCode docNo_iRec)

     at Microsoft.Dynamics.Nav.BusinessApplication.Record77.OnInvoke(Int32 memberId, Object[] args)

     at Microsoft.Dynamics.Nav.BusinessApplication.Record77.SendEmailToCustDirectly_Scope.OnRun()

     at Filter.InvokeWithFilter(NavMethodScope )

     at Microsoft.Dynamics.Nav.Runtime.NavMethodScope.Run()

     at Microsoft.Dynamics.Nav.BusinessApplication.Record77.SendEmailToCustDirectly(Int32 reportUsage, NavVariant recordVariant, NavCode docNo, NavText docName, Boolean showDialog, NavCode custNo)

     at Microsoft.Dynamics.Nav.BusinessApplication.Record77.SendEmailInBackground_Scope.OnRun()

     at Filter.InvokeWithFilter(NavMethodScope )

     at Microsoft.Dynamics.Nav.Runtime.NavMethodScope.Run()

     at Microsoft.Dynamics.Nav.BusinessApplication.Record77.SendEmailInBackground(INavRecordHandle jobQueueEntry)

     at Microsoft.Dynamics.Nav.BusinessApplication.Record77.OnInvoke(Int32 memberId, Object[] args)

     at Microsoft.Dynamics.Nav.BusinessApplication.Codeunit260.OnRun_Scope.OnRun()

     at Filter.InvokeWithFilter(NavMethodScope )

     at Microsoft.Dynamics.Nav.Runtime.NavMethodScope.Run()

     at Microsoft.Dynamics.Nav.BusinessApplication.Codeunit260.OnRun(INavRecordHandle εrec)

     at Microsoft.Dynamics.Nav.Runtime.NavCodeunit.DoRun(DataError errorLevel, NavRecord record)

     at Microsoft.Dynamics.Nav.Runtime.NavCodeunit.RunCodeunit(DataError errorLevel, Int32 objectId, NavRecord record)

     at Microsoft.Dynamics.Nav.BusinessApplication.Codeunit449.OnRun_Scope.OnRun()

     at Filter.InvokeWithFilter(NavMethodScope )

     at Microsoft.Dynamics.Nav.Runtime.NavMethodScope.Run()

     at Microsoft.Dynamics.Nav.BusinessApplication.Codeunit449.OnRun(INavRecordHandle εrec)

     at Microsoft.Dynamics.Nav.Runtime.NavCodeunit.DoRun(DataError errorLevel, NavRecord record)

     at Microsoft.Dynamics.Nav.Runtime.NavCodeunit.RunCodeunit(DataError errorLevel, Int32 objectId, NavRecord record)

     at Microsoft.Dynamics.Nav.BusinessApplication.Codeunit448.HandleRequest_Scope.OnRun()

     at Filter.InvokeWithFilter(NavMethodScope )

     at Microsoft.Dynamics.Nav.Runtime.NavMethodScope.Run()

     at Microsoft.Dynamics.Nav.BusinessApplication.Codeunit448.HandleRequest(INavRecordHandle jobQueueEntry)

     at Microsoft.Dynamics.Nav.BusinessApplication.Codeunit448.OnRun_Scope.OnRun()

     at Filter.InvokeWithFilter(NavMethodScope )

     at Microsoft.Dynamics.Nav.Runtime.NavMethodScope.Run()

     at Microsoft.Dynamics.Nav.BusinessApplication.Codeunit448.OnRun(INavRecordHandle εrec)

     at Microsoft.Dynamics.Nav.Runtime.NavCodeunit.DoRun(DataError errorLevel, NavRecord record)

     at Microsoft.Dynamics.Nav.Runtime.NavCodeunit.RunCodeunit(DataError errorLevel, Int32 objectId, NavRecord record)

     at Microsoft.Dynamics.Nav.Runtime.NavTaskScheduler.TaskRunInfo.RunCodeunit(Int32 codeunitId, NavRecordRef recordRef)

     at Microsoft.Dynamics.Nav.Runtime.NavTaskScheduler.TaskRunInfo.InternalRun()

Source: mscorlib

HResult: -2147024816

</ii>

=============================================================

This error will come at time of system use the TEMP folder for saving any intermedicate result file.

Like - While Generate word layout report, QR Code Generate, Create Zip File, etc. case

Use will face the error on screen like below.

===========================================

Solution:

Delete files from TEMP folder for below users account:

1) Delete temp file in User Machine

2) Delete temp file from NAV Service Acccount User in NAV Application Server.

You can open the TEMP Folder in user machine by click on RUN and type %temp%

Select All and make sure you delete all .tmp files

Done....

Restart NAV in User Machine and try to do the process.

It will solve the issue.

I hope it will help someone in future.

Thank you for reading

Keep Sharing...Keep Growing....

Optimize the SQL Database Table Performation by Rebuild Query - Dynamics NAV SQL Optimization

 Hello Experts,

Run the below query on SQL DB for optimization

Take the full backup of database before run this query

================================================

DECLARE @sql VARCHAR(MAX), @i INT, @init INT, @table VARCHAR(50), @indextype VARCHAR(50), @indexname VARCHAR(500), @fillfactor INT

--DROP TABLE ##t

IF OBJECT_ID('tempdb..##t') IS NOT NULL

      DROP TABLE ##t

 

SELECT

ROW_NUMBER() OVER(ORDER BY i.fill_factor DESC) as ID

, DB_NAME() AS Database_Name

, sc.name AS Schema_Name

, o.name AS Table_Name

, o.type_desc

, i.name AS Index_Name

, i.type_desc AS Index_Type

, i.fill_factor

, 0 AS [Status]

, CAST('' AS VARCHAR(1000)) AS ErrorMEssage

INTO ##t

FROM sys.indexes i

INNER JOIN sys.objects o ON i.object_id = o.object_id

INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id

WHERE i.name IS NOT NULL

AND o.type = 'U'

ORDER BY i.fill_factor DESC, o.name

SELECT @i = MAX(ID), @init = 1 FROM ##t 

WHILE @i >= @init

BEGIN

SELECT @table = Table_Name, @indexname = Index_Name, @indextype = Index_Type, @fillfactor = fill_factor FROM ##t WHERE ID = @init

IF @indextype = 'CLUSTERED' AND @fillfactor NOT IN (0,100)

SET @sql = 'ALTER INDEX ['+@indexname+'] ON [dbo].['+@table+'] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100)'

ELSE IF @indextype = 'NONCLUSTERED' AND @fillfactor NOT IN (0)

SET @sql = 'ALTER INDEX ['+@indexname+'] ON [dbo].['+@table+'] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)'

BEGIN TRY

PRINT 'Starting index rebuild of number ' + CAST(@init AS VARCHAR(10)) + ' out of ' + CAST(@i AS VARCHAR(10)) + ' Table Name = ' + @table

PRINT @sql

EXEC (@sql)

UPDATE ##t SET [Status] = 1 WHERE ID = @init

END TRY

BEGIN CATCH

UPDATE ##t SET ErrorMEssage = ERROR_MESSAGE() WHERE ID = @init

END CATCH

SET @init += 1

END

=====================================================

This query will take some time depend on size of database

I hope it will help someone.

Keep Sharing...Keep Growing...

====================================================

Perform following Steps to Reduce Table Lock: (Dynamics NAV)

1. Run above query to rebuild table index (run in night time – we can setup this query to run at every weekend)
2. Increase the table lock time waiting period (default it is 10 second – we can changes to 30 second) From Database à Alter à Advance à Timeout Duration
3. Check our custom code – if it is running in long loop for some standard  calculation than commit in code to release table lock
4. Verify that there is no CONFIRM command used in- between long running code- it is cause the major issue in table lock
5. We need to identify the Table Name – for that user getting table lock issue (We can run SQL Query  EXEC sp_who2  identify the running sql command and check the query executing cause the table lock)
6. Re-transfer the full SQL Database to fresh DB – It will reduce database size to 40% and increase performance (this is optional steps we can do if relay require)
7. Check our long executive code and add command SetCurrentKey to optimize it

Download Multiple PDF Files as Compress Zip File in Business Central SaaS Version

Hello Expert,

The goal of this pattern is to enable the users to download multiple files as a zip file instead of downloading one by one.  On the Web Client this is preferred way of delivering multiple files since it is one of the web patterns and we cannot use File Management code unit to place files silently on the machine.

To Enable download of multiple files of report at result single zip archive file we can following sample code.

report 65001 "Download Zip File Sample"

{

    UsageCategory = Administration;

    ApplicationArea = All;

    ProcessingOnly = true;

    Caption = 'Download Zip File';

    trigger OnPreReport()

    begin

        //Init Zip Archive

        Clear(DataCompression);

        DataCompression.CreateZipArchive();

        //Create  PDF File 1 and add in Archive >>

        SIH_lRec.RESET;

        SIH_lRec.SetCurrentKey("Sell-to Customer No.");

        SIH_lRec.SetFilter("No.", '103086');

        Clear(ReportRunTempBlob);

        Clear(Rpt_Out);

        Clear(Rpt_Instr);

        ReportRunTempBlob.CreateOutStream(Rpt_Out);

        SIH_RecRef.GetTable(SIH_lRec);

        REPORT.SaveAs(1306, '', REPORTFORMAT::Pdf, Rpt_Out, SIH_RecRef);

        ReportRunTempBlob.CREATEINSTREAM(Rpt_Instr);

        DataCompression.AddEntry(Rpt_Instr, 'test 1.pdf');

        //Create  PDF File 2 and add in Archive >>

        SIH_lRec.RESET;

        SIH_lRec.SetCurrentKey("Sell-to Customer No.");

        SIH_lRec.SetFilter("No.", '103087');

        Clear(ReportRunTempBlob);

        Clear(Rpt_Out);

        Clear(Rpt_Instr);

        ReportRunTempBlob.CreateOutStream(Rpt_Out);

        SIH_RecRef.GetTable(SIH_lRec);

        REPORT.SaveAs(1306, '', REPORTFORMAT::Pdf, Rpt_Out, SIH_RecRef);

        ReportRunTempBlob.CREATEINSTREAM(Rpt_Instr);

        DataCompression.AddEntry(Rpt_Instr, 'test 2.pdf');

        //Create  PDF File 3 and add in Archive >>

        SIH_lRec.RESET;

        SIH_lRec.SetCurrentKey("Sell-to Customer No.");

        SIH_lRec.SetFilter("No.", '103088');

        Clear(ReportRunTempBlob);

        Clear(Rpt_Out);

        Clear(Rpt_Instr);

        ReportRunTempBlob.CreateOutStream(Rpt_Out);

        SIH_RecRef.GetTable(SIH_lRec);

        REPORT.SaveAs(1306, '', REPORTFORMAT::Pdf, Rpt_Out, SIH_RecRef);

        ReportRunTempBlob.CREATEINSTREAM(Rpt_Instr);

        DataCompression.AddEntry(Rpt_Instr, 'test 3.pdf');

        //Save Zip Archive and Convert in InStream Data

        ZipTempBlob.CreateOutStream(ZipFileOutStream);

        DataCompression.SaveZipArchive(ZipFileOutStream);

        ZipTempBlob.CreateInStream(ZipFileInStream);

        DataCompression.CloseZipArchive();

        ZipFileName := 'Test.Zip';

        DOWNLOADFROMSTREAM(ZipFileInStream, '', '', '', ZipFileName);

    end;

 var

        DataCompression: Codeunit "Data Compression";

        ReportRunTempBlob: Codeunit "Temp Blob";

        Rpt_Out: OutStream;

        Rpt_Instr: InStream;

        SIH_lRec: Record "Sales Invoice Header";

        SIH_RecRef: RecordRef;

        ZipTempBlob: Codeunit "Temp Blob";

        ZipFileOutStream: OutStream;

        ZipFileInStream: InStream;

        ZipFileName: Text;

}

The above code create 3 pdf files of invoice and at the result we get the single zip file.

I hope it will help someone in future.

Thank you reading this blog.

Keep Sharing....Keep Growing...

Bulk Export Item Pictures in Business Central SaaS version as Zip File

Hello Experts,

Use the below sample code to bulk export items pictures at a tims from item master.

Make sure you have imported the picture in your Item list or for testing purpose just import the pictures for some items and then check

you can use the below code as it is for your test environment.

    Procedure DownloadItemImage()

    var

        MyItem: Record Item;

        TenantMedia: Record "Tenant Media";

        datacompresion: Codeunit 425;

        blobStorage: Codeunit "Temp Blob";

        PicInStream: InStream;

        ZipInStream: InStream;

        ZipOutStream: OutStream;

        ZipFileName: Text;

        ItemCnt: Integer;

        Index: Integer;

        PicCount: Integer;

    begin

        ZipFileName := 'Picture.zip';

        datacompresion.CreateZipArchive();

        MyItem.Reset();

        MyItem.FindSet();

        repeat

            if MyItem.Picture.Count > 0 then begin

                ItemCnt := ItemCnt + 1;

                for Index := 1 to MyItem.Picture.Count do begin

                    PicCount := PicCount + 1;

                    if TenantMedia.Get(MyItem.Picture.Item(Index)) then begin

                        TenantMedia.calcfields(Content);

                        if TenantMedia.Content.HasValue then begin

                            TenantMedia.Content.CreateInStream(PicInstream);

                            datacompresion.AddEntry(PicInStream, MyItem."No." + '.png');

                        end;

                    end;

                end;

            end;

        until MyItem.Next() = 0;

        Message('Items processed ' + Format(ItemCnt) + ' Pictures processed ' + Format(PicCount));

        blobStorage.CreateOutStream(ZipOutStream);

        datacompresion.SaveZipArchive(ZipOutStream);

        datacompresion.CloseZipArchive();

        blobStorage.CreateInStream(ZipInStream);

        DownloadFromStream(ZipInStream, 'Download zip file', '', '', ZipFileName);

    end;

This code simple create the steam of image media and add in zip archive

at last we download the full image file zip at once using DownloadFromSteam command

I hope it will help someone in future.

Thank you reading this blog.

Keep Sharing....Keep Growing...