cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
NicHerg
Level: Power Up

Server error

Hi,

 

For the last couple of months, I have been working on a mobile PowerApps application. Everything was working fine, and we recently moved our app from our dev environment to our production environment.

 

After doing so, I checked, and everything was still operational (this was early this week).

As of this morning, when using the app, I am getting odd error messages which make no sense to me, since nothing has been changed since it was functional.

In this case, I am getting a "An error occurred on the server. Server Response: Not a valid connector Error response" error on several fields in my application. 

 

I have checked several other posts related to this error, and none of the suggested fixes seem to work: none of the experimental features are enabled; I have refreshed the data sources; I have removed and re-added the data source connection (Azure SQL Server) and nothing seems to work.

 

I could really use your help in this situation.

I am joining a screenshot of the issue.

 

Thanks in advance.

 

Note: This issue also occurs on the app in the dev environment. 

2 REPLIES 2
nctdcodes
Level: Powered On

Re: Server error

I've been struggling with a similar issue. I have two views in the same database. I call them, within the same app, in the same manner:

ClearCollect(TripTable, Filter('[dbo].[BusDriverTrips]'
, route_id = Value([@Route].SelectedText.Value)
, incident_date_int = DateDiff(DateValue("1900-01-01")
, [@IncidentDate].SelectedDate, Days)
, start_hour <= [@Hour].Value
, end_hour >= [@Hour].Value))

and

ClearCollect(DriverStops, Filter('[dbo].[BusDriverStops]'
, incident_date_int=Drivers.Selected.incident_date_int
, driver_id=Drivers.Selected.driver_id
, route_id=Drivers.Selected.route_id
, block_id=Drivers.Selected.block_id
, run_id=Drivers.Selected.run_id
, trip_id=Drivers.Selected.trip_id))

The first (arguably more complicated) Filter statement works 100% of the time. The second fails 100% of the time.

 

They both result in queries to the SQL server similar to this:

execute sp_executesql N'select top 500
    [_].[driver_id],
    [_].[vehicle_id],
    [_].[route_id],
    [_].[incident_date_int],
    [_].[block_id],
    [_].[run_id],
    [_].[trip_id],
    [_].[scheduled_start],
    [_].[scheduled_end],
    [_].[actual_start],
    [_].[actual_end],
    [_].[start_hour],
    [_].[end_hour]
from [dbo].[BusDriverTrips] as [_]
where ((((([_].[incident_date_int] = 43653 and [_].[incident_date_int] is not null) and ([_].[driver_id] = 524521 and [_].[driver_id] is not null)) and [_].[route_id] = 301) and [_].[block_id] = 30109) and [_].[run_id] = 301009) and [_].[trip_id] = 1398'

Again, they are in the same database, on the same server and are both views of similar complexity (and return in fractions of a second). The database is an on premesis SQL Server 2017 Standard edition accessed via an Enterprise Gateway. Both queries are recveived and processed successfully by the SQL server, so the problem is either occurring on the gateway (unlikely because PowerBI works flawlessly with the same queries) or in PowerApps.

 

I wish I could help with your issue (and my own), but I've likely been Googling the same posts you have and have found no remedy either. This seriously needs some looking at by Microsoft as one of their services works just fine and the other does not, and the error message generated is basically useless.

nctdcodes
Level: Powered On

Re: Server error

And I sort of solved my problem, and this might fix yours if it is the result of a recent change.

 

Bottom line - PowerApps HATES with infinite loathing, certain field types - date, datetime, and (I suspect) datetime2. It doesn't matter if you are sorting by them or even if you are using them in your app, if they exist in the date you are returning from SQL, PowerApps will provide this ever so enlightning error.

 

I knew from other posts that the PA team, in their infinite wisdom, had decided that sorting and filtering by so uncommon a field as date (I mean, no one ever uses a date field as an index, right?) was anathema to thier vision for the platform, and so I jumped through the contortionist's hoops to represent all dates as an integer number of days since a date (Jan 1, 1900 in my case). I discovered that it was unable to properly display a time value, and so I had to convert it to a string for use in PowerApps. And now it seems that even if you are merely requesting a dataset that includes date or a datetime value, you have to adjust your SQL backend to convert it to a string or the query will fail. And again, not just that value, but because PA by default requests every column of the table, there can be no offending columns in the table at all. Oh, but don't worry, apparently PowerApps supports datetimeoffset just fine, because who doesn't aggregate their daily sales by datetimeoffset?

 

In short, PowerApps needs serious work. These deficiencies are inexcusable in a production product, and in the case of the date and datetime support, have been known to the dev team for years. These arbitrary shortcomings are not clearly mentioned in the documentation, and it works poorly with Microsoft's own database product. 

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

PowerApps Monthly Community Call

Next Wednesday, August 21st at 8am PDT

Top Community Contributors for July 2019

Top Community Contributors for July 2019

Let's thank our top community contributors

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

FifthImage

Dynamics 365 and Power Platform April 2019 Release notes

Features releasing from April 2019 through September 2019!

SixthImage

Power Summit Australia 2019

August 20-23rd 2019

Users Online
Currently online: 44 members 3,914 guests
Please welcome our newest community members: