Executing a VB app from a insert trigger

Question

Post reply

Executing a VB app from a insert trigger

scotttr

SSC Eights!

Points: 965
More actions
November 22, 2002 at 2:30 pm

#158693

How do I go about executing a VB Application from a insert trigger

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Mkumari Hall of Fame Points: 3498 More actions · Answer 1

To the extent I know about SQL server we cannot execute the VB app. inside a trigger.

scotttr SSC Eights! Points: 965 More actions · Answer 2

Here's a different angle, can you invoke any kind of executable in a trigger

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 3

You can use xp_cmdshell to run an exe or use the sp_oa.. stored procedures to interact with a com dll.

However doing this in a trigger is not advised. It will really hinder performance. you are better to populate a table and scan that table for inserts

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

scotttr SSC Eights! Points: 965 More actions · Answer 4

I need to have the ability to kick off my application when and only when a new record is inserted. Scheduling the application to kick off at intervals is not an option. How can I do a row level scan without using triggers

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 5

You can use triggers but the insert that caused the trigger to fire will have to wait for your app to finish before the insert will complete.

If in your trigger you insert a row in another table and then you have a scheduled job that runs and checks every 10s if a record has been inserted into the other table

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

mhempenstall SSC Veteran Points: 276 More actions · Answer 6

Scott,

I'm shortly to be working on a project which needs to do just what you have descibed. My idea at the moment is to create a DLL which is called by the stored procedure (using sp_OA stuff). The DLL then in turn does some minimum processing, starts a VB app then ends - releasing the INSERT trigger. The VB app on the SQL server can then continue for as long as required.

scotttr SSC Eights! Points: 965 More actions · Answer 7

Hey mhemp,

Interesting angle. Two questions:

1) If I create a insert trigger that calls a .bat or.exe that in turn calls up the VB app, will that have the same effect of releasing the trigger as soon as the .exe calls up the VB app.

2) Could you define what you mean by "minimum processing"

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 8

It won't work. You would have to create a new thread under which the application runs.

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

scotttr SSC Eights! Points: 965 More actions · Answer 9

Simon,

How would I get the VB application to operate under a new thread?

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 10

Don't know, anything like that gets messy with VB. I would definitely prefer the other mechanism of inserting into a second table and polling that table

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

csraocvs SSC Journeyman Points: 94 More actions · Answer 11

On UNIX, you can execute a program in the background ( & at the end OR nohup ). This means the trigger ( xp_cmdshell ) gets a signal that the command execution is completed.

You have to find out a similar way to execute your BAT or EXE file in the background on WINDOWS so that the trigger gets released immediately.

I hope you will find it out and post back the result.

-- Chandra

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 12

How about triggering a scheduled job to run which can do the work and allows the trigger to return. Up scaling maybe a problem if trigger is slow!

Andy Warren SSC Guru Points: 119902 More actions · Answer 13

Starting the job is a good way. Lets you do async execution. Whether you start the job directly or put a row in a table and have the job run every 1 min to check, both work. Side note - this is a great way to let users run things indirectly for which they dont normally have access.

Andy

http://www.sqlservercentral.com/columnists/awarren/

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

the.troff Newbie Points: 3 More actions · Answer 14

quote:
Could use a VB DLL, inserting a class that has a pulic procedure.
Call the procedure and kick of an asynchronous task from a timer
in VB and return to the procedure and exit.
The focus should be returned to SQL Server immediately and the VB task is fired by the timer function in the VB bas module. I have done this successfully in VB over COM and DCOM in the past.