how to call a batch file from trigger

Hi,
I have a requirement to invoke a java application and update the remote MySQL database, when a particular field in MSSQL Server 2000 is updated. my idea is to write a trigger on AFTER update of field and from trigger invoke a batch file which will run jav
a application.
if any one provide me information on how to invoke a batch file from trigger or any alternative solution to my problem, would be mutch appriciated.
Thanks In advance.
Sunil
Hi Sunil,
Try using the following command in the trigger
exec master.dbo.xp_cmdshell '<bat_file>'
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Sunil,
As Ashish indicates, xp_cmdshell will enable you to invoke external shell
cmds.
I personally would, in general, be wary of this approach as there are a
number of undesirable implications with it: the primary one being execution
time. Within a transaction, the time taken to invoke the shell (and then run
your application which updates a remote MySQL instance) would be extremely
costly; this would be even more so for multirow updates. One consequence of
this is that you will be hold locks for long periods of time, and your table
is likely to become a hot spot / point of contention. Security could also be
an issue. How much control do you have of the batch file that will be
invoked ? Can anyone replace the file with a potentially malicious script ?
As regards alternatives, the logically obvious one is to make the relevant
transaction distributed. This also gives rise to various issues, but I
gather linking to MySQL is problematic because of insufficient support from
the ODBC driver. Still you may wish to investigate this further.
Alternatively, within your trigger, write the updated data to another table.
From outside SQL Server (in your Java app / server, say) you could then poll
the table, and apply any updates to MySQL. This would probably be the more
"robust" approach.
A "similar" approach would be use the SQL Server Agent's alert system. This
would, essentially, entail your defining a custom error / message [ie.
sp_addmessage 50100, 10, 'Field Updated', say], and then defining an alert
based on your custom error number; your alert's response could then invoke
your batch file. To raise the event from within your trigger, you would
simply call raiserror with your custom error number [ie. raiserror(50100,
10, 1), say].
It would also be possible to combine these: you could write the updated data
to another table, and when you've built up a batch of pending updates raise
an event / alert to run your batch file / app. If you decide to batch
updates, try to avoid counting rows if at all possible (you may be able to
use sysindexes.rowcnt instead); you could also batch based on time or on the
data being updated.
Without knowing a bit more about your requirements (inc. timeliness of
updates to MySQL) and your environment, it is difficult to decide which
approach is "best". Still, the above should give you a few more options to
consider.
Regards
Stewart
"Sunil" <sunil@.methdos.com> wrote in message
news:A29F6296-6088-421D-8DE4-364B0D7EF6C8@.microsoft.com...
> Hi,
> I have a requirement to invoke a java application and update the remote
MySQL database, when a particular field in MSSQL Server 2000 is updated. my
idea is to write a trigger on AFTER update of field and from trigger invoke
a batch file which will run java application.
> if any one provide me information on how to invoke a batch file from
trigger or any alternative solution to my problem, would be mutch
appriciated.
> Thanks In advance.
> Sunil