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 Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (2,072)