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

azmi
Starting Member

37 Posts

Posted - 2005-11-11 : 20:05:58

hi guy...
i need your assist to overcome error message 'an INSERT EXEC statement cannot be nested' when i conmpile my sproc. Below is sproc (simplified) to explained the error message looks like..
(1)
------------------------------
create procedure HTX_ar_rep_ledger_test
@company_no GLCOMPANY,
@company_locn GLLOCN,
@languageid CMN_LANGID = 1,
@cust_no cmn_cust_ven_code,
@cust_no1 cmn_cust_ven_code,
@from_date char(10),
@to_date char(10))
as
begin
..
..
insert into #aged_calc
EXEC salesdb..HTX_Aged_Receivable_Rpt @company_no,@company_locn, @languageid, @cust_no,@cust_no,@to_date
..
..
end

(2)
-----------------------------------
create procedure HTX_Smt_of_Acct_by_Role
( @company_no GLCOMPANY,
@company_locn GLLOCN,
@languageid CMN_LANGID = 1,
@cust_no cmn_cust_ven_code,
@cust_no1 cmn_cust_ven_code,
@from_date char(10),
@to_date char(10))
AS
begin
..
..
..
if (condition)
begin
raiserror ('You Dont Have An Authorised To View This Customer!!!')
end
else
begin
INSERT INTO as_mie_tmp
EXEC dbo.HTX_ar_rep_ledger_asmie @company_no,@company_locn,@languageid,@cust_no,@cust_no1,@from_date,@to_date
end
end
---------------
-in sproc (1) i already use insert exec statement.
-in sproc (2) i use again insert exec statement cause in want to populate result to as_mie_tmp tmp table

do you have any idea and other option how to workaroud with this sproc to avoid this error.Thanks..

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-11-11 : 20:13:19

does your as_mie_tmp table have the same columns as your sproc returns?

Go with the flow & have fun! Else fight the flow

azmi
Starting Member

37 Posts

Posted - 2005-11-11 : 20:16:52

ops.sory there is some mistake sproc no (2). the sproc look like this...

create procedure HTX_Smt_of_Acct_by_Role
( @company_no GLCOMPANY,
@company_locn GLLOCN,
@languageid CMN_LANGID = 1,
@cust_no cmn_cust_ven_code,
@cust_no1 cmn_cust_ven_code,
@from_date char(10),
@to_date char(10))
AS
begin
..
..
..
if (condition)
begin
raiserror ('You Dont Have An Authorised To View This Customer!!!')
end
else
begin
INSERT INTO as_mie_tmp
EXEC HTX_ar_rep_ledger_test @company_no,@company_locn,@languageid,@cust_no,@cust_no1,@from_date,@to_date
end
end

the sproc (2) exec again the sproc (1) within sproc..I hope u understand...thanks..

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-11-11 : 20:21:18

ohh i see your calling the first sproc in the second sproc...
hmm... i didn't know that you can't nest them like that...

maybe you could use global temp tables in your HTX_Aged_Receivable_Rpt sproc. that way you wouldn't need to do insert into exec

Go with the flow & have fun! Else fight the flow

azmi
Starting Member

37 Posts

Posted - 2005-11-11 : 20:22:47

thnks spirit1 for reply.yes as_mie_tmp temp table return same column as sproc returns. I've read read some article that we can use other method like 'openquery' or using dynamic sql.Do u know about that..

azmi
Starting Member

37 Posts

Posted - 2005-11-11 : 20:29:33

how to use global tmp table in my HTX_Aged_Receivable_Rpt sproc. izzit i have to change # table to permenent tmp table?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-11-11 : 20:37:12

use ##temp instead of #temp
##temp's get dropped when all of the connections that use them aren't closed.
they're preety usefull for your problem. and you don't need openquery.

Go with the flow & have fun! Else fight the flow

azmi
Starting Member

37 Posts

Posted - 2005-11-11 : 20:41:26

thanks for your information. i will try your suggestion...

azmi
Starting Member

37 Posts

Posted - 2005-11-18 : 03:15:03

i had tried using ##tmp as you are suggested, but the error message still persist. Can i have an example stored proc regarding with this problem..

azmi
Starting Member

37 Posts

Posted - 2005-11-21 : 03:17:59

hi guys, i need your expertise to solve this problem. Anyone can come out with best solution. I 've spend a week try to solve but still not found the solution. Your attention is much appreciated.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-11-21 : 04:14:07

[code]
use northwind
go
create proc test1
as

select orderid, customerid, employeeid from ##temp2

go
create proc test2
as
create table ##temp2 (orderid int , customerid char(5), employeeid int)
insert into ##temp2 exec test3
go

create proc test3
as
select top 100 orderid, customerid, employeeid from orders
go

exec test2
exec test1

go
drop proc test1, test2, test3
drop table ##temp2
go
[/code]

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources