Can EXEC master..xp_cmdshell be used for dataset?
I have one Windows shell command that I would like to run (via EXEC master..xp_cmdshell) once for each row of the table. I am using information from various fields to generate command output.
I am more into writing T-SQL programs (as opposed to individual queries), and cannot fully think about the syntax for this, or even possibly / recommended.
I tried to create one column table variable and then populated each row with the command I want to run. I strangled how to iterate over this table variable and actually run the commands. Googling around was useless.
Thanks in advance for your help!
source to share
You can 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
source to share
Is this a one-time job? If so, you might be better off coming from the other side. That is, instead of writing a stored procedure to call XP_CMDSHELL to run some program against the table data, you should consider writing a program to work with the table data directly. If one scripting product comes to mind, it's PowerShell. It has built-in support for any database supported by the Windows platform, and you'll find tons of scripts at www.poshcode.org to do this sort of thing.
On the other hand, if this is something that needs to be planned, I guess there is nothing terrible about your idea, other than XP_CMDSHELL disabled out of the box with SQL Server these days. Re-enabling is opening your server to a whole new world of exploits, especially if the table data comes from a web page form or some other questionable source.
-Oisin
source to share