Skip to main content

NAV 2015 - Error While Consuming Query Output.

Hi All,

In today article we will discuss how to resolve a issue that I faced during a development.

The Error Occurs when i try to consume the output of a Query in Power BI. The Error can be faced by any application which is trying to Consume the Data From Query.

Below is the Query that I was trying to Consume -

Issue - While Trying to Consume Output of Query, The Navision Service Tier Crashes. 

Here is What I found when I checked Event Viewer -
Server instance: DynamicsNAV80
Type: System.InvalidCastException
Message: Failed to convert parameter value from a SqlDecimal to a String.
Source: System.Data
HResult: -2147467262

Ideas after Reading Error Message - 

1. The Error Says that System is unable to Cast a Decimal Value to a String Value.
>> But I am not converting anything??

2. Is the query output comes as String for All Values in the Result?
>> NO

3. Is my application (in this case Power BI) trying to Convert a Numeric Field to String?
>> NO

Tried Following Steps to resolve issue - 

1. Removed Method Type Totals From Quantity Field & it worked Fine.

2. Tried Creating Same Query in SQL Managment Studio.

The Reason Behind Issue is - In my Query When I am trying to Calculate Sum of Quantity From Job Ledger Entry, the Query Trying to Group It by all Fields (same as SQL).

But my Query have one FlowField (Customized) i.e. Job Task Description in Job Ledger Entry. 

I moved that field to actual table i.e. Job Task and it worked Fine.

Learning / Take Away -
Whenever You Design a Navision Query with Group By Clause, you should not have any flow-field in Group Expression.

Reason (what I can think of)-
As we all know, FlowFields are not stored in SQL and Group By Clause cannot Group data on virtual fields.

Suggestion / Request To Microsoft - 

Please put an error message while trying to Use Method Type with FlowFields, as it happens with all other cases.

I Posted This Issue to Microsoft, If you have same issue Please Vote Here To Resolve it in Future Release.

Hope the article helps you, if you get stuck with same issue.

Saurav Dhyani


Popular posts from this blog

VIEW SERVER STATE permission on SQL Server?

Hi all, Sometime While trying to Login into a database we face an error message as shown below. --------------------------- Microsoft Dynamics NAV Classic --------------------------- You cannot start Microsoft Dynamics NAV Classic because you do not have the VIEW SERVER STATE permission on SQL Server. Contact your system administrator. --------------------------- OK    ---------------------------

BC 21 and Higher - PowerShell Cmdlet (Replacement of Business Central Administration).

Hi Readers, As discussed in last article about deprecating of Business Central Administration, there are few common actions that we use in administration till Business Central 20. For our on-prem customers, we will still require doing activities. As Microsoft suggest we need to start using PowerShell cmdlet.    Let's see how to do those via PowerShell, or Administration Shell. I will be keep adding commands as you comment to this article.

Send Mail with Attachment From Navision.

Hi all, We have seen how to save a report into PDF and how to send mail to a customer. Let's link these two post in one i.e. Mailing statement to a customer into PDF Format. This article is part of the Series. Please Refer  Table of Content here . If you have the old objects set let me brief you what I will be changing -