Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2003
    Posts
    41

    Question 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?

    Thanks, Matt

  2. #2
    Join Date
    Feb 2003
    Posts
    15
    sp_OACreate allows you to call .dll's. Check books online.

    Yukon (next version of SQL Server) is supposed to allow creation of .net code with stored procedures and triggers, but that won't help you right now.

  3. #3
    Join Date
    Feb 2003
    Posts
    41
    Thanks Strader,


    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.




    ---------------CODE SNIPPET---------------------

    Dim myInstance, mySubscriber

    const instanceName = "MyInstanceName"
    const subscriberId = "TestSubscriber"

    'Create the NSInstance object.
    set myInstance = WScript.CreateObject("Microsoft.SqlServer.Notifica tionServices.NSInstance")
    myInstance.Initialize instanceName

    set mySubscriber = WScript.CreateObject("Microsoft.SqlServer.Notifica tionServices.Subscriber")
    mySubscriber.Initialize myInstance

    'Set the properties that describe the subscriber record.
    mySubscriber.SubscriberId = @@subscriberId
    mySubscriber.SusbscriberType = @@subscriberType
    mySubscriber.Enabled = @@true

    'Add the subscriber record to the database.
    mySubscriber.Add


    ------------------------------------------------------------

  4. #4
    Join Date
    Feb 2003
    Posts
    41
    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.

  5. #5
    Join Date
    Feb 2003
    Posts
    15
    Can you create a VB .dll that contains the code snippet you included, which would be callable from sp_OACreate?

    It would serve as a sort of a "wrapper" to the notification services you're trying to call.

    Is what you're doing related to the sp_add_notification and related sp's at all?

  6. #6
    Join Date
    Feb 2003
    Posts
    41

    Talking

    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.

    Thanks, Matt

  7. #7
    Join Date
    Sep 2003
    Posts
    10

    Visual Basic .net code within SQL triggers and Stored Procedures

    Hi

    i am using Win 2K OS.
    i am not using .NET OS nor am i using .NET API

    i would like to call an existing VB application from within a SQL Server trigger.

    Would it be possible to call the VB Application (.exe) using sp_OACreate inside the trigger and if so, how ?
    Would we perforce have to call a .dll ?

    i will be grateful for an answer to this query.

    Regards
    Yadavendra

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •