February 20, 2007 at 4:17 pm
I have a system that needs to notify an external program when certain data modification occurs. The system already exists, but is being updated/rewritten. I'll describe the system:
Process control software (3rd party) gets info from the PLC and executes a stored procedure on SQL Server. This stored procedure changes data in one or more tables.
Controller application (custom app, VB.NET) uses a timer, connects and selects data every second. If the data has changed, the application has processing to perform, which may include additional database updates.
While I can't change the 3rd-party app, I have full control over the SQL Server (will be 2005) and the custom app (will be .NET 2.0).
I'm looking for a way that the SQL Server, in a stored procedure or a trigger, can send a message/event to an external program running on the same machine. The program will be running as a service. It monitors devices on the serial ports and it also sends events in the other direction, which is not a problem, since it can execute stored procs.
Would Service Broker be useful here? What about CLR procedures? There is so much documentation on those and I as I'm digging through it, I'm not sure what can actually be done with external applications.
I can see how to launch an external app with xp_cmdshell, but I need to send data to a service that is already running.
Any advice would be appreciated...
February 20, 2007 at 5:57 pm
This is very easy to do with DTS/SSIS package run as a job using xp_cmdshell, SQL Server Agent needs Admin level permissions either as clone or nonclone. There is .NET code in both C# and VB.NET by Microsoft and you should also check out the sqldts site for more options. Hope this helps.
Kind regards,
Gift Peddie
February 21, 2007 at 8:56 am
It does help, gives me a direction. I've never used SSIS. I know what it stands for and that it has something to do with data import/export. Microsoft's edition chart indicates that Workgroup edition (which I was recommending) doesn't have it. I might be able to swing Standard edition, though. Enterprise is out of the question.
I looked at sqldts site and I haven't the first idea where to start. I have about one day to figure out whether this is feasible and maybe one more day to make a basic design. Even then, I might have to purchase Workgroup edition.
February 21, 2007 at 10:10 am
Ok I have got the links to the Microsoft provided code, you should write a stored proc to collect all the new values and put the values into a separate table, create a DTS/SSIS package to collect the data in your separate table and deliver that to your external program. Standard will do but you could also use 2000 DTS if cost is an issue. There are many more DTS related code in the links below, for similar SSIS code browse books at your local bookstore. Hope this helps.
http://support.microsoft.com/default.aspx?scid=kb;en-us;326909
http://support.microsoft.com/default.aspx?scid=kb;en-us;321525
http://support.microsoft.com/default.aspx?scid=kb;en-us;328587
Kind regards,
Gift Peddie
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply