NSERT 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

General SQL Server Forums

New to SQL Server Programming

NSERT EXEC statement cannot be nested.

Author

Topic

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2009-06-22 : 07:19:45

hi when i try to execute below query,the follwing error occurs,please help how to fix this issue.

Msg 8164, Level 16, State 1, Procedure Mis_allocation_T2_T1_sp, Line 77
An INSERT EXEC statement cannot be nested.


declare @test table(

sub_group         varchar(100),

cust_code_out     varchar(20),

cust_name_out     varchar(60),

cost_center_out   varchar(20),

head              varchar(50),

head_type         varchar(100),

aviation_out      numeric(13,8),

non_aviation_out  numeric(13,8),

sas_out                 numeric(13,8),

sub_total_out     numeric(13,8),

bpo_out                 numeric(13,8),

crm_out                 numeric(13,8),

grand_total_out   numeric(13,8),

label_out         varchar(100),

company_code_out  varchar(10),

currency_code_out varchar(10),

account_code_out  varchar(20),

fb_id_out               varchar(20))

 

insert into @test(sub_group,cust_code_out,cust_name_out,cost_center_out,head,head_type,aviation_out,non_aviation_out,sas_out,sub_total_out,

bpo_out,crm_out,grand_total_out,label_out,company_code_out,currency_code_out,account_code_out,fb_id_out)

exec Mis_allocation_main_sp 'All','All','01 apr 2009','30 jun 2009','USD','All','ALL'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-22 : 07:44:21

It means you have another INSERT INTO in your stored procedure Mis_allocation_main_sp

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-22 : 10:34:51

look into code for Mis_allocation_main_sp and spot any INSERT...EXEC's used

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2009-06-23 : 01:08:53

Txs for your reply,another insert statement is there,but i need to store the finaly output in a table by executing MIS_Allocation_main_sp.how to overcome this?

quote:
Originally posted by Peso

It means you have another INSERT INTO in your stored procedure Mis_allocation_main_sp

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-23 : 02:03:48

What happens in stored procedure Mis_allocation_main_sp?
Are there any inserts into tables other than temporary tables?

If there are not, you may overcome this be rewriting the sp as a function instead.

E 12°55'05.63"
N 56°04'39.26"

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2009-06-23 : 02:56:57

yes,inside MIS_ALLocation_main_sp there are 2 inner sp,the resultset is stored in table variable instead of temp table.

insert into @table1
exec sub_sp1

insert into @table2
exec sub_sp2

can i change table variable to #temp tables in replacing @table1,@table2 to #tmp1,#tmp2.so that the result of ,main sp [mis_allocation_main_sp] will get inserted in a table?

quote:
Originally posted by Peso

What happens in stored procedure Mis_allocation_main_sp?
Are there any inserts into tables other than temporary tables?

If there are not, you may overcome this be rewriting the sp as a function instead.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-23 : 03:06:17

No, not if you call two another SP with the main SP.
Have you tried the OPENROWSET alternative with a linked server to self?

E 12°55'05.63"
N 56°04'39.26"

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2009-06-23 : 03:20:25

yes, i tried and that too i faced some problem,which i posted in the below id:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=127987

quote:
Originally posted by Peso

No, not if you call two another SP with the main SP.
Have you tried the OPENROWSET alternative with a linked server to self?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-23 : 03:31:00

See my answer over at that thread how to [maybe] solve it.
What you want is a dynamic string for the OPENROWSET part, and then EXEC(@string)

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources