Thanks for the article Eli,

I was just the other day trying to remember how to get the resulset back like that. I'd seen an article a while back... maybe it was yours from 2009?

I think your example threw some people off - They seem obsessed with other ways to access the sp_who and whatever... but that wasn't the point, was it: sometimes we need to get the records from an already-written procedure... that procedure being already in use in other places.. no need to go through a Create Table #xxyyy() to do an Insert / Execute.

SELECT *

FROM OPENROWSET ( 'SQLOLEDB'

, 'SERVER=.;Trusted_Connection=yes'

, ' SET FMTONLY OFF; EXEC [dbname].[dbo].[spName] @paramName = paramValue')

Unfortunately, I have to agree with a few others with regards to opening up the server to Ad Hoc Distributed Queries: no can do - not generally; further, reconfiguring a production server on the fly, then setting it back after the process is done - another no can do.

However, I'll keep this technique in my back pocket: it'll be a good tool for those one-off requests that inevitably come up.

Cheers and best regards form Denver,