SELECT from a Stored Procedure

There are multiple ways of doing this.

Method #1

1. if exists (select * from master..sysservers where srvname = 'loopback')     
2. exec sp_dropserver 'loopback'
3. go 

Method #2

1. exec sp_addlinkedserver @server = N'loopback', @srvproduct = N'',     @provider, @datasrc = N'SQLOLEDB', = @@servername 
2. go

Method #3

1. select * into #t from openquery(loopback, 'exec yourSproc') 
2. select * from #t 
3. drop table #t

go