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

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 - 

Microsoft Dynamics NAV 2016 - How to Configure Phone Client.

Hi All, In this article we will discuss how we can connect Microsoft Dynamics NAV 2016 with New Client Launched i.e. Phone Client. This Article Contain Steps for a Android Phone as I have Only Android Phone. I am doing it having all tiers on my windows 8 machine, steps remain same for multiple servers but issues might be different. What we Need (Other what we discuss in this article) -  The Service Tier should be on Public IP . Some of the Data-card does not Provide you Public IP. check it for sure.

Download Microsoft Dynamics Business Central on Premises 2020 Wave 2 Release.

 Hi Readers, As Expected Microsoft have released #msdyn365bc 2020 wave 2 is available.  This article provides details of the new release. The 2020 release wave 2 for Business Central delivers a wealth of new features. Read What's new in 2020 Release Wave 2 here .  Dynamics NAV Customers upgrading to Business Central on Prem will be able to run business central on premises, move to cloud or choose a hybrid approach.