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

Transact-SQL (2000)

An INSERT EXEC statement cannot be nested

Author

Topic

dejjan
Yak Posting Veteran

99 Posts

Posted - 2005-08-29 : 05:18:07

Hello,
does anybody know why I am getting this error:
"An INSERT EXEC statement cannot be nested.
insert into #tmp
execute dbName..sp_spaceused TableName"

All I wanna do is:

insert into tbl_ListOfTables
EXEC sp_ListOfTables 'dbName'

and I use one temp table #tmp in sp_ListOfTables:
insert into #tmp
execute dbName..sp_spaceused TableName
(I have one cursor for all tables in specific database)

thanks for any help

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-29 : 05:29:41

In sp_ListOfTables procedure, dont assign to table
Use this only
execute dbName..sp_spaceused TableName

Madhivanan

Failing to plan is Planning to fail

dejjan
Yak Posting Veteran

99 Posts

Posted - 2005-08-29 : 05:46:07

But I want to have a list of all tables ordered by rows desc. So, I made a cursor and did a sp_spaceused for every tables. I input row by row into #tmp and then do a select statement.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-29 : 05:48:06

Dont use temporary table
After you run the sp, Do
select * from yourTable

Madhivanan

Failing to plan is Planning to fail

dejjan
Yak Posting Veteran

99 Posts

Posted - 2005-08-29 : 06:08:13

I don't like that combination - to keep table in DB because I don't need it and I don't want it ... but OK. If it is the only solution I must accept that.
I still don't understand why can't use #tmp table.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-29 : 08:51:09

The temp table lost its scope as soon as sp is executed
Your method will try to insert records to two different table using Exec at the same time
I think having the table and querying it is the effient way

Madhivanan

Failing to plan is Planning to fail

dejjan
Yak Posting Veteran

99 Posts

Posted - 2005-08-29 : 09:39:15

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources