locked
EXEC master..xp_CMDShell - Execute bat file placed in other server RRS feed

  • Question

  • Hi people,

    I have a batch file placed in a diferent server from the machine of SQL Server and I have a trigger to execute a batch file everytime that I have a insert into a table. However, due to file is in another server I'm getting the following error:

    • The system cannot find the path specified.

    Anyone nows How to fix that?

    Regards

    due to the file that is on another server it returns the seuinte error:
    due to the file that is on another server it returns the seuinte error:
    due to the file that is on another server it returns the seuinte error:
    due to the file that is on another server it returns the seuinte error:
    due to the file that is on another server it returns the seuinte error:
    Tuesday, January 5, 2016 11:05 AM

Answers

  • >But with Trigger I can identify the transactions in my table.

    And then you can write them to some other table that tracks the changes.  Later, perhaps on a schedule, run the program on the other server to consume the changes.

    >With PowerShell even to?

    You _could_ run the remote powershell through xp_cmdshell in your trigger, but you shouldn't.  And you'll need to write the changes somewhere anyway, as the external program won't be able to see the INSERTED and DELETED virtual tables that you can see in the trigger body.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Tuesday, January 5, 2016 2:48 PM
  • I agree with David and Scott. Spawning xp_cmdshell in a trigger is a seriously bad idea.

    There are a number of ways to get the changes from a table:

    1) Use Change Tracking to detect which row has changed. (But if a row has changed several times, you will not get the individual data.)

    2) Roll your own with a timestamp column has a high-water mark. (Same note as above applies, plus that you can get concurrency issues leading to that you miss updates.)

    3) Use query notification to get changes in the table.

    4) Have the trigger to write the changes to a table, the changes can be written into an XML column created with FOR XML RAW. That table can then be handled as above.

    5) Post a message on a Service Broker queue. The application can have a thread that waits for messages on the receiver queue (inside a stored procedure).

    But forget about xp_cmdshell in this context.

    Tuesday, January 5, 2016 3:44 PM

All replies

  • Remember when I suggested this was a bad idea in your previous thread.  This is one reason why! Start over. What are you trying to accomplish?
    Tuesday, January 5, 2016 1:42 PM
  • Thanks Scott_morris-ga for your response! :)

    I need to identify when a table has changed. For such created the Trigger that conseue detect when a table has undergone change values ​​operations. And my goal is always to detect changes in the data from that table , run a batch file that aims to run an application that is present in another machine.
    
    
    Basically , I want this application (present on another server ) to run , where to enter data in a given SQL SEerver table.
    Thanks!
    Tuesday, January 5, 2016 2:15 PM
  • Again, running external processes from a trigger is a bad idea. 

    But here's how to do it in Powershell.

    Running Remote Commands

    David


    David http://blogs.msdn.com/b/dbrowne/

    Tuesday, January 5, 2016 2:19 PM
  • But with Trigger I can identify the transactions in my table. With PowerShell even to?
    Tuesday, January 5, 2016 2:23 PM
  • >But with Trigger I can identify the transactions in my table.

    And then you can write them to some other table that tracks the changes.  Later, perhaps on a schedule, run the program on the other server to consume the changes.

    >With PowerShell even to?

    You _could_ run the remote powershell through xp_cmdshell in your trigger, but you shouldn't.  And you'll need to write the changes somewhere anyway, as the external program won't be able to see the INSERTED and DELETED virtual tables that you can see in the trigger body.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Tuesday, January 5, 2016 2:48 PM
  • I agree with David and Scott. Spawning xp_cmdshell in a trigger is a seriously bad idea.

    There are a number of ways to get the changes from a table:

    1) Use Change Tracking to detect which row has changed. (But if a row has changed several times, you will not get the individual data.)

    2) Roll your own with a timestamp column has a high-water mark. (Same note as above applies, plus that you can get concurrency issues leading to that you miss updates.)

    3) Use query notification to get changes in the table.

    4) Have the trigger to write the changes to a table, the changes can be written into an XML column created with FOR XML RAW. That table can then be handled as above.

    5) Post a message on a Service Broker queue. The application can have a thread that waits for messages on the receiver queue (inside a stored procedure).

    But forget about xp_cmdshell in this context.

    Tuesday, January 5, 2016 3:44 PM