How do I create a Python CLR procedure in SQL Server?

Question

We want to call some python from tsql on sql server 2008.

The python script is fully developed and works great. We cannot trigger anything from the client app because it is closed source and cannot be modified.

So we have full access to SQL Server (2005 and 2008), and can see that "trigger" record get inserted... and need to fire our python code when that happens.

Rather than using xp_cmdshell to launch an old fashioned python script, we would like to do this as "sql native" as we can. (xp_cmdshell will work, but is scary at a few levels.)

What is the path to do this?

Answer 1

[EDIT] Per Paul White's comment, IronPython (and anything else that relies on dynamically-generated code) cannot be used inside a SQLCLR object.

Which makes the following statement incorrect. I will leave it here for future visitors.

In order to use Python to program SQL CLR objects, you will need to use IronPython, and manually create .py files containing the proper directives necessary for the .Net runtime to target the required .Net SQL CLR libraries. I have no idea of the precise directives you would need to use in Python.

http://IronPython.net contains the .Net version of Python that can be used for this purpose.

Having said all that, Visual Studio 2012 is geared towards using either C# or VB.Net for creating SQL Server CLR assemblies. In order to do so in a nice, automated way, you need to create a "SQL Server Database" project, then change the default language of the solution to either C# or VB.Net as appropriate. You then click "Project -> Add New Item" and choose "SQL CLR Trigger". This automates the setup of the function declarations etc to "just work" with SQL Server.