sql server programming: add another column to sproc output -- sql server programming

RE: add another column to sproc output

Chris Leonard
11/16/2005 10:59:01 PM

Hassan,

This technique might be more generally useful to you:

SELECT getDate() as CurrentTime, *
FROM OPENROWSET ('SQLOLEDB',
'Server=(local);Database=master;Trusted_Connection=yes', 'SET FMTONLY OFF;
exec sp_who2');

You may have to change the connection string if you are not using Windows
authentication for some reason.

Cheers,
Chris

[quoted text, click to view]

Re: add another column to sproc output

Greg Linwood
11/17/2005 12:00:00 AM

There *might* be *better* ways to do this, but my general purpose sp_who2
script might help you with this specific requirement..

go
if OBJECT_ID('tempdb..#spwho') > 0 drop table #spwho
go
create table #spwho (
SPID int not null
, Status varchar (255) not null
, Login varchar (255) not null
, HostName varchar (255) not null
, BlkBy varchar(10) not null
, DBName varchar (255) null
, Command varchar (255) not null
, CPUTime int not null
, DiskIO int not null
, LastBatch varchar (255) not null
, ProgramName varchar (255) null
, SPID2 int not null
)
go
insert #spwho
exec sp_who2
go
select getdate(), *
from #spwho
--where SPID > 50 and login != SUSER_SNAME()
order by SPID --LastBatch desc
go
if OBJECT_ID('tempdb..#spwho') > 0 drop table #spwho

HTH

Regards,
Greg Linwood
SQL Server MVP

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.