Executing a VB app from a insert trigger

Question

Executing a VB app from a insert trigger

Viewing 6 posts - 16 through 20 (of 20 total)

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

Antares686 SSC Guru Points: 125444 More actions · Answer 1

I would first like to know what the VB app may be doinhg. My concern is what if you fire multiple times. Will this cause error, data integrity problems or some other issue? A bat file with VB should start the app and release as it has no cmdline interface and thus command prompts do no stay attached to the processand thus does not lock anything else. xp_cmdshell works exactly the same way.

well0549 SSCrazy Points: 2259 More actions · Answer 2

Just create a Job that performs the desired task. Use xp_startjob to execute it. It is the easiest way of doing things......

Only what will happen with two subsequent Jobs ? I mean when the other is still running..........

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

Antares686,

The VB app scans a table that stores requests for data for recently inserted records, then generates flat files on the fly, then FTPs the flat files to a directory on the server whose location is determined on the fly by the value that is in the "userlocation" field of the record. Normally I would use DTS for something like this but I have not been able to crack that nut yet. So at this point I'am leaning toward creating a insert trigger that calls a .BAT file that in turn executes the VB app. That way the trigger should be released.

tcartwright SSCertifiable Points: 5573 More actions · Answer 4

I do something similar to what you are attempting to do. I am using "Loosely Coupled Events" in COM+ with MSMQ Queued Components. This allows me to call a VB com dll that then returns execution to the sql, and then the COM+ Queued Component finishes my task. This is a very extensive topic, and too lengthy for me to explain here, so here are some links :

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsmart01/html/sa01a14.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dncomser/html/complus_events.asp

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q246825&

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q256096&

Tim C.

//Will write code for food

Tim C //Will code for food

BobAtDBS SSCarpal Tunnel Points: 4590 More actions · Answer 5

Perhaps what we're doing will work for you as well. We have an ActiveX EXE VB program that runs full time on a server. It's job is to monitor for new rows in a table and take several actions on that data, very similar to what you described with handling the data, then doing external (non-SQL) tasks.

We simply call a public function in this external app using the SQL sp_OA series of Stored Procedures. The value returned by the function is only used to verify that the app is listening, and the return takes almost no time at all, so the trigger that started the whole chain (from an inserted row just like what you want) is allowed to complete very quickly.

From the external app's point of view, when SQL calls that public function, it's a signal to do a check for new rows now and then do the external processing as it gets to it.

Just add a class to your external EXE program. Set Instancing to MultiUse and Persistable to Not Persistable and DataBindingBehavior to None (all defaults I think). Add a Public Function that returns a 1 any caller to indicate that the EXE is listening. When that function is called make the EXE do whatever it was designed to do.

The only problem I had setting this up was getting the permissions right in DcomCnfg. Our SQL Server has it's own account and I kept getting permission denied when I first started testing the SP's in QA.

Student of SQL and Golf, Master of Neither

tcartwright SSCertifiable Points: 5573 More actions · Answer 6

There are several benefits to my solution that I posted earlier :

1) COM+ Transactions

2) Asynchronous - allows execution to return back to the calling stored proc IMMEDIATELY when queued

3) Multiple subscribers to the published event

4) Uses existing technology, (Not a HACK, like a timer, or an ActiveX Exe, This is not a dig at the users that suggested these solutions, but the solutions themselves, and the VB limitations that caused their vile conception)

Drawbacks :

1) All parameters must / should be ByVal (non-pointer)

2) All methods must be Subs (void return)

3) Passing objects references as parameters is frowned upon, can be done, but nasty

Technologies used : SQL, COM+, MSMQ, VB6

If anyone has any questions upon this subject please let me know by posting back to this thread and I will answer them.

Tim C.

//Will write code for food

Tim C //Will code for food