Can I implement SQL Server Service Broker in python?

Question

Is it possible to implement a service broker to receive messages from SQL Server in a language like Python, or does it always have to be in some .Net language?

Answer 1

Yes it is possible (but not much fun)

Service Broker is configured and used via T-SQL commands, so if you have a way to talk to SQL Server from python (e.g. using the excellent pyodbc) then you just have to connect and issue the appropriate queries. Figuring out the appropriate T-SQL and getting Service Broker to work for you is the hard part, not the python bit.

import pyodbc

conn = pyodbc.connect(your_connection_string)
conn.autocommit = True
conn.execute("ALTER DATABASE CURRENT SET NEW_BROKER WITH ROLLBACK IMMEDIATE")

# ... etc ...

The rest of the (many hundreds of lines of) SQL is an exercise in understanding Service Broker, for which there is much MSDN documentation, such as the Developer's Guide for SQL Server 2008 R2.

The community tutorials are very useful for figuring out stuff that Microsoft doesn't tell you e.g. these which I found helpful enough to get a proof-of-concept running before I gave it up :)

• http://rusanu.com/2006/04/06/fire-and-forget-good-for-the-military-but-not-for-service-broker-conversations/
• http://www.davewentzel.com/content/service-broker-demystified-fire-and-forget-anti-pattern
• http://www.davewentzel.com/content/service-broker-demystified-default-not-default