Using SMO from within SQLCLR
SMO is a great feature of SQL Server 2005. It is a rich
API for managing SQL Server and as with its predecssor DMO the SQL Server tools
are built on top of it.
As SMO is a .Net managed library many people want to reference SMO in a SQL
CLR function or stored procedure. However SMO is one library that cannot be
hosted within SQL Server (See Bob's post for more details http://www.sqlskills.com/blogs/bobb/2007/03/13/TwoThingsYouCantDoInSQLCLR.aspx)
If you want to use SMO functionality there is a way you can achieve this from
within TSQL and thats with the use of Service Broker.
Service Broker allows us to submit a request to a service in TSQL and waits
for a response, an external service is then able to recieve the request, process
it and reply with the response. The TSQL receives the response and can do what
it needs to do with it. Whilst Service Broker is an asynchronous process
you can make an end to end solution that is synchronous by having the calling
process wait for a response.
The beauty of this is that your external service can be anything from a VB6
application to a .Net application, it just needs to be able to read a message
from a queue and send a message back. Both of which are just TSQL
statements.
I will shortly be posting a demo of this in action.
-