Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have a nice piece of python that sends some emails and interacts with a cloud system. Works fine. But we have to fire it every few minutes to poll the db. We really need, for business purposes, to have the python script fire in real time, so there is no polling delay. (This serves sales people who are on the phone with customers.)

We really do not want a 1 minute polling loop. Or 30 seconds. We want the record to show in the db and for things to happen right away.

The fast way to make this fly is to have it fire when a specific record type is inserted into a table.

Can we fire a python script from a trigger?

Yes, per Aaron's note below, we know that this is a very bad thingTM, but this table gets very very little use (0-12 inserts a day). Polling the table fails to meet our business need (we need the .py to run immediately -- it does much more than send an email).

We believe a way to meet our business need is to set up the .net version of python on the sql server, and then have tsql call the python script the way it calls C# stuff... but we have no idea how to actually do this! (ergo this question)

Docs/details?

Thank you!

share|improve this question

closed as primarily opinion-based by Paul White, RolandoMySQLDBA, swasheck, Kin, Marian Dec 14 '13 at 11:42

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise.If this question can be reworded to fit the rules in the help center, please edit the question.

4  
"so there is no polling delay." So make it fire every 30 seconds - of more often. –  ypercube Dec 13 '13 at 19:53
4  
I suggest you create a new question that asks, "How do I create a Python CLR procedure in SQL Server?" Stop asking about triggers and e-mail, and assuring us that your way is the right way and that our suggestions are not useful to you. Seeing as you have no idea how to do the thing you want to do the way you want to do it, it seems quite presumptuous to sweep our advice aside and tell us that that is the right way to do it. –  Aaron Bertrand Dec 13 '13 at 20:22
5  
Out of curiosity, why not make whatever client-side-code that is inserting the "specific record type" run the Python code? Triggers are not the place to do this, as Aaron said. –  Max Vernon Dec 13 '13 at 20:31
2  
@AaronBertrand To your suggestion to create a new question: Good call! Done! dba.stackexchange.com/q/54995/6229 –  samsmith Dec 13 '13 at 20:35
3  
Finally, re: "To All commenters: It is fine to say "bad idea because... but if you still want to do it, here is how" It is not OK to diss the question just for fun. It is a legit question, even if a bad approach" ... just sounds like "whaaaa." Why not take heed to the commentary that thinks that this is a terrible idea and that your future self will hate your present self? –  swasheck Dec 13 '13 at 21:45

1 Answer 1

Don't make your user transaction wait for the (hopefully!) successful completion of the Python script. Your entire transaction sits there and waits for this external process to run, try to send mail, etc. I doubt the e-mail really has to go out that instant - especially given you can't control any delays it has as it gets routed to the recipient's inbox anyway. Why not just run the process more frequently, if timing is so important?

Please give this tip a look-through.

If you really, really, really want to do this the wrong way, you can just enable xp_cmdshell and fire away.

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

Now, assuming the user has access to xp_cmdshell and/or the SQL Server service account can see the folder where the python script is stored, you should be able to do this from within your trigger:

EXEC master..xp_cmdshell N'C:\Python27\python.exe C:\source\NotifyAgents.py';

As an aside, you should state in your question that you aware that this is a very bad thingTM, but you are not concerned with that, for whatever reason. I still don't think you're going to get as real time as you expect, even if you do fire this from the trigger. Have you considered database mail instead of python?

share|improve this answer
    
Aaron, All your points are valid, but that is my problem. I want to fire python from a trigger, and I have no problem with the issues. (I can have the trigger on the real table push a value into a "job" table, and a trigger on the "job" table run python...) –  samsmith Dec 13 '13 at 19:46
    
xp_cmdshell is a fair call, but that seems outmoded? Isn't the modern way to run python as a .net animal, and then call it from tsql? (This is what I don't know how to do.... will update the OP) –  samsmith Dec 13 '13 at 19:53
3  
Also, your trigger -> other table -> other trigger idea still suffers from the same problem, only now it's worse. The original transaction still has to wait for all of that cascading activity to complete. –  Aaron Bertrand Dec 13 '13 at 20:08
3  
@samsmith I'm curious about the back-handed stuff here. I'm glad all the help I've tried to offer gets "good call," "valid points," etc., yet I still earned a down-vote from you. Guess what I won't be doing on your follow-up question? –  Aaron Bertrand Dec 13 '13 at 20:49
2  
@samsmith I added the xp_cmdshell bit after your comment (you did not make it clear in your question how adamant you are that you must do this in a trigger - most people don't choose that path, they just don't know any better, are a victim of least resistance, etc). Then you undid your down-vote. Then you down-voted me again when you started complaining about people dissing your question. You just undid your second down-vote now. Please don't try to appease me by re-writing history. –  Aaron Bertrand Dec 13 '13 at 22:15

Not the answer you're looking for? Browse other questions tagged or ask your own question.