How to call many times the same Snowflake stored procedure in only one script ?

RSchammass

Hi guys,

I created a stored procedure in Snowflake and a script that call this stored procedure four times. I already tested it in Snowflake and it works; however, when I try with Flyway, it shows me an error:

'' Stored procedure execution error: Scoped transaction started in stored procedure is incomplete and it was rolled back. "

So how to fix it ? Thanks in advance

Jon_Kirkwood

Hi @RSchammass

 

Thank you for reaching out on the Redgate forums regarding your Flyway/Snowflake error.

 

I have investigated, and it appears this error is being passed through from Snowflake directly and isn't generated by Flyway.

Their article pages do have a possible cause for this error and solution.

Can you please review if this help article is of use in your situation: 

https://community.snowflake.com/s/article/Stored-procedure-execution-error-Scoped-transaction-started-in-stored-procedure-is-incomplete-and-it-was-rolled-back

 

If you do have the required COMMIT statement/s in your code, are you able to share a copy of the file that is failing in Flyway so we can look to replicate and see if there is something that Flyway may be doing that is leading to the error being generated by Snowflake.

 

I have created a secure file link for you to provide any scripts against. This link will be valid for 14 days.

https://files.red-gate.com/requests/Lzp6CAv6ypAYtYLjCxv7CX

 

RSchammass

Thanks guys. I solved the issue. Have a good one.

shalena62

To execute multiple calls to the same Snowflake stored procedure within a single script, utilize loops or iterative constructs available in your scripting language. This approach optimizes efficiency and streamlines code, ensuring seamless execution of the procedure across various datasets or scenarios without redundancy.