xp_cmdshell Alternative

An xp_cmdshell alternative is useful in an environment where its use is locked down. This article describes a simple alternative.




It is a very powerful and useful extended stored procedure, and I often find a need for it in my SQL Agent jobs.

One such use might be to create a folder in a backup script. I need to run the following DOS command:

md "E:\Backups\dbname_20080611"
If it is not disabled, this could be called from within my job as follows:

exec master..xp_cmdshell 'md "E:\Backups\dbname_20080611"'
However, use of this procedure is disabled by default in SQL Server 2005, and is deliberately disabled anyway in many organizations. My work-around is to create a SQL Agent job to run the command, give it time to run, then delete it again.

The following script does the job:

DECLARE @jobID uniqueidentifier, @cmd varchar(1000) SET @cmd = 'md "E:\Backups\dbname_20080611"' EXEC msdb.dbo.sp_add_job @job_name = '_tmp_MakeDirectory', @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 = 'Create Backup Folder', @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 '000:00:05' -- Give the job a chance to complete IF EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = '_tmp_MakeDirectory') BEGIN EXEC msdb.dbo.sp_delete_job @job_name = '_tmp_MakeDirectory' END
This creates a SQL Agent job, starts it, waits 5 seconds for it to complete, and then deletes it. I suggest you follow my naming convention of prefixing the job name with '_tmp_'. This means that if a job is created but fails to be deleted for some reason, you can see it easily at the top of the list of jobs.

SQL FAQ |  Recent Articles |  SQL Server Administration |  SQL Server Performance
SQL Server Scripts |  SQL Server Error Reference |  Learn SQL |  Reviews |  Miscellany
My Services |  Contact Me |  Privacy Policy |  Site Map

© Richard Fryar. All Rights Reserved.