cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mabl4367
Frequent Visitor

On prem SQL data source not returning any data from one table but working fine for other tables tables in the same db.

I have created a gateway and added a connection to an on prem SQL database (CA Service desk manager, mdb) to it.
I then created a new power app and connected to the database.

Reading the "dbo.pri" or the "dbo.act_log" works fine but when i try to get data from the "dbo.ca_contact" table in the same database I only get the column names an no data. There is a lot of data in that table. I have verified that using sql server management studio.

Sometimes i get an error message "s is not a function" and a session id.

Does anyone know why I cant get data from this table and is there a way to get around it?

1 ACCEPTED SOLUTION

Accepted Solutions

The normal way around this would be to create a view that excludes the columns, and I can't think of any alternative (unless you can create a separate database that you do have permissions to create objects in, and create the view there that references the table). So, I think you'd have to ask the database administrator to create the view for you

View solution in original post

4 REPLIES 4
v-monli-msft
Community Support
Community Support

Hi @mabl4367 ,

 

What is the formula that you use the show the data? Is your table connected to PowerApps successfully? Check from View > Data Source.

 

Regards,

Mona

Community Support Team _ Mona Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Hi Mona @v-monli-msft ,

 

I use a data tabel control to display the data from the data source so the formula i use in the "Items" property is simply '[dbo].[ca_contact]'. I've also tried to limit the amount of data by using FirstN('[dbo].[ca_contact]';10) but I still get no data at all from the ca_contact table.

Other tables from the same data source work fine like the act_log table.

It seams there is a problem with the data source because when I hover over it I can see the error message "s is not a function".

I have attached some screen shots to this post.

 

Since the tables are in the same database the only thing I can think of, that could be the cause of my problem, is that the ca_contact table has some column with an unsupported datatype while the act_log table has not.

I have googled some more and it seams that the sql connector does not support the data types of some of the columns in the table ca_contact.

https://docs.microsoft.com/en-us/connectors/sql/

ca_contact has multiple columns of type binary(16) that is not supported by the connector.

I may not add views or stored procedures to the database so I can't create a view that excludes or transforms the columns with these datatypes.

Any tips on where to go from here?

The normal way around this would be to create a view that excludes the columns, and I can't think of any alternative (unless you can create a separate database that you do have permissions to create objects in, and create the view there that references the table). So, I think you'd have to ask the database administrator to create the view for you

View solution in original post

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Top Kudoed Authors
Users online (86,163)