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

[ElDorko]

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

 

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

[timl]

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 · Accepted solution]

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!

[timl]

Thanks for reporting the cause of this @ElDorko 

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