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.
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.
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.