Update or Insert event triggers a VB application

Question

Update or Insert event triggers a VB application

Gators79

SSC Enthusiast

Points: 108
More actions
August 12, 2010 at 8:46 am

#222433

I have a table with a column status_flag, if the flag is updated “I" initialize; I will need to execute an ACTIVE X processes. I thought about adding in a form with a 5 second timer to check the table, however I have over 125 servers with the ACTIVE X and the resources might be too much on the Database server. Each machine has its own MSDE and active x. Does anyone know of a viable solution or suggestions that might help? Maybe a .Net program to do this. The DB is SQL 2000.

Viewing 3 posts - 1 through 2 (of 2 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

You should be able to create an update trigger that could run a sql job when your column is updated to "I". The sql job would execute a dts package. The dts package would execute your activex. The challenge is that you would have to create this same job and package on every server, do it all over again when you need to update it. As i recall to do this you would need to create a sql job that executes your dts package. Then you can invoke the job with sp_start_job. Will take some serious tinkering to make it work but should be possible. Good luck!!!

Seems like whatever you have going is way more complicated than it needs to be. I would bet there is a simpler solution to what you are trying to accomplish.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lowell SSC Guru Points: 323518 More actions · Answer 2

i would do this from a single machine; make a .NET app that connects to each of the 125 servers, one at a time (or run multi threads)..which reads the table, does the activex work, and updates the table.

this makes the code base in one place, so you can make a log file and know that servers 46,62 and 91 had no updates, or server111 failed to run,etc.

then when you add server 126, it's fairly easy , maybe using a config file witht he list of servers and their connection info.

if you have the same code copied 125 times, it's very hard to know which ones failed or not, without reviewing 125 machines seperately...or creating something that polls all 125 machines, which puts you right back to the top...much better to have a unified codebase for all steps of the process.

also you might want to start with the original specification to see if there is a better way to do it; what are you doing that you need to check a table every 5 seconds? email?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!