Unanswered: Visual Basic .net code within SQL triggers and Stored Procedures.
Is it possible to make calls to a Visual Basic .net API from within a SQL trigger or stored procedure?
I have a situation when when data changes in a table I need to ensure that the changes are also updated in a seperate notifications database by executing some visual basic code. Is there a way to execute VB code from within a SQL stored Procedure or trigger? Would something like OLE DB allow me to do this?
I'm trying to make calls to the namespace Microsoft.SqlServer.NotificationServices. The sp_OACreate seems to require that the OLE object created support the iDispatch interface, which it appears that the .dll I'm working with doesn't Can I use the namespace as the ProgID? I can't find a CLSID for the notification services object either.
An example of the code that I would like to execute might be the following. I'd like to trigger it to run on each insert operation calling the Microsoft.SqlServer.NotificationServices.Subscribe r constructor and then passing values from the newly inserted record to it for database insertion.
FYI - in the above example the variable prefixed with @@ should actually only use a single '@'s. The value of these variables would be set prior to the execution of the vb code by by referenceing the updated table.
Thanks for the shove in the right direction Strader. After doing a fair bit more diggin I've discovered that sp_OACreate will probably fit the bill perfectly. I'll be writting a C++ .dll which I can call from a trigger. This way I can pass the updated data from SQL to the Notificaton Services system.
I'm actually working with SQL Notification Services which provides the framework for building a notifications application. I can set up event provider to moniter data in the system. When certain conditions are met a notification is generated which formats and delivers a message via either e-mail, voice, SMS, or any HTTP based technology to the subscriber group to which the data was relevent. Lots of neat potential applications of the technology in my oppinion.
It's nice to know that I'll be able to piggyback this on an existing application and not have to start from the ground up.