Invoke VB program from SQL server

[adwarak]

Hi,

I want to trigger off a VB program when a particular
row value in a table exceeds some threshold limit.

Say the table is MQ_XYZ_LOG and the row is called
MQ_Timeout. If any row with MQ_Timeout touches
50, I need to fire a VB program. This VB program
will block traffic.

Can this done be from SQL server?
Please suggest

 

[TomKane]

Maybe you can use sp_cmdshell to trigger your program

 

[adwarak]

How do i use this sp_cmdshell?

 

[ClaireHCM]

should be xp_cmdshell 'statement'

whatever stated after xp_cmdshell are exactly the same as you execute command in Command prompt!

BTW,check books on line for xp_cmdshell

 

[tlbroadbent]

As suggested, you can execute the VB program using xp_cmdshell. However, xp_cmdshell may be disabled or not executable by the user that causes the threshold limit to be exceeded. For security reasons, most users should not have execute permissions for xp_cmdshell.

I recommend the following.

1) Create a SQL Agent Job that executes the VB program. The VB program should not display any screens or prompt for user input. Assume the job is named "Block Traffic."

2) Add an alert that starts the Job and optionally sends an Email or Windows (Net Send) message.

USE master
EXEC sp_addmessage 50050, 10, N'MQ_Timeout limit exceeded'

Use msdb
Go
EXEC sp_add_alert
@name = 'MQ_Timeout limit exceeded',
@message_id = 50050,
@severity = 0,
@notification_message = 'The MQ_Timeout limit has been execeeded!',
@job_name = 'Block Traffic'

3) Create a trigger on the table that raises the alert.

Use YourDatabase
Go
Create Trigger trg_Upd_Ins On MQ_XYZ_LOG
For Update, Insert
As

If Exists (Select * From inserted Where MQ_Timeout >= 50)
RAISERROR (50050, 10, 1) With Log

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm