Using Openrowset to return usable columns from a stored procedure

use master
go

-- drop if already present
if object_id('master..waittype_summary') is not null 
 drop proc waittype_summary

-- create procedure
create proc waittype_summary as
  select lastwaittype, 
        count(dbid) as process_count,
         sum(cpu) as cpu
    from master.dbo.sysprocesses
group by lastwaittype
order by sum(cpu) desc;
go

-- use openrowset to query the local procedure
select * from openrowset('SQLOLEDB', 'Trusted_Connection=Yes;Server=(local);Database=master', 'exec dbo.waittype_summary')
go

Although when running locally (as in this example), this is a daft idea (much easier to use a temp table) it demonstrates how to use openrowset.
Openrowset allows you to quickly connect to external data sources (access, excel, text files, oracle etc... ) without adding linked servers or importing data.
Under SQL 2005 however, we get this error -

Msg 15281, Level 16, State 1, Line 2
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

This is because SQL 2005 is more secure by default and the OpenRowset statement needs to be allowed via SAC (the Surface Area Configuration tool).

27/11/06 : More about that, here.