Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2003
    Posts
    10

    Unanswered: 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 Girenkov

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    yeah...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...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Mar 2003
    Posts
    10
    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

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    Can you be more specific as to what this executable is doing ?

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    [i]I made the small project with the message box
    ~Andrei
    You don't think a message box is a gui?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Mar 2003
    Posts
    10
    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
    Last edited by Andrei_Girenkov; 12-04-03 at 10:48.

  7. #7
    Join Date
    Dec 2002
    Posts
    1,245
    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

  8. #8
    Join Date
    Mar 2003
    Posts
    10
    My SQL Server Service account is the Administrator. I can run the executable just fine when logged in as Administrator.

    ~Andrei

  9. #9
    Join Date
    Sep 2003
    Posts
    522
    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.

    Code:
    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'

  10. #10
    Join Date
    Mar 2003
    Posts
    10
    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

    ~Andrei

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •