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.

Regards,
Saurav Dhyani

Comments

Popular posts from this blog

Welcome - Microsoft Dynamics 365!

Hi All, In this article we will discuss the new introduced member of Dynamics Family. Yes you heard it correctly, one more new member with Name "Microsoft Dynamics 365" . #ProjectMadeira   #Dynamics365

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 - 

MSDYN365BC - Data Upgrade To Microsoft Dynamics 365 Business Central on premises.

Hi Readers, We have already talked about the number of steps for upgrading to Business Central on Premises from different NAV versions. After that article, I received multiple requests for an article which list down steps for Data Migration. In this article, we will discuss steps of data migration to MSDYN365BC (on-Prem) from NAV 2017. For this article, I am considering a Cronus Demo Database without any customization. For an actual upgrade project, we will have to complete object merge using compare and Merge process. After the Merge Process, the next step is data migration. Let's discuss those steps. Direct Upgrade to Microsoft Dynamics 365 Business Central (on-Prem) is from following versions - 1. NAV 2015. 2. NAV 2016. 3. NAV 2017. 4. NAV 2018.