Objective: Write csv file of updated data from sql table only after SQL table update, insert, delete.
I've read from the community that I should not do triggers to execute python. I should not run triggers to write to file from within sql. Also, my IT keeps the environment very very restricted and I can't use XP_cmdshell, or sqlcmd.
I have python in windows. This must be a very common task. Yet, surprisingly, it's hardly mentioned. Perhaps I'm using the wrong search terms. Please no expensive software suggestion. I can't ask the IT to buy it.
How can I execute python only after the sql table updates?
1 Answer 1
Check out Query Notifications. You could leverage this functionality and a SQL Server agent job to monitor for data changes and execute your Python script on change.
Basically, you create a query notification for the data in question and create a stored procedure that will receive the notification, execute the Python script then return to wait for the next notification.
Schedule a SQL Agent job to run on startup and start this procedure and it will run continuously (unless it is stopped or fails, but you should be monitoring for these events in SQL Server anyway) and execute your Python script whenever data changes.
It's not the same as a trigger, as in you still must poll the queue for notifications of data changes, but the Service Broker technology it is built on is very lightweight compared to polling the tables constantly or adding triggers to your tables.