直接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
'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
有没有方法不指定 'SERVER=servername;uid=sa;pwd=123;Database=testdb' 这句呢?
insert #t exec sp1select * from #t
from openrowset('sqloledb','Trusted_Connection=yes','SET FMTONLY OFF;set nocount on;exec 库名.dbo.sp2 参数')
from openrowset('sqloledb','Trusted_Connection=yes;Database=testdb','SET FMTONLY OFF;set nocount on;exec sp2 参数')
用create table #t(...)肯定是不行的,因为这个SP2可能每次返回的结果集结构都不一样的,看来只能用:
select * into #t
from openrowset('sqloledb','Trusted_Connection=yes;Database=testdb','SET FMTONLY OFF;set nocount on;exec sp2 参数')我再试试!
select * into #t
from openrowset('sqloledb','Trusted_Connection=yes;Database=testdb','SET FMTONLY OFF;set nocount on;exec sp2 参数')
中的sp2 参数这怎么把它换成临时变量呢?因为SP2会变得,参数也变的,我用下面的不行:
declare @strTmp varchar(1000)
set @strTmp = 'SET FMTONLY OFF;set nocount on;exec ' + 'GetMyData 100,200,30'select * into #t
from openrowset('sqloledb','Trusted_Connection=yes;Database=testdb',@strTmp)