| Author |
Topic |
|
simondeutsch
Aged Yak Warrior
547 Posts |
 Posted - 2010-01-03 : 02:45:24
|
| I'm designing an interface component that will send data via TCP to a listening service. This component needs to be "fired" every time a row is inserted or modified in Tables XYZ. The data that will be sent over is in a special format that would be too difficult to code in T-SQL, and therefore it needs to be coded outside of SQL Server. Besides for managed code/using CLR integration, what are the other possibilities of running this? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-03 : 11:27:31
|
| isnt it better to do this via sql job which fires in frequency? You may use a table to store changed values (deltas) for each period interval and then use this for job to process. Each run will populate a date in a control table which it uses next time to identify delta data. |
 |
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2010-01-03 : 12:00:25
|
| It must be done in real-time. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-03 : 12:04:09
|
quote:
Originally posted by simondeutsch
It must be done in real-time.
ok then wat about calling xp_cmdshell from trigger (though its a bit dirty) |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2010-01-03 : 12:09:40
|
| Doable. Does it run synchronously or asynchronously? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-03 : 12:22:31
|
| it runs synchronously |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-03 : 12:23:13
|
| see this toohttp://msdn.microsoft.com/en-us/library/aa260689(SQL.80).aspx |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2010-01-03 : 12:30:02
|
| What you want to look at using is service broker - it is designed to do exactly what you want:http://msdn.microsoft.com/en-us/library/ms345108(SQL.90).aspxThis will run asynchronously - but, that should be desired as it will not block your application. |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2010-01-04 : 23:55:03
|
| Service Broker seems very complicated. xp_cmdshell can call an application, but I'm thinking more in line of a .dll.Has anyone ever worked with sp_OACreate etc.? I'm wondering as to the speed hit when the code is inside a trigger. Also, if the automated object is a COM .exe, perhaps that will run asynchronously? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Exporting data upon INSERT trigger