cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ElDorko
Regular Visitor

Failed to get dynamic schema - can't use Azure SQL Server Stored Procedure

Hi all.

 

I'm stumped on calling a Stored Procedure from an Azure SQL Server. I can call one SP in that same database just fine, but not another one. The SP is seen by the flow, it is listed in the "Execute Stored Procedure (v2)" action drop-down, so the connection should be fine. But when I select that SP, there is an error message "Failed to get dynamic schema" and none of the parameter fields appear.

 

For the other SP, it works just fine. Both are very similar, there is a different number of parameters but neither does anything very strange - this "non working" one more or less just calls an INSERT statement (which I can't do directly from a Power App, so I am trying to do it this route).

working_sp.png error_sp.png

The picture without the error message is the working SP and the other one is... not, of course.

 

Any ideas? I didn't find anything clearly related to this by googling or from these forums, so I am a bit lost..

1 ACCEPTED SOLUTION

Accepted Solutions
ElDorko
Regular Visitor

Thanks - I finally figured it out by trial-and-error. I tried commenting out parameters and fiddle with them back and forth, and it turns out this doesn't work:

 

@amount NUMERIC(10,2) = 0

 

Oddly enough, if I change it to

 

@amount DECIMAL(10,2) = 0

 

...it works! Ho hum. Ok so I am an old guy, "NUMERIC" is kinda built-in to my fingers, but as far as I know the both are equal so... it doesn't make sense to me, really. Plus, a shout out to the department of rubbish error-messages: we don't appreciate your effort, go away. Imagine if the error would have just said "NUMERIC not supported, use DECIMAL instead"...

 

Oh well, onwards!

View solution in original post

3 REPLIES 3
timl
Super User
Super User

Hi @ElDorko 

This sounds really strange. I'm not sure what the cause of this would be. But to diagnose it, could you maybe share the parameter definition of your SP?

Compared to your working SP, are you using any parameters with a different data type? Have you set default values for any of the parameters, or defined output parameters?

Have you granted both SPs with Execute permissions to the same user/group?

Just to rule out the possibility of the SQL in your SP causing the problem, if you comment out the contents of your SP and replace it with a really simple command, for example,   print 'hello', do you still see the same problem?

 

 

ElDorko
Regular Visitor

Thanks - I finally figured it out by trial-and-error. I tried commenting out parameters and fiddle with them back and forth, and it turns out this doesn't work:

 

@amount NUMERIC(10,2) = 0

 

Oddly enough, if I change it to

 

@amount DECIMAL(10,2) = 0

 

...it works! Ho hum. Ok so I am an old guy, "NUMERIC" is kinda built-in to my fingers, but as far as I know the both are equal so... it doesn't make sense to me, really. Plus, a shout out to the department of rubbish error-messages: we don't appreciate your effort, go away. Imagine if the error would have just said "NUMERIC not supported, use DECIMAL instead"...

 

Oh well, onwards!

Thanks for reporting the cause of this @ElDorko 

It's useful to know that the numeric data type doesn't work.

Helpful resources

Announcements
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Top Solution Authors
Top Kudoed Authors
Users online (1,580)