Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2002
    Posts
    30

    Unanswered: Capturing event from SQL Server with VB

    Hi, I want to know how to capture event from SQL server?
    for example, I want to alert someone when a branch has lower stock qty than minimum qty.
    The only thing I know is that I can generate it using triggers, but I don't know how to make one, and how to capture it using Visual Basic.

  2. #2
    Join Date
    Jan 2003
    Location
    Dordrecht, The Netherlands
    Posts
    95

    Re: Capturing event from SQL Server with VB

    I don't know of any mechanism to have SQL Server generate events that are transferred to VB.

    I would do the following:
    1. Make a table tblEvents in SQL Server that can hold the event information.

    2. Write triggers for the actions on the tables that should generate events. This trigger should insert a row in tblEvents.

    3. Read out tblEvents in VB on a regular basis, e.g. in a Timer sub, and take action on the records in tblEvents that haven't been handled yet. You'll want to have a column in tblEvents that holds information if the Event has been handled.

    Hope this helps.

    Ad Dieleman.

  3. #3
    Join Date
    Sep 2002
    Posts
    30
    thx

  4. #4
    Join Date
    Jan 2003
    Posts
    14

    Re: Capturing event from SQL Server with VB

    The Idea of storing the Data in a Table is good, but having unnessesary network traffic wouldn't impress me.

    I have another idea:

    1.) Make a new local package (dts), which checks, if new events happened

    2.) Write a vb-script that establishes a WMI-Connection to the Client. There you can update a file. I prefer WMI-Sessions for that, because you can easily create the administrative shares (c$) if deleted by users.

    3.) Append the script to the package, you can get the client-ip from the sessioninfos.

    4.) The Program checks the date of the local file you updated before.

    5.) Schedule the dts that it runs at the intervals you prefer.

    If you need the Information even if the Application doesn't run, SQL-Server can send EMails with SQLMail.

Posting Permissions

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