Sql Server 2005 executing exe on remote machine or connect to server application

Question

Hello, I'm try to figure how to create sql server stored procedure that when a condition is met executes an executable, with command line arguments, on a remote machine within the network. I could write my own little server application to handle the communication sent from the stored procedure for the executable, but I'm not certain if socket programming is a reasonable option with sql server. Any direction would be helpful.

Answer 1

You'd need some server-side application anyway, even if that application were SQL Server :)

Something like rsh might do - have your local SQL instance invoke xp_cmdshell with an rsh command (or a batch file), connecting to the remote server and executing the command.

If you have SQL on the remote machine, it'd be far easier; call a stored procedure on that machine from the local machine, and let that stored procedure do the work (might need fiddling with proxies and "execute as").

A third option would be to create a .NET stored procedure, and do your socket programming or remote invocation from there - it runs in-process, so you get similar same performance and security benefits you'd get from writing it in T-SQL rather than hopping out to a cmdshell with all the mess that entails.