1

I'm trying and failing to do something a follows

select 1 union xp_cmdshell 'whoami'

Is there anyway to get this work? The simpler the better i.e. one statement.

  • 4
    Please don't over-simplify your actual problem. If you are trying to union the results of a table with the content of a text file, don't state that your problem involves a constant and command output. This just makes it harder to help you. There are smart people here; they don't need over-simplification. – Aaron Bertrand Jan 19 '16 at 19:34
  • That's basically what I have... – pee2pee Jan 19 '16 at 22:43
  • Wait, what is "that"? What's in your question about a constant and command output, or what I stated in my comment about a table and a text file? – Aaron Bertrand Jan 19 '16 at 23:04
2

If we ignore that UNION would remove duplicates (which seems valid to ignore in the case given in the question because 1 can't clash with a DOMAIN\UserName string).

EXEC master.sys.xp_cmdshell N'echo 1 & whoami'
|improve this answer|||||
1

This is probably the simplest:

CREATE TABLE #x(y nvarchar(max));
INSERT #x(y) EXEC master.sys.xp_cmdshell N'whoami';
SELECT N'1' UNION SELECT y FROM #x WHERE y IS NOT NULL;

You might be able to get it done in fewer statements by creating a loopback linked server, but you'll run into all kinds of obstacles - server needs to be configured for data access, metadata can't be determined from an extended stored procedure, etc.

Why not have whatever creates the text file actually put the contents into SQL Server instead (or at least in addition to)?

|improve this answer|||||
1

You're going to have to insert the results into a temporary table or table variable. You can use INSERT ... EXEC to fill a table with results from a stored procedure call, but that's a special construct. You can't use the results from a stored procedure call as though it's a table in its own right.

DECLARE @Result TABLE(ResultData NVARCHAR(255) NULL);

INSERT INTO @Result(ResultData)
EXEC xp_cmdshell 'whoami';

At that point, you can use the table variable in your UNION query.

|improve this answer|||||
0

Your problem is that xp_cmdshell is a stored procedure. You can do it, and it isn't difficult, but it will require multiple steps. Basically you just have to dump the output of xp_cmdshell (or any other stored procedure) into a temp table, then union that temp table with your query.

CREATE TABLE #temp (outputstr varchar(500))

INSERT INTO #temp
EXEC xp_cmdshell 'whoami'

SELECT '1'
UNION ALL
SELECT outputstr FROM #temp
WHERE outputstr IS NOT NULL
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.