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.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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)?
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.
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