使用預存程序返回集–OPENROWSET

取得預存程序sp2的返回記錄，直接select into到暫存資料表：

select * into #t from OPENROWSET(
'SQLOLEDB','SERVER=servername;uid=sa;pwd=123;Database=testdb',
'SET FMTONLY OFF;set nocount on;exec sp2 參數') as a
select * from #t
drop table #t

select * into #t
from openrowset('sqloledb','Trusted_Connection=yes','SET FMTONLY OFF;set nocount on;exec 庫名.dbo.sp2 參數')

--或者這樣寫吧,就不用在調用預存程序中指定庫名,當然,連接字串中就要字串了

select * into #t
from openrowset('sqloledb','Trusted_Connection=yes;Database=testdb','SET FMTONLY OFF;set nocount on;exec sp2 參數')