Get Results from XP_CMDSHELL

Question

I have been searching the web some and it seems like the only way to get the results from XP_CMDSHELL is to store them into a temp table. Is there really no easier way?

From Experts Exchange:

No, xp_cmdshell will not return any information from the exe. and you have to use the following syntax if you are not in the master database to run it. master..xp_cmdshell. You will have to give your user permission to execute this procedure in the master database. You will have to have your exe insert the information its self because it can not return information to the process that called it.

And...

While @result only gets the return value from xp_cmdshell, you may be able to capture the results of the command by inserting directly into a table... something like this:

ymmv...

set nocount on
declare  @filepath   varchar(255),
         @cmd        varchar(255),
         @rc         int

select   @filepath = 'c:\temp\'         
select   @cmd      = 'dir ' + @filepath + '~*.tmp'

create table #output (output varchar(255) null)
insert #output exec @rc = master..xp_cmdshell @cmd
select * from #output where output is not null
drop table #output

score 32 · Accepted Answer · answered Feb 29 '12 at 19:11

There is no easier way to capture STDOUT/STDERR feedback from xp_cmdshell; there is at least one alternative but it couldn't be classed as easier:
It would be possible to redirect the output of the command to a text file as part of the command, then read the text file using OPENROWSET.

BTW there is at least one error in the script posted above. The docs for xp_cmdshell state that it returns command output as nvarchar(255).
Also, the temp table ought to have an identity column, otherwise the results may not be displayed in the correct order:

...
create table #output (id int identity(1,1), output nvarchar(255) null)
insert #output (output) exec @rc = master..xp_cmdshell @cmd
select * from #output where output is not null order by id
drop table #output

score 1 · Answer 2 · answered Mar 01 '12 at 17:01

This is what I ended up doing... I checked back today and saw your response. I was in a real time crunch yesterday so started working in the direction of the temp table since it was a confirmed working solution. I had chosen to avoid creating the temp files since it seemed just as easy or easier to handle things internally since I really am just using it as a clipboard. The one change I will make if necessary is to add a unique number to the temp table name although I do not think that I have to worry about these being processed simultaneously (meaning that a second call of the stored procedure might dump the temp table while the cmd shell is running). We'll see...

I call a stored procedure (look further down) to encrypt the password: The code below has been modifed to make it self-sufficient. I am not actually setting the password manually as this is basically a password offloading/synchronization solution.

DECLARE @password       VARCHAR(64)
DECLARE @encryptedpass  VARCHAR(128);

SET @password = '1234'

BEGIN TRY
    EXEC pass_encrypt @password, @encryptedpass = @encryptedpass OUTPUT
END TRY
BEGIN CATCH
    PRINT 'ERROR'
    RETURN
END CATCH
SELECT @encryptedpass

Here is the encrypting stored procedure: To check and make sure the program executes properly without having to guess why the return code indicates failure, I have additional code (not listed here) that checks @@rowset. If it is more than 1, I know something went wrong and I can capture/return the actual error (if desired) instead of just making up my own message that says it failed without giving any indication of why. Realistically checking this way is more useful for debugging or for logging the error into another table for future review - not for real time interpretation since I am not going to send an error like that back to an end user.

USE [**my_database**]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO


CREATE procedure [dbo].[pass_encrypt]
(   @password       VARCHAR(64),
    @encryptedpass  VARCHAR(128) OUTPUT
)
AS
BEGIN
    DECLARE @command        VARCHAR(200)
    SET @command = **'C:\encrypt_pwd.exe**' + ' "' + @password + '"'

    BEGIN
        IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[#temppass]') AND type in (N'U')) 
            DROP TABLE [dbo].[#temppass]
        BEGIN TRY
            CREATE TABLE #temppass(encrypted varchar(1000))
            INSERT INTO #temppass execute xp_cmdshell @command
            IF (@@ROWCOUNT > 1)
                BEGIN
                    SET @encryptedpass = NULL
                    DROP TABLE #temppass
                    RETURN
                END
            ELSE
                BEGIN
                    SELECT @encryptedpass = encrypted FROM #temppass
                END
            --SELECT @encryptedpass
        END TRY
        BEGIN CATCH
            SET @encryptedpass = NULL
            DROP TABLE #temppass
            RETURN
        END CATCH
        --SELECT encrypted FROM #temppass
        DROP TABLE #temppass
    END
END

Get Results from XP_CMDSHELL

2 Answers2

Linked