An INSERT EXEC statement cannot be nested error.

Hi,all,
When I use following sql, an error occurs:
insert into #tmprep
EXECUTE proc_stat @start,@end

There is a "select * from #tmp " in stored procedure proc_stat, and the
error message is :

Server: Msg 8164, Level 16, State 1, Procedure proc_stat, Line 42
An INSERT EXEC statement cannot be nested.

What's the metter? Any help is greatly appreciated. Thanks

Could you provide some code for use. A adhoc solution would be to
consider if you can use a UD(T)F for that to retrieve the data from.

HTH, jens Suessmeyer.

Scarab (pest9998@hotmail.com)  writes:
> When I use following sql, an error occurs:
>     insert into #tmprep
>     EXECUTE proc_stat @start,@end
>
> There is a "select * from #tmp " in stored procedure proc_stat, and the
> error message is :
>
> Server: Msg 8164, Level 16, State 1, Procedure proc_stat, Line 42
> An INSERT EXEC statement cannot be nested.
>
> What's the metter? Any help is greatly appreciated. Thanks

Yes, that's a restriction with INSERT-EXEC. See this article for
alternative solutions: http://www.sommarskog.se/share_data.html.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx