Home > SQL Server, SSIS > xp_cmdshell strange problem

xp_cmdshell strange problem


I was working on this request: from a ship are unloaded many containers, it is needed to track the ones not still invoiced; in this case must be sent an email with an Excel file as attachment, containing the list of these containers.
The containers are register in a table of an SQL Server 2008 SP3 10.0.5520.0 (X64).
I tried OPENQUERY but without success (it seems that read and write Excel with OPENQUERY is a sort of black magic) so , giving also the fact that in every case there were many steps too complex to achieve with TSQL (CreateObject and CLR stored procedures were not acceptable options) i used an SSIS package , without using a SSIS catalog: it must be used dtexec.
I shoud try with SQL Server 2014, but with SQL 2008 there is this problem and double quoted strings are tipically requested in a dtexec command line: so i use the trick of the old MSDOS command ECHO to create a batch file and then execute this .bat from xp_cmdshell.
My TSQL is :

DECLARE @varSql nvarchar(500)
DECLARE @varFileName nvarchar(20)
DECLARE @varEcho nvarchar(1000)
DECLARE @XlsFileName nvarchar(500) 
DECLARE @intRes int
SET @varFileName = 'D:\SSIS\ContainersWithoutBilling\batches\' + CAST(@p_Voyage AS varchar) + '.bat'
SET @XlsFileName = 'D:\SSIS\ContainersWithoutBilling\Voyages\' + CAST(@p_Voyage AS varchar) + '_NotInvoiced.xlsx'
SET @varSql = '' + CHAR(34) + 'C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec' + CHAR(34) + ' /FILE ' + CHAR(34) + 
				'D:\SSIS\ContainersWithoutBilling\SSISPkg\bin\Package.dtsx' + CHAR(34) + ' /CHECKPOINTING OFF  /REPORTING EW  /SET \Package.Variables[IDVoyage].Value;' + 
				CAST(@p_Voyage AS varchar)
SELECT @varEcho = 'ECHO ' + @varSql  + ' > ' + @varFileName 
EXEC @intRes =  master..xp_cmdshell @varEcho , no_output	

At this point we have an batch file, for example is generated something as this 116243.bat:

"C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec" /FILE "D:\SSIS\ContainersWithoutBilling\SSISPkg\bin\Package.dtsx" /CHECKPOINTING OFF  /REPORTING EW  /SET \Package.Variables[IDVoyage].Value;116243

This example works without problems launched from the File Explorer, and you can think that with

EXEC @intRes = master..xp_cmdshell @varFileName, no_output

your problems are resolved, instead i was surprised that sometimes the .bat file, that was perfectly working launched by hand, under xp_cmdshell the process hangs: and i was constrained to kill both the cmd and the dtexec processes.
Tipically a xp_cmdshell is blocked if is requested an user input , but this was not the case.
After some head scratching, i tried with an temporary job:

DECLARE @jobID uniqueidentifier
DECLARE @cmd varchar(1000) 
SET @cmd = 'D:\SSIS\ContainersWithoutBilling\batches\' + CAST(@p_Voyage AS varchar) + '.bat'
EXEC msdb.dbo.sp_add_job @job_name = '_tmp_batch', @enabled  = 1, @start_step_id = 1, @owner_login_name='sa', @job_id = @jobID OUTPUT 
EXEC msdb.dbo.sp_add_jobstep @job_id = @jobID, @step_name = 'launch batch', @step_id = 1, @subsystem = 'CMDEXEC', @command = @cmd
EXEC msdb.dbo.sp_add_jobserver @job_id = @jobID
EXEC msdb.dbo.sp_start_job @job_id = @jobID, @output_flag = 0 
WAITFOR DELAY '00:00:30' -- wait an reasonable time of 30 seconds until the job should be securely completed
IF EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = '_tmp_batch')
	EXEC msdb.dbo.sp_delete_job @job_name = '_tmp_batch'
-- or you could delete if exists the temp job before the creation, and avoid the
-- WAITFOR if there are not other instructions that depends from the job work

and this is working also for the .bat files that hangs under xp_xmdshell.
I think that using an SSIS catalog there aren’t these problems, but in this case the dtexec was mandatory.
At the end it seems that xp_cmdshell has some bug, and an temporary job is always a better choice.

Categories: SQL Server, SSIS
%d bloggers like this: