xp_cmdshell hell

[Rozza]

Hi, I'm trying to run an external program from within a stored procedure using xp_cmdshell. The actual software I want to run is some address cleaning software which does not require any user input to run. For testing purposes I have been trying to launch the windows calculator from within the procedure. My procedure is as follows:

CREATE PROCEDURE addressclean AS
exec master.dbo. xp_cmdshell 'c:\winnt\system32\calc.exe'
GO

When I run this, the query never finishes and I have to stop it manually. The calculator never appears on the screen although if I look in task manager the calc.exe process is running. All permissions to run xp_cmdshell are ok, I can do a dir.* from xp_cmdshell and this works OK, the problem only occurs when trying to run programs. I have also tried calling the program from within a batch file and then running the batch file from xp_cmdshell, but this produces the same results.
I have also tried setting up a scheduled job to run an external application, but when it runs this also produces the same results.

Can anyone shed any light on this? I know it should be possible to achieve this, but I'm completely stumped!!

Cheers

Paul

 

[JamesLean]

When you use xp_cmdshell, it runs the cmd process on the server side. The behaviour you describe is perfectly normal.

Although you can return the results of a command, eg "dir", to the client, you can't run a program that needs user input, eg calc, on the client side. It runs on the server and the process will just stay in memory - as you've seen.

If your address cleaning app really doesn't need user input then it should be fine to use xp_cmdshell to start it off - have you actually tried it?

--James

 

[Rozza]

Hi James,

Thanks for your reply. The answer to your question is yes I have tried it, but it doesn't work. The process stays in memory but never completes and hence never cleans the addresses. The software definately doesn't require user intervention as you can point it to a file which it uses to read the various settings that a user would normally do via the GUI. If this can't be possible Is there some other way to run external programs from SQL Server?

Cheers

Paul