Calling an application from SQL (xp_cmdshell)

Hi,

I am trying to run an executable from MS SQL Server 2000. I tried using the commands:

EXEC master..xp_cmdshell '"\\comp1\c\test.exe"', NO_OUTPUT

and

EXEC master..xp_cmdshell 'Start Test "\\comp1\c\test.exe" , NO_OUTPUT

When I go to task manager on comp1 I see that the process is listed as running only under Processes, not under Application. In addition the application doesnt seem to execute properly and the SQL Query doesnt terminate. For testing processes I have just a small VB project that pops up a message box and then exits (it works fine by running it from windows). I never see the message box, nor does it exit the process.

If I try to manually cancel the SQL Query, it still will not stop until I kill the process for test.exe from the task manager.

Does anyone have any ideas on how I could get the query to terminate properly and how to get the executable to do its thing?

Sincerely,
Andrei Girenkovyeah...don't call gui apps from xp_cmdshell unless you want to hang your server...

Only use batch executables that don't require ANY prompting...|||Well I wasn't aware of that, however the executable I'm trying to launch actually doesnt have any sort of GUI...all it does is checks some output from another program and updates some DB tables.

I found that after launching the executable through double clicking it worked fine, but when launching it from SQL it didn't update the DB. I made the small project with the message box as an easier way of testing it rather than having to go to SQL every time and checking whether or not a table got updated.

~Andrei|||Can you be more specific as to what this executable is doing ?|||[i]I made the small project with the message box
~Andrei

You don't think a message box is a gui?|||I mean the actual executable I am trying to run has no GUI. The messagebox program is only for testing. I now scrapped the message box project because of the GUI and am trying to just run/test with the actual executable, however I am not seeing any results either.

My program does the following: I am trying to record the Volume Serial of the C drive of the machine the instance of the SQL server is running. I have an executable which reads the volume serial, scrambles it, records it into the DB, and then exits out. It takes as a command line argument the name of the instance of SQL server on that machine.

I am trying to write a stored procedure that I could call which would run this executable and pass it the instance name as an argument. The call in the stored procedure is the following:

EXEC master..xp_cmdshell '"C:\volumeserial.exe" -' + (select @.@.SERVERNAME), NO_OUTPUT

Right now the select @.@.SERVERNAME part is hardcoded to the server name because I was getting an syntax error at the + before the select statement, and the comma after it. It actually looks like this:

EXEC master..xp_cmdshell '"C:\volumeserial.exe" -InstanceName', NO_OUTPUT

When I call this stored procedure I can see in the task manager under processes that Volumeserial.exe is running, however the DB doesnt get updated, the process never terminates, and the stored procedure never gets past that line. Like I mentioned earlier, the program works fine if I call it from outside SQL, so I think it's a SQL issue.

~Andrei Girenkov|||One thing to double check would be the connection string to the database. If you are using a trusted connection, remember that the application will be running under the identity of the SQL Server Service account.

Another thing to check would be permissions on the executable. Again, be sure that the SQL Server Service account as execute permissions on the application executable.

Make sure that you test you application executable while logged in as the SQL Server service account.

Regards,

hmscott|||My SQL Server Service account is the Administrator. I can run the executable just fine when logged in as Administrator.

~Andrei|||how about making the executable just return the scrambled serial number without trying to connect to the server and do the update? you can do those things in your stored procedure.

create table #tmp ([output] nvarchar(4000) null)
insert #tmp EXEC master..xp_cmdshell 'C:\volumeserial.exe'
delete #tmp where [output] is null
update y set scrambled_serial = [output]
from your_table y, #tmp where y.drive_letter = 'C'|||Thank you everyone for all your help. I had a hunch that it was not connecting to SQL properly so instead of writing the value back to the DB I had the program write it out to a file along with the command line arguments. It turned out that the problem was caused because in SQL I was passing the InstanceName parameter to the program with a dash (-). The program wasn't expecting that.

The other problem of not being able to select @.@.ServerName was fixed by just declaring the whole string as a varchar() and passing that variable to xp_cmdshell instead of the concatinated string.

Thanks again everybody who helped

~Andreisql