INSERT EXEC PROC不能嵌套的解决方法
--王成辉整理,转贴请注明出处
存储过程C返回一个结果集,存储过程B用INSERT/EXEC PROC将C返回的结果集存入一个临时表中并返回一个结果集,
如果存储过程A用INSERT/EXEC PROC再将B的结果集存入一个临时表,这时会报错:INSERT EXEC 语句不能嵌套。
如果存储过程A用INSERT/EXEC PROC再将B的结果集存入一个临时表,这时会报错:INSERT EXEC 语句不能嵌套。
解决方法就是:
将过程B的INSERT/EXEC PROC语句替换为
insert into #T
SELECT *
FROM OPENROWSET('MSDASQL',
'DRIVER={SQL Server};SERVER=192.168.0.1;UID=sa;PWD=111111;Initial Catalog=Pubs',
'EXEC pubs.dbo.pc') AS A
insert into #T
SELECT *
FROM OPENROWSET('MSDASQL',
'DRIVER={SQL Server};SERVER=192.168.0.1;UID=sa;PWD=111111;Initial Catalog=Pubs',
'EXEC pubs.dbo.pc') AS A
即可,不过好像在过程A中要先执行一下过程B才行,即在A过程的语句INSERT/EXEC PROC的前面加上exec B。
只要过程B先执行一次,以后可以不用再执行A也能够正常执行了,这样可将过程B设置为启动时执行即可(用系统过程sp_procoption设置)
只要过程B先执行一次,以后可以不用再执行A也能够正常执行了,这样可将过程B设置为启动时执行即可(用系统过程sp_procoption设置)
create proc pC
as
select top 10 * from authors
as
select top 10 * from authors
drop proc pB
create proc pB
as
select * into #T from authors where 1=0
--insert into #T exec pc
insert into #T
SELECT *
FROM OPENROWSET('MSDASQL',
'DRIVER={SQL Server};SERVER=192.168.0.1;UID=sa;PWD=111111;Initial Catalog=Pubs',
'EXEC pubs.dbo.pc') AS A
select * from #T
create proc pB
as
select * into #T from authors where 1=0
--insert into #T exec pc
insert into #T
SELECT *
FROM OPENROWSET('MSDASQL',
'DRIVER={SQL Server};SERVER=192.168.0.1;UID=sa;PWD=111111;Initial Catalog=Pubs',
'EXEC pubs.dbo.pc') AS A
select * from #T
create proc pA
as
select * into #T from authors where 1=0
as
select * into #T from authors where 1=0
exec pb
insert into #T exec pB
select * from #T
insert into #T exec pB
select * from #T
exec pB
exec pA
exec pA
或者用下面的方法解决:
先建一个连接到自身数据库的连接服务器,如下:
EXEC sp_addlinkedserver
@server = 'self',
@srvproduct = '',
@provider = 'MSDASQL',
@datasrc = NULL,
@location = NULL,
@provstr = 'DRIVER={SQL Server};Server=(local); Initial Catalog=master;uid=sa;pwd=111111;'
EXEC sp_addlinkedserver
@server = 'self',
@srvproduct = '',
@provider = 'MSDASQL',
@datasrc = NULL,
@location = NULL,
@provstr = 'DRIVER={SQL Server};Server=(local); Initial Catalog=master;uid=sa;pwd=111111;'
然后再开启该连接服务器的RPC功能,右击该连接服务器,单击属性,单击服务器选项,然后选中RPC和RPC输出项即可。
在过程A中的语句insert into #T exec pB更新为insert into #T exec self.pubs.dbo.pB即可。全部代码如下:
--创建过程C
create proc pC
as
select top 10 * from authors
as
select top 10 * from authors
--创建过程B
create proc pB
as
select * into #T from authors where 1=0
insert into #T exec pc
select * from #T
as
select * into #T from authors where 1=0
insert into #T exec pc
select * from #T
--创建一个自连接的服务器
EXEC sp_addlinkedserver
@server = 'self',
@srvproduct = '',
@provider = 'MSDASQL',
@datasrc = NULL,
@location = NULL,
@provstr = 'DRIVER={SQL Server};Server=(local); Initial Catalog=master;uid=sa;pwd=111111;'
EXEC sp_addlinkedserver
@server = 'self',
@srvproduct = '',
@provider = 'MSDASQL',
@datasrc = NULL,
@location = NULL,
@provstr = 'DRIVER={SQL Server};Server=(local); Initial Catalog=master;uid=sa;pwd=111111;'
--创建过程A
create proc pA
as
select * into #T from authors where 1=0
insert into #T exec self.pubs.dbo.pB
select * from #T
--执行过程A,测试通过
as
select * into #T from authors where 1=0
insert into #T exec self.pubs.dbo.pB
select * from #T
--执行过程A,测试通过