I am executing:
exec sp_who
go
Is it possible to sort by a field like this?
exec sp_who order by dbname
go
How would I accomplish this?
-
3Nothing says "I'm a guy" like having a username like that!Barrie Reader– Barrie Reader2010-10-29 14:54:59 +00:00Commented Oct 29, 2010 at 14:54
Add a comment
|
3 Answers
You can accomplish this using OPENROWSET as per this question.
sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO
SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;',
'EXEC sp_who')
SELECT * FROM #MyTempTable order by [dbname]
If for whatever reason you are unable to use OPENROWSET then you will have to create a temp table that matches the output of sp_who exactly.
e.g
Create Table #temptable
(
spid smallint,
ecid smallint,
status varchar(100),
loginame varchar(100),
hostname varchar(100),
blk smallint,
dbname varchar(100),
cmd varchar(100),
request_id smallint
)
Insert Into #temptable
Exec sp_who
Select * From #temptable order by [dbname]
Comments
If possible, the best solution would be to try and refactor the procedure into a view.
EDIT
You could also modify the procedure to stick the output into a temporary table, which you could then query.
If you are only going to sort by dbname, the the simplest solution would be to modify the procedure to always sort by dbname.
1 Comment
mikerobi
@i am a gir, it depends on the procedure code, some simple procures can be turned into a view, but most complex ones can't.
Run:
exec sp_helptext sp_who
This will give you the SQL to use. Copy this into a new query window and add in your ORDER BY clause.
