ravioli
ravioli

Reputation: 3823

SQL Server - xp_cmdshell timeout

I have an SP that invokes xp_cmdshell to call an .exe. The .exe has logic to avoid requiring user input, but a few still slip through, which cause the SP to hang indefinitely and leave things in a bad state.

enter image description here

Is there a way to add timeout logic to the xp_cmdshell call, so that the .exe call is aborted, gracefully if possible, after 60 seconds (i.e. equivalent of CTRL-Z or CTRL-C in cmd prompt)?

SP

CREATE OR ALTER PROCEDURE RC.USR_SP_REFRESHEXCELFILE (
      @SourceFilePath VARCHAR(1000)
    , @SourceFileName VARCHAR(255)
    , @TargetFilePath VARCHAR(1000)
    , @TargetFileName VARCHAR(255)
    , @Result INTEGER OUTPUT -- 0 = success, 1 = failure
) AS
BEGIN 
    -- Declare variables
    DECLARE 
       @ExcelRefreshProgramLocation VARCHAR(1000),
       @CommandString VARCHAR(8000)

    -- Get exe path
    SET @ExcelRefreshProgramLocation =  <...some location...>

    -- Set command string
    SET @CommandString = 
        @ExcelRefreshProgramLocation + ' ' + 
        @SourceFilePath + ' "' + 
        @SourceFileName + '" ' + 
        @TargetFilePath + ' "' + 
        @TargetFileName + '"' -- quotes not working with file paths

    -- Allow advanced options to be changed
    EXECUTE sp_configure 'show advanced options', 1;  
    RECONFIGURE;  
  
    -- Enable command shell
    EXECUTE sp_configure 'xp_cmdshell', 1;  
    RECONFIGURE;  

    -- Execute command
    EXEC @Result = xp_cmdshell @CommandString;

    -- Disable command shell
    EXECUTE sp_configure 'xp_cmdshell', 0;  
    RECONFIGURE;  
  
    -- Prevent advanced options from being changed
    EXECUTE sp_configure 'show advanced options', 0;  
    RECONFIGURE;  

END  

Upvotes: 0

Views: 555

Answers (0)

Related Questions