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
User Groups Public Preview

Join us for our User Group Public Preview!

Power Apps User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

Power Apps Community Call

Monthly Power Apps Community Call

Did you miss the call?? Check out the Power Apps Community Call here!

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Top Solution Authors
Top Kudoed Authors
Users online (72,923)