Answered

The data source is read-only error

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hello!

I have a collection that I'm trying to save to an on-premise SQL Server database. I tried both Collect() and Patch() but they both trow the error: "The data source is read-only, so the function Collect can't write to it".

Patch('[dbo].[EquipmentRequests]',Defaults('[dbo].[EquipmentRequests]'), {Job: JobCboBox.Selected.Jobs});

Collect('[dbo].[EquipmentRequests]', EquipmentRequestCollection)

Error:

The SQL database is successfully connected through the gateway (I'm reading data out of a different database on the same server). The database I'm attempting to write to is very simple:

I created a label with the following formula but it shows blank:

First(Errors('[dbo].[EquipmentRequests]')).Message

Any thoughts on what I can change or might be doing wrong?

Categories:

Building Power Apps

All responses (6)

Answers (1)

Sort by

Verified answer

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!

@Anonymous

I would take a look at the account that you are using to connect to the data source. Make sure it is an account that has the access you need to the data base. Also that the permission is properly configured in the database to allow write capabilities.

Also, I recall seeing a log on the gateway machine that may have some particular help in diagnosis, but I don't recall offhand where it was and am not at a point to check on the accuracy of that statement.

I hope this is helpful for you.
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you for the suggesions.

I actually created both databases the app is connecting to so I'm assuming here since I am connected over my personal GW I setup and configured with my account that created the databases in SQL which is the local admin perms should be ok?

I checked the GatewayInfo,Network,Error logs but nothing stood out.
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@Anonymous

I'm still suspect of the account only because I saw most issues involved that when working with it. Your table appears fine, but have you tried another table to see if there are the same problems?

As for the log...I'd have to check on it later today to give you specifics...Unless someone else chimes in with a suggestion for you.
Pstork1 69,661 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

You need to check the account you are using on the Gateway and the connection account inthe SQL connector, unless they are your account. Those accounts will need access to the database as well.
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Just so I'm not missing anything to check.

When I created the connection in PowerApps, I selected the GW (a personal gateway running on my laptop under the default service account) and in the PowerApps UI, I selected Auth Type: Windows, Username: domain\username and my password to connect to my on-premise database. These credentials are successfully reading data out of the databases, and are the same credentials I'm signed into on my local PC which is running the database PowerApps is connecting to. Within SSMS I am the same user domain\username, which is the creator/owner of the databases and the sysadmin of SQL (since it's my local instance).
Community Power Pla... Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

I restarted the GW service on my local machine and it magically started working. Thank you all for your inputs!