Dedicated pool to azure sql database

Question

If executing workload in dedicated pool, through stored procedure for example, is it possible to log to azure sql database?

Considering single row inserts in dedicated pool are relatively slow. Also, if all logging needs to be available in azure sql database.

Thanks for any help,
Chris

Answer 1

Hello @Chris Bracchi
If you plan to build a logging framework outside the dedicated pool, the best way to handle it in orchestration tools like synapse pipeline/ADF. It is not possible to fire statements to Azure SQL DB inside dedicated pool.

For your scenario, one of the workaround you can consider here is to use “Lookup” activity to invoke store-procedure and include a “select” statement(remember to add alias) at the end of the store-procedure returning row-count and other values needed to log. And you can use @activity(<LookupActivityName>).output.firstRow.<select-field> to retrieve it for storing to SQL DB.
Below is the screen shot of a quick prototype:

Please do let me know how it goes .
Thanks
Himanshu

-------------------------------------------------------------------------------------------------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators