opc.three (7/3/2012)

---

Lowell (7/3/2012)

---

there's a bit of a work around you can try by using openrowset:, it tends to be a LOT slower, but it works:

--database name required

SELECT *

INTO #tmp

FROM OPENROWSET( 'SQLNCLI',

'Server=(local);Trusted_Connection=yes;',

'SET FMTONLY OFF; SET NOCOUNT ON; exec master.sys.sp_who'

)

this worked. thanks

It should be noted that using this technique requires us to enable Ad Hoc Distributed Queries. The option is disabled by default.

-- view your settings

EXEC sys.sp_configure

@configname = 'Ad Hoc Distributed Queries';

Please see the Security Note in BOL:

Ad Hoc Distributed Queries Option (SQL Server 2005)