Unanswered

Power App Stored procedure Error

Like (2) Share

Report

Posted on 9 Apr 2024 19:21:29 by ImantasCes

Hi folks, I have strange error.
Using stored procedure in Power App.
In develop mode, it works perfectly and retrieving my data from SQL.
But, when I run(play) app and try load data from sql, immediately got error:

DSA.dboprocradpakugamyba failed: { "error": { "code": 500, "message": "Null object cannot be converted to a value type.\r\n at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)\r\n at Newtonsoft.Json.Linq.Extensions.Convert[T,U](T token)", "source": "europe002-002.azure-apihub.net", "path": "choose[3]\\when[1]\\choose\\when[2]", "policyId": "", "clientRequestId": "5d876b9a-94eb-49b4-929c-b8c2c7c6fb8b" } }

Authoring version: 3.24034.17
With other Authoring versions, same result.
Any tips? help?

Categories:

Connectors

Connecting to Data

All responses (6)

Answers (0)

markinwaukesha 58 on 24 Jul 2024 at 18:33:35

Like (0)

Report
Power App Stored procedure Error

I did a blog post for the PowerApps Community not too long ago -

Here: https://community.powerplatform.com/blogs/post/?postid=af8c870f-d52b-4c92-964a-fd56bebd4ce4

I am using MicroSoft SQL Server and this is just a "hello world" app - I re-ran it and it was fine. I did have to change the

"Try/Catch" and move the Check for Results from the Try to inside the block - This may be a Scoped variable issue however - so now my block is something like the snippet below

This may not solve your issue - so a couple things that I do when I run into something is:

* I put together a hello world and break down things to just doing a stored proc call with param - so I can isolate it

* One thing I notice is that sometimes it's user permissions - so if I run it in dev mode - I'm using windows Auth with my id and I have greater permissions - but in published mode it's running differently - this was mostly how the connector was setup so I tore that out and re-did it

* Last resort and good to have in place -- - I have log levels in my powerApp and I write out to a Logging Table --

The log levels are important otherwise your app may be too chatty - then you can at least log verbosely and fine it eventually -- this isn't ideal but I've done it many a time.

Good Luck

END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage ROLLBACK TRANSACTION SELECT @Error=@@ERROR, @Rows=@@ROWCOUNT IF @Rows!=1 OR @Error!=0 BEGIN SET @ErrorMsg='ERROR 20, ' + ISNULL(OBJECT_NAME(@@PROCID), 'unknown') + ' - unable to ???????? the ????.' IF @@TRANCOUNT >0 BEGIN ROLLBACK END SET @LogInfo=ISNULL(@LogInfo,'')+'; '+ISNULL(@ErrorMsg,'')+', Error='+@Error+', Rows='+@Rows -- INSERT INTO MyLogTable (...,Message) VALUES (....,@LogInfo) -- RETURN 20 END END CATCH END
mmbr1606 13,193 Super User 2025 Season 1 on 24 Jul 2024 at 12:58:36

Like (0)

Report

Power App Stored procedure Error

hey

maybe this helps:

SQL Stored Procedure Supported In Power Apps (youtube.com)

If the Post help you solve your issue, please mark it as verified answer and give it a like so we can help other in the community with similar issues

Greetings
VibhaHegde89 17 on 24 Jul 2024 at 12:23:33

Like (0)

Report

Power App Stored procedure Error

I have the same issue. Have you found a solution?
Commonman 6 on 06 May 2024 at 19:20:12

Like (0)

Report

Re: Power App Stored procedure Error

Same issue for me as well error 500 while accessing Stored procedure from SQLserver through PowerApps canvas apps when published.. In design mode in the browser it works as well
The preview mode feature is getting that error oly on publish but from automate it works fine as well as from dseigner mode in canvas apps as mentioned above
redaero 2 on 06 May 2024 at 10:08:35

Like (0)

Report

Re: Power App Stored procedure Error

Same here: works fine in preview, 500 error on published, for every user. The strange thing is that it worked last week, and I changed nothing but the stored procedure itself, but not its parameters or inputs, just its output.

Also, a fun, related bug: renaming the SQL Server connection from the default causes calls to it to error out with a 404 error, and what's more, the 404 error remains even if the connection is re-renamed to its original name. The only way to fix it is to remove and recreate the connection.
Ctypo 17 on 19 Apr 2024 at 12:30:48

Like (0)

Report

Re: Power App Stored procedure Error

I'm also having this issue. Pulling data from sql server works okay but get the error 500 when using stored procedure. Tried using stored procedure directly and by creating a collection.....same issue....error 500.

Works in the preview when developing which is frustrating.

I'm using the 1 month's free trial.

I'm half suspecting that it because I'm not passing any parameters...

hold on.....I've just tried with parameters and it works so then tried without and now it works.

Is this an intermittent issue?