Good morning,
Is there a way for me to run ad-hoc SQL Server queries that do not return a row set please? I would like to include administrative processes in my orchestrations.
Nick
HI @NK112421, the only way that I know of to do this in Matillion is by using Python. Matillion doesn't have any components currently that will make calls to SQL Server in this fashion. Checkout using the PyODBC library, it's fairly mature and seems to do a decent job. Here is a good example of it can be used in a situation like you are asking for: https://www.easysoft.com/developer/languages/python/examples/CallSP.html
I hope this helps.
Good morning Bryan,
Thank you so much for the speedy answer. Python was the route I had initially chosen but being new the platform I had hoped that there was a trick that I was missing. I had IT pip install PyODBC yesterday. It is not a bad approach since you end up with the opportunity to parse and raise platform exceptions from the driver.
Thanks for help.
Cheers,
Nick
I faced the same challenge today.
Another solution that worked for me is using the DB Query Component.
e.g.
exec "stored procedure";
SELECT 1 AS TEST;
The select part is to avoid the component returning an error message for not returning any values.
You can improve this as well by using an select that retrieves something useful data like some logs of the stored procedure.
Keep in mind that you can't execute this code using the "sample" feature because the sample will generate a sub select so the code will not be valid.
It is not the cleanest solution but it should work.
I was able to use SQL Script component, and embed the "call Stored Procudure" for residing in snowflake. I believe this will be the same with other databases as well.
I forgot to mention that you will need to install the PyODBC library on your Matillion instance before you can use it in the Matillion software. This is explained in this document: https://documentation.matillion.com/docs/2234735 in the "Addition Modules" section.