An INSERT EXEC statement cannot be nested

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

An INSERT EXEC statement cannot be nested

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-06-10 : 08:32:39

joseph writes "hello
i have a problem with this proc

CREATE PROCEDURE dbo.tmpInsert AS
set nocount on
create table #tmp (a int)
insert into #tmp
exec('select cast(3 as int) aa')
select a from #tmp
set nocount off
drop table #tmp
GO

when i use this code via any procedures

create table #tmp (a int)
insert into #tmp
exec [test].[dbo].[tmpInsert]
drop table #tmp

i got this error:
Server: Msg 8164, Level 16, State 1, Procedure tmpInsert, Line 5
An INSERT EXEC statement cannot be nested.
BUT i found that the following code works great

create table #tmp (a int)
insert into #tmp
SELECT a.*
FROM OPENROWSET('MSDASQL',
'DRIVER={SQL Server};SERVER=.;UID=sa;PWD=admin818',
' set fmtonly off exec [test].[dbo].[tmpInsert] ') AS a

select a from #tmp

drop table #tmp

I looked up "set fmtonly off" on BOL but i did not any entry .
can you explain it?is it work in multi user invironment good?

Thanks in advance

-yousef"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-06-10 : 08:51:14

I think you need to review the concepts of temp table scope. Maybe you mean #tmp in the proc to be different to #tmp in the dml ...

Jay White
{0}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources