cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

The data source is read-only error

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:

1.png

 

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:

2.png

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?

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User III
Super User III

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

6 REPLIES 6
RandyHayes
Super User III
Super User III

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

Anonymous
Not applicable

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.

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
Pstork1
Dual Super User III
Dual Super User III

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.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Anonymous
Not applicable

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).

 

 

Anonymous
Not applicable

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

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,121)