Reputation: 3823
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.
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