insert into #pub_status
exec distribution..sp_replmonitorhelppublication @@SERVERNAME,'DATA_BASE' 

Msg 8164, Level 16, State 1, Procedure sp_MSload_tmp_replication_status, Line 80

An INSERT EXEC statement cannot be nested.

exec distribution..sp_replmonitorhelppublication @@SERVERNAME,'DATA_BASE'

insert into #pub_status
exec distribution..sp_replmonitorhelppublication @@SERVERNAME,'DATA_BASE' 

insert into #pub_status
select *
from openrowset('SQLNCLI','Server=Servername;Trusted_Connection=yes;','exec distribution..sp_replmonitorhelppublication @@SERVERNAME,'DATA_BASE') AS a 

quote:

---

Originally posted by Sachin.Nand

Since you know the no of columns the sp would return then you can create the table with the columns instead of insert into...and then insert into the table from the stored proc.


PBUH

---

Create table #t(id int)

Insert into #t
Exec MySp

quote:

---

Originally posted by Sachin.Nand

I am saying create a temporary table with a create statement and define the columns and then use the insert statement to insert data from the sp.
If my sp returns only single column say id then do Something like this

Create table #t(id int)

Insert into #t
Exec MySp

sorry but i cant see how this will solve the problem

you're still doing insert...exec which is cause for problem OP is facing


PBUH

---

quote:

---

Originally posted by Sachin.Nand

Sorry..Not sure what I was thinking...

PBUH

---

insert into #pub_status
select *
from openrowset('SQLNCLI','Server=Servername;Trusted_Connection=yes;','SET FMTONLY OFF exec distribution..sp_replmonitorhelppublication @@SERVERNAME,'DATA_BASE') AS a 

quote:

---

Originally posted by visakh16

try using below syntax instead

insert into #pub_status
select *
from openrowset('SQLNCLI','Server=Servername;Trusted_Connection=yes;','exec distribution..sp_replmonitorhelppublication @@SERVERNAME,'DATA_BASE') AS a 

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---