0

Would it be possible to return the results of the following query into a table in SQL Server column wise.

exec master..xp_cmdshell 'Query User'

I have tried the following but this only returns one row with a string.

declare @tbl table (output NVARCHAR(100))

insert into @tbl (output)
    exec master..xp_cmdshell 'Query User'

select * 
from @tbl 
where output is not null

I would like the output to be in different columns within the table instead of one. For Example:

USERNAME  SESSIONNAME  ID  STATE   IDLE TIME   LOGON TIME

Any help would be appreciated.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Rahul Bhola
  • 11
  • 5
  • No. The "query user" function doesn't return a table. Like anything on the command line that is printing to the standard output, it's just writing text. There's no metadata associated with it. In the case of this particular command, it happens to return that text in a way that "looks like" a table, but it's just text with spaces in it. You will have to dump to a single column and then parse the output strings if you want to split the data into actual columns in SQL Server. – allmhuran Jul 23 '20 at 09:51
  • Whats your output of `exec master..xp_cmdshell 'Query User'`? – NvrKill Jul 23 '20 at 09:52
  • @RyffLe A sample output is:- `USERNAME SESSIONNAME ID STATE IDLE TIME LOGON TIME abc.xyz console 1 Active none 21-07-2020 16:16` – Rahul Bhola Jul 23 '20 at 09:57
  • What happens if you do `SELECT value FROM STRING_SPLIT(exec master..xp_cmdshell 'Query User', ' ');`? Sorry on my Server the 'Query User' function is locked for users. – NvrKill Jul 23 '20 at 10:01
  • @RyffLe You can't `select` from `exec`. – allmhuran Jul 23 '20 at 10:02
  • @RyffLe It provides me with an error. I'm currently on Microsoft SQL Server and the STRING_SPLIT was introduced with 2016. I guess @allmhuran is also right that we cannot use `SELECT` with `EXEC`. – Rahul Bhola Jul 23 '20 at 10:06
  • I found 2 Stackoverflow post which can probably help. INSERT-EXEC: https://stackoverflow.com/questions/3795263/errors-insert-exec-statement-cannot-be-nested-and-cannot-use-the-rollback-s STRING-SPLIT: https://stackoverflow.com/questions/10914576/t-sql-split-string All posts are for a sql-server-2008 – NvrKill Jul 23 '20 at 10:12

0 Answers0