An INSERT EXEC statement cannot be nested error.

[Scarab]

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

[Jens]

Re: An INSERT EXEC statement cannot be nested error.

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.

[Erland Sommarskog]

Re: An INSERT EXEC statement cannot be nested error.

Scarab (pest9998@hotma il.com) writes:[color=blue]
> 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[/color]

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@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx