All posts by Zoran

Visual Studio – no .NET SDKs were found

When trying to change a VS project to implement some new Azure function need by our Business Central implementation of certain feature Visual Studio was unable to detect dotnet SDKs although multiple versions were installed.

So running

dotnet --info

has given me the following output:

So I have tried running Visual Studio repair multiple times. Installed dotnet SDK for that exact version of Visual Studio 2020 CE. Checking PATH environment variables multiple times. Restarting machine multiple times to no avail.

Solution

However, the solution was right into my nose but… failing to see the obvious. I have suspected that it has something to do with both x86 and x64 versions of .NET installed but could not figured it out what is the difference.

Solution was quite easy. Running

where.exe dotnet

will give you the result of order in which paths of dotnet search is executed.

 If you’re on a x64 machine, you want to have C:\Program Files\dotnet listed first.

Now this is the output of the dotnet –info command:

NAV 2013 R2 RunPageLink bug

Bug

If you have standard Page Action that uses RunPageLink on field that is an Option field, and the OptionCaption of that field has entry that uses & sign (“and” sign) the RunPageLink will not filter the underlying table correctly in Filter Group0.

Explanation

The standard example would be Comment Line Table. If you define Page Action to run Comment Line Table, from some page, where RunPageLink uses filter on the field, that is of Option type and the OptionCaption (OptionCaptionML) of the field has & sign (“and” sign):

2014-09-01_1522

then the standard filter will not work:

2014-09-01_1507

After you change the OptionCaption (OptionCaptionML) of the field that is in RunPageLink filter to (note that OptionString still has & sign):2014-09-01_1510

the standard filter works as it is supposed to:

2014-09-01_1513Of course, when you run Page Action any other value of Option field, that doesn’t have & sign in OptionCaption, RunPageLink works properly.

 

Add multiple Excel worksheets to single Excel workbook – Dynamics NAV 2013

In Microsoft Dynamics NAV 2013 Excell Buffer table (370) changed from using Excel Automation variable to Open XML 2.0 DotNet interoperability classes. More information about that can be found on NAV Team blog in article Excel Buffer Using Open XML Instead of Excel Automation.

The one function that is needed, in real life, when exporting data to Excel workbook is to export data to the same Excel workbook but to different worksheets.

Let say you want to export Item and Item Ledger Entry table to single Excel workbook. I haven’t found any function in ExcelBuffer that does that – correct me if I am wrong.

So I decided to write function that enables just that, open single Excel workbook and just adds new worksheet one after another. In Excel Buffer table (370) I have created AddNewSheet function that receives only one parameter (SheetName).

AddNewSheet(SheetName : Text[250])
IF SheetName = '' THEN
  ERROR(Text002);

IF ISNULL(XlWrkBkWriter) THEN BEGIN
  CreateBook(SheetName);
  ActiveSheetName := SheetName; //first sheet activated
END ELSE BEGIN
  WHILE XlWrkBkWriter.HasWorksheet(SheetName) DO BEGIN
    IF NOT FirstIncrement THEN BEGIN
      SheetName := SheetName + '-01';
      FirstIncrement := TRUE;
    END ELSE BEGIN
      SheetName := INCSTR(SheetName);
    END;
  END;
  XlWrkShtWriter := XlWrkBkWriter.AddWorksheet(SheetName);
  FirstIncrement := FALSE;
  //ActiveSheetName := SheetName; //last sheet activated
END;
WriteSheet('',COMPANYNAME,USERID);
DELETEALL;
ClearNewRow;

Additional variable that I have used is FirstIncrement (Boolean) – all other are global variables in Excel Buffer table.

Let me explain few key points in this code. First in line 5 we check if the WorkSheetWriter is initialized. If it is not initialized we call standard CreateBook function and set the ActiveSheetName to that “first” excel sheet.

In line 9 we check if there is sheet in workbook that has the same name as the one we have passed as parameter – which can happen in subsequent calls. If this is true then we append ‘-01’ so we can use INCSTR(SheetName) until we find unused SheetName – so we don’t overwrite data on existing sheet.

Then the main task of this function is executed  in line 17 when we append new worksheet to currently open workbook.

In line 19 I have commented out line of code that sets the last sheet that we call to be initially shown when we open Excel workbook. I really want the first worksheet to be shown but you might want it the other way.

Finally, we WriteSheet and delete all data currently in Excel Buffer.

When the code, that populates data in Excel Buffer for single sheet, is finished we have to call this function to write sheet and flush all the data so it is free for another sheet.

Also, there is a difference that we cannot use standard function CreateBookAndOpenExcel – because it is used for single worksheet case scenario – but we must “close” Excel manually by calling standard functions CloseBook, OpenExcel, GiveUserControl on Excel Buffer.

Test codeunit that simulates usage of this function follows.

OBJECT Codeunit 50000 Test AddNewSheet
{
  OBJECT-PROPERTIES
  {
    Date=27.08.14;
    Time=10:58:00;
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
    OnRun=BEGIN
            CreateItemSheet;
            CreateItemLedgerEntrySheet;
            CreateItemSheet;
            CreateItemSheet;
            CreateItemLedgerEntrySheet;
            CreateItemLedgerEntrySheet;
            CreateItemSheet;
            CreateItemSheet;
            CreateItemLedgerEntrySheet;
            CreateItemLedgerEntrySheet;

            ExcelBuff.CloseBook;
            ExcelBuff.OpenExcel;
            ExcelBuff.GiveUserControl;
          END;

  }
  CODE
  {
    VAR
      Item@1002 : Record 27;
      ItemLedgerEntry@1001 : Record 32;
      ExcelBuff@1000 : TEMPORARY Record 370;

    PROCEDURE CreateItemSheet@1();
    BEGIN
      ExcelBuff.AddColumn(FORMAT(Item.FIELDCAPTION("No.")),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Text);
      ExcelBuff.AddColumn(FORMAT(Item.FIELDCAPTION(Description)),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Text);
      ExcelBuff.AddColumn(FORMAT(Item.FIELDCAPTION("Base Unit of Measure")),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Text);
      ExcelBuff.AddColumn(FORMAT(Item.FIELDCAPTION(Inventory)),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Text);
      ExcelBuff.NewRow;

      Item.RESET;
      IF Item.FINDSET THEN REPEAT
        Item.CALCFIELDS(Inventory);
        ExcelBuff.AddColumn(FORMAT(Item."No."),FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Text);
        ExcelBuff.AddColumn(FORMAT(Item.Description),FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Text);
        ExcelBuff.AddColumn(FORMAT(Item."Base Unit of Measure"),FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Text);
        ExcelBuff.AddColumn(FORMAT(Item.Inventory),FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Text);
        ExcelBuff.NewRow;
      UNTIL Item.NEXT = 0;

      ExcelBuff.AddNewSheet(Item.TABLECAPTION);
    END;

    PROCEDURE CreateItemLedgerEntrySheet@2();
    BEGIN
      ExcelBuff.AddColumn(FORMAT(ItemLedgerEntry.FIELDCAPTION("Item No.")),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Text);
      ExcelBuff.AddColumn(FORMAT(ItemLedgerEntry.FIELDCAPTION("Posting Date")),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Text);
      ExcelBuff.AddColumn(FORMAT(ItemLedgerEntry.FIELDCAPTION("Document No.")),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Text);
      ExcelBuff.AddColumn(FORMAT(ItemLedgerEntry.FIELDCAPTION(Quantity)),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Text);
      ExcelBuff.AddColumn(FORMAT(ItemLedgerEntry.FIELDCAPTION("Remaining Quantity")),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Text);
      ExcelBuff.AddColumn(FORMAT(ItemLedgerEntry.FIELDCAPTION("Entry No.")),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Text);
      ExcelBuff.NewRow;

      ItemLedgerEntry.RESET;
      ItemLedgerEntry.SETCURRENTKEY("Item No.","Posting Date");
      IF ItemLedgerEntry.FINDSET THEN REPEAT
        ExcelBuff.AddColumn(FORMAT(ItemLedgerEntry."Item No."),FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Text);
        ExcelBuff.AddColumn(FORMAT(ItemLedgerEntry."Posting Date"),FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Date);
        ExcelBuff.AddColumn(FORMAT(ItemLedgerEntry."Document No."),FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Text);
        ExcelBuff.AddColumn(FORMAT(ItemLedgerEntry.Quantity),FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Number);
        ExcelBuff.AddColumn(FORMAT(ItemLedgerEntry."Remaining Quantity"),FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Number);
        ExcelBuff.AddColumn(FORMAT(ItemLedgerEntry."Entry No."),FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Number);
        ExcelBuff.NewRow;
      UNTIL ItemLedgerEntry.NEXT = 0;

      ExcelBuff.AddNewSheet(ItemLedgerEntry.TABLECAPTION);
    END;

    BEGIN
    {
      ZORAN - Excel buffer AddNewSheet Test
    }
    END.
  }
}

I hope this helps someone and please feel free to comment.

 

Dynamics NAV “The Object table does not exist.” error

The Problem

One week ago we started experiencing problem with compiling and importing objects into our clients test database. The error which pop-ups is the “The Object table does not exist.”. Aaaah… we had our share of headaches with NAV 2013 R2 architecture and we are sceptic about everything in nowadays.

The problem was even more curious because the same database worked well in our development environment. When we restore the SQL backup on our client’s test environment we cannot import or compile objects.

The Resolution

What I have determined, after SQL tracing, is that we had transfered trigger, in Object table of our development NAV database, along with the SQL backup :).

The trigger is used for SourceSafe database, for Object table on our development databases, so we could roll-back objects that had been changed or deleted and we had to restore previous versions.

The misleading of the displayed error was in the unfortunate coincidece of the name of “Object” tables both in our SourceSafe and NAV databases.

Dynamics NAV NAS server error “Function sequence error” & “Invalid cursor state”

The problem

While trying to setup NAS server for client to do the nightly Job Queue events we were stuck with the problem of NAS failing to do anything after starting up.

In the event log there were three Event ID 20010 Warnings:

The following ODBC error occurred:
Error: [Microsoft][ODBC Driver Manager] Function sequence error
State ID: HY010
The following ODBC error occurred:
Error: [Microsoft][ODBC SQL Server Driver]Invalid cursor state
State ID: 24000
The Application Server for Microsoft Dynamics NAV NAVSRVAPL-SQL could not initialize properly.
The server will attempt to initialize every 30 seconds until this
is successful.

When starting without any jobs in the Ready state NAS would initialize but didn’t have these warnings because it had nothing to do.

The Resolution

After series of investigations we came up with the clue to investigate further. I created the test table that had only two fields: 1. Primary key, 2. Datetime. As the primary key field was setup as Autoincrement we managed to recreate the error we saw with NAS using the same user that was used to start NAS. This was strange because this user had dbo role in SQL server that is required for user when using Autoincrement property – as NAV Development Documentation states. When removing Autoincrement property from the primary key field the error didn’t appear anymore.

We setup simple codeunit, that would increment this simple table, in Job Queue but the problem reappeared again. After “digging deep” in the NAS initialization process in standard NAV codeunits we found out that the two tables that are used in this process also had Autoincrement property set to YES. These are tables 405 Change Log Entry and 474 Job Queue Log Entry.

Because inserting records in these two tables were failing the NAS failed to do anything but to report error. We removed Autoincrement property and added code to populate primary keys of these two tables incrementaly. After that the NAS started normally and was executing jobs in Job Queue.

The Conclusion

We believe that the problem is, still, with the assigned SQL rights for the user that is running NAS, although it had db_owner rights on that database, but we could not investigate further because this was production environment. All the other NAS implemenations were working properly without this “intervention” on the standard Microsoft Dynamics NAV code or tables. But this is the workaround we had to implement to overcome this situation.

Update

After moving client’s database to another SQL server this problem went away (as did the few other). This encourages me to believe that the db_owner rights were messed up somehow on initial SQL server.

Dynamics NAV 2013 RapidStart package import error

RapidStart Services are introduced in Dynamics NAV 2013 as a mean of quickly setting up a new company with predefined data. It has great benefits for NAV implementers when working with new clients or setting up new company in test environment.

While working on localization of NAV 2013 for Serbia we started having problems with importing Rapidstart package files. The error we got, while testing, was “The specified file could not be imported because it is not a valid RapidStart package file.

2013-09-30_1128
Error window

We have found out that the problem was in Codeunit 8619 Config. Pckg. Compression Mgt. The function IsGZip did not return true in our case, although the file was created with the same database for NAV 2013.

The source of the problem was that the Windows Server, on which our NAV Server was installed, had Regional Settings for Language for non-Unicode programs set to “Serbian (Latin, Serbia and Montenegro (Former))”.

So we had two options to resolve the error.

First solution

This was simple as setting the Regional Settings for Language for non-Unicode programs set back to English (United States). After restarting the Windows Server the error was gone. I would recommend this solution wherever this setting does not interfere with other software installed on NAV Server.

Second solution

This solution is “not the best option” but it is focused on changing the IsGZip function in Codeunit 8619 Config. Pckg. Compression Mgt. so it returns TRUE after uploading file to NAV Server. I would not recommend this solution but if there are no other options then…

Device installation. The system cannot find the file specified.

While updating Windows XP on one of my computers I couldn’t get any of the hardware drivers to install or update properly. All tries have ended with error:

An error occurred during the installation of the device.
The system cannot find the file specified.

The solution was to go to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion
and check if the RunOnce registry key exists. For some reason this key was missing – it was renamed to RunOnceEx. After recreating key RunOnce in the above registry tree all the driver installations afterwards were successfull.

 

Building and using MS Project 2010 proxy assembly for WCF PSI Service

To do the task of integrating Microsoft Dynamics NAV 2009 R2 and MS Project Server 2010 I have decided to use WCF interface. WCF interface is provided by PSI (Project Server Interface) which has both ASMX object model and WCF object model implemented.

There are three options for communicating with WCF interface of MS Project Server:

  1. Compiling ProjectServerServices.dll PSI proxy assembly.
  2. Add a PSI proxy source code to the Visual Studio solution.
  3. Add a service reference by using Visual Studio.

I have decided to use the first option and compile ProjectServerServices.dll proxy assembly.

Project 2010 SDK

First you need to download and install Project 2010 SDK from Microsoft Download site (Project 2010 Reference: Software Development Kit).

After you have installed Project 2010 SDK you need to go to the installation folder. In my case that was folder: C:\Program Files (x86)\Microsoft SDKs\Project 2010\Documentation\Intellisense\WCF. Unpack the Source.zip file, found in that folder, so you get the Source subfolder containing C# source files.

Next, you need to start CompileWCFProxyAssembly.cmd to create ProjectServerServices.dll file. The best way to do it is to open Command prompt with administrative privileges (Start->All Programs->Accessories->Command prompt then right click and select Run as administrator), then cd to the C:\Program Files (x86)\Microsoft SDKs\Project 2010\Documentation\Intellisense\WCF and then run CompileWCFProxyAssembly.cmd.

Note: You need to change the path of sn (sn.exe) to the location of Windows SDK in CompileWCFProxyAssembly.cmd. In my case that was C:\Program Files (x86)\Microsoft SDKs\Windows\v8.0A\bin\NETFX 4.0 Tools\x64\sn.exe.

After the script has run you should have ProjectServerServices.dll in this folder.

Visual Studio

In Visual Studio you shoud add reference by clicking right mouse button on Reference folder in your C# project then select Add reference… option, then select Browse on the left hand side of the Reference Manager window and click Browse… button on the lower side of the window. Navigate to C:\Program Files (x86)\Microsoft SDKs\Project 2010\Documentation\Intellisense\WCF\ProjectServerServices.dll file and click Add button. Click OK to close the Reference Manager window.

Now have fun with connecting to the MS Project Server PSI using WCF interface.

Explicit error handling when working with XMLPort

During my development of Dynamics NAV Web Services for client’s integration with with their web shop I have had unforseen behaviour of XMLPorts.

When you use SETTABLEVIEW function of XMLPort to narrow data to export you use some kind of input parameter (or parameters) you can do something like this:

GetShipToAddress(CustID : Code[20];VAR XMLShipToAddress : XMLport "Ship To Address")
IF CustID <> '' THEN BEGIN
 ShipToAddressLoc.RESET;
 ShipToAddressLoc.SETRANGE("Customer No.",CustID);
 XMLShipToAddress.SETTABLEVIEW(ShipToAddressLoc);
END;

But with this approach you risk the unintentional exposure of all data. Because of using XMLPort as VAR there is no filter in XMLPort – unless defined otherwise on Node element in XMLPort used.

When XMLPort is used in function exposed as web service of codeunit it is “automagically” populated with data AFTER the function has finished. Therefore, if no filter is set on XMLPort it is populated with ALL data from that/those tables used in it.

The secure way of doing this is with proper error handling. Something like this:

GetShipToAddress(CustID : Code[20];VAR XMLShipToAddress : XMLport "Ship To Address")
IF CustID <> '' THEN BEGIN
 ShipToAddressLoc.RESET;
 ShipToAddressLoc.SETRANGE("Customer No.",CustID);
 XMLShipToAddress.SETTABLEVIEW(ShipToAddressLoc);
END ELSE BEGIN
 ERROR(Text001);
END;

When inserting data though web services using XMLPort you should just use XMLPort.IMPORT function in you exposed web service function. All other required data checking should be done in OnBeforeInsertRecord trigger of Table element in XMLPort.

ShipToAddressTable - Import::OnBeforeInsertRecord()
IF ("Ship-to Address"."Customer No." = '') THEN
 ERROR(Text001);
IF ("Ship-to Address".Code = '') THEN
 ERROR(Text002);

When calling web service functions from client side, you should be writing your calls within try/catch block of code so you get proper exception/error code.

These are some thing you should have in mind when using XMLPorts in exposed web services to get enhanced security of data.

 

 

Metadata for object of type CodeUnit with id ***** is in a failed state.

While developing Windows Embedded 6.5.3 application for WMS (Warehouse Management System) which relies on Dynamics NAV web services I have encountered rather “familiar” error:

Metadata for object of type CodeUnit with id ****** is in a failed state…. error CS1001: Identifier expected.

OK, I said, I will recompile the object and restart Microsoft Dynamics NAV Business Web Services.

Recompile, restart… same error. What now? Hmm… restart Web Services Windows Service again. Nope!

Went into code and view the last function I have edited. Nothing suspicious. Compile – everything OK. Puf?!

Let’s go to error again (reading it whole this time). Whoa… Error details info:

…Error details: c:\ProgramData\Microsoft\Microsoft Dynamics NAV\60\Server\MicrosoftDynamicsNavServer\source\Codeunit\Codeunit50004.cs(498,163) : error CS1001: Identifier expected

Ok. Let’s open the error details file. It opens in Microsoft Visual Studio Tools for Applications. Went to line 498 and position 163 and I can see the following line of my function (with source in C#) which ends like:

CS1001_identifier_expectedThere is Int32 param in my function without identifier (which the error clearly states)!

Went back to my codeunit in NAV and opened C/AL locals of the function I have edited and I have something to see. There is a parameter WITHOUT NAME of Integer Type.

dynamicsNAV_identifierexpectedSomehow, while editing parameter list I have, inadvertently, created new parameter row, never assigning a name to it. NAV thinks this is OK and allows the codeunit to be compiled, but the NAV Business Web Services doesn’t like this.

Resolution

Resolution was simple. Removed the “unintentional” parameter, recompiled codeunit, restarted NAV Web Service and everything was fine, again. 🙂