Turn: insert exec proc cannot be nested

Insert exec proc cannot be nested solution insert exec proc cannot be nested solution from: http://tuolang2006.spaces.live.com/blog/cns! 7a975ff078b9a497! 119. entry-Wang Chenghui: repost the post. indicate the source Stored Procedure C and return a result set, stored Procedure B uses insert/exec proc to store the result set returned by C into a temporary table and return a result set,
If stored procedure a uses insert/exec proc to save the result set of B to a temporary table, an error is reported: The insert exec statement cannot be nested. Solution: Replace the insert/exec proc Statement of process B
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, but it seems that process a needs to execute process B first, that is, add exec B before the statement insert/exec proc of process.
As long as process B is executed once first, it can be executed normally without executing a later, so that process B can be set to run at startup (set with system process sp_procoption) Create proc PC
As
Select top 10 * From authorsdrop 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
Select * from # tcreate proc Pa
As
Select * into # T from authors where 1 = 0 exec Pb
Insert into # T exec Pb
Select * from # T exec Pb
Exec PA or use the following method to solve the problem: first create a connection server to connect to your own database, as shown below:
Exec sp_addmediaserver
@ Server = 'self ',
@ Srvproduct = '',
@ Provider = 'msdasql ',
@ Datasrc = NULL,
@ Location = NULL,
@ Provstr = 'driver = {SQL Server}; server = (local); initial catalog = Master; uid = sa; Pwd = 111111; 'then enable the RPC function of the connection server, right-click the connection server, click Properties, click server options, and select the RPC and RPC output items. In process a, insert into # T exec Pb is updated to insert into # T exec self. Pubs. DBO. Pb. All Code As follows: -- ccreate proc PC
As
Select top 10 * from authors -- create process bcreate proc Pb
As
Select * into # T from authors where 1 = 0
Insert into # T exec PC
Select * from # t -- create a self-Connected Server
Exec sp_addmediaserver
@ Server = 'self ',
@ Srvproduct = '',
@ Provider = 'msdasql ',
@ Datasrc = NULL,
@ Location = NULL,
@ Provstr = 'driver = {SQL Server}; server = (local); initial catalog = Master; uid = sa; Pwd = 111111; '-- the creation process is acreate proc Pa
As
Select * into # T from authors where 1 = 0
Insert into # T exec self. Pubs. DBO. Pb
Select * from # T
-- Execution process A: The test passes