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
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
Post a Comment