Answered

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

(0) Share

Report

Posted on by mabl4367

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?

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

v-monli-msft on at

Like (0)

Report

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

Was this reply helpful? Yes No
mabl4367 23 on at

Like (0)

Report

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.

Non working datat...

Data source error...

Powerapp with two...

Was this reply helpful? Yes No
mabl4367 23 on at

Like (0)

Report

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?

Was this reply helpful? Yes No
Verified answer

David Jennaway 716 on at

Like (0)

Report

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

Was this reply helpful? Yes No