Can EXEC master..xp_cmdshell be used on a set of data?

Question

I have a single windows shell command I'd like to run (via EXEC master..xp_cmdshell) once for each row in a table. I'm using information from various fields to build the command output.

I'm relativity new to writing T-SQL programs (as opposed to individual queries) and can't quite get my head around the syntax for this, or if it's even possible/recommended.

I tried creating a single column table variable, and then populating each row with the command I want to run. I'm stifled at how to iterate over this table variable and actually run the commands. Googling around has proven unhelpful.

Thanks in advance for any help!

Answer 1

You could always use a cursor:

USE Northwind

DECLARE @name VARCHAR(32)
DECLARE @command VARCHAR(100)

DECLARE shell_cursor CURSOR FOR 
SELECT LastName FROM Employees

OPEN shell_cursor

FETCH NEXT FROM shell_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @command = 'echo ' + @name
    EXEC master.dbo.xp_cmdshell @command
    FETCH NEXT FROM shell_cursor INTO @name
END

CLOSE shell_cursor
DEALLOCATE shell_cursor

GO

Answer 2

Hey Alan,

Is this a once-off job? If so, you might be better off coming from the reverse direction. That is to say, instead of writing a stored procedure to call XP_CMDSHELL to run some program against table data, you should consider writing a program to work against the table data directly. If one scripting product comes to mind, it's PowerShell. It has integrated support for any database that the windows platform supports and you'll find a ton of scripts on www.poshcode.org to do that kind of thing.

On the other hand, if this is something that is to be scheduled, I guess there's nothing hugely wrong with your idea, apart from the fact that XP_CMDSHELL is disabled out of the box with SQL Server these days. Re-enabling it is opening your server to a whole new world of exploits, especially if that table data is sourced from a web page form or some other questionable source.

-Oisin