How to call a DLL on a workstation in response to a record insertion on a dbase sitting on a server

Question

How to call a DLL on a workstation in response to a record insertion on a dbase sitting on a server

geoff-541707

Newbie

Points: 9
More actions
November 15, 2007 at 10:17 am

#92750

Hi
I think the title says it all! I need to call a DLL I have written in VB.NET registered on a workstation in response to a record insertion into a table in a SQL Server 2000 database sitting on a separate but LAN connected server
All help gratefully received
Geoff

Viewing 6 posts - 1 through 5 (of 5 total)

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

Dave-3000 SSCertifiable Points: 6186 More actions · Answer 1

How are you inserting the row into the table? If you're using a stored procedure I can't think of how you would trigger execution of the VB.NET code on a separate device. Even on the same device you're stuck with sp_oacreate, which if you search about you'll find most folks abhor. If you're using embedded SQL can you have whatever client runs the SQL trigger the DLL? What about modifying the VB.NET code to listen for a change in the database? I've found on SSC that if you describe the business need you're trying to satisfy and the data structures you're working with, it's pretty often you'll hear from someone somewhere in the world who has encountered and solved the same or a similar problem.

There is no "i" in team, but idiot has two.

Stewart Joslyn SSCertifiable Points: 6131 More actions · Answer 2

What latency can you afford? One solution is to poll the database at a suitable frequency from the external device. You only need to look for a flag to tell you whether there is anything that needs doing so the overhead can be small.

geoff-541707 Newbie Points: 9 More actions · Answer 3

Thanks for that, Dave.

It is an external company's software that is putting the data into the SQL table so I don't have any control over that. I thought there would be a way with something like sp_OACreate but I wasn't sure how it would work - possibly, send the workstation name to a VB routine sitting on the server that then called the appropriate DLL on the appropriate workstation.... all a bit messy

What I've decided to do instead is just put a 1 second timer in my VB and each workstation check the the SQL table for new data with their own workstation name as a marker.

Cheers

geoff-541707 Newbie Points: 9 More actions · Answer 4

Hi Stewart

I was in the middle of composing my response to Dave when your post came in so now it looks like my reply is just nicking your ideas but honestly, I did come up with the same solution ... great minds think alike after all!!

Thanks

Geoff

PS ...but you express yourself much better!

Stewart Joslyn SSCertifiable Points: 6131 More actions · Answer 5

no problem, Geoff. If you only need to check when the user is accessing a data area (eg customer details), then you only need to check the database as the user enters that screen. Saves continuous polling.

Alternatively, I would suggest having a server component do the data access rather than have every user hitting the database every second. (How many users?). Presumably, you are refreshing a separate local database with the changes.

If the local database is SQL Server and there are not any firewall issues (the server component solution helps with that by the way), you could set up a view in the local database that accesses the remote database. Perhaps throw in a trigger to update local tables? Or perhaps a trigger in the remote database that updates the local one on changes or inserts (never tried that but may be worth investigating).