SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 an INSERT EXEC statement cannot be nested
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

azmi
Starting Member

Malaysia
37 Posts

Posted - 11/11/2005 :  20:05:58  Show Profile  Reply with Quote
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

Slovenia
11751 Posts

Posted - 11/11/2005 :  20:13:19  Show Profile  Visit spirit1's Homepage  Reply with Quote
does your as_mie_tmp table have the same columns as your sproc returns?



Go with the flow & have fun! Else fight the flow
Go to Top of Page

azmi
Starting Member

Malaysia
37 Posts

Posted - 11/11/2005 :  20:16:52  Show Profile  Reply with Quote
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..
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 11/11/2005 :  20:21:18  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

azmi
Starting Member

Malaysia
37 Posts

Posted - 11/11/2005 :  20:22:47  Show Profile  Reply with Quote
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..
Go to Top of Page

azmi
Starting Member

Malaysia
37 Posts

Posted - 11/11/2005 :  20:29:33  Show Profile  Reply with Quote
how to use global tmp table in my HTX_Aged_Receivable_Rpt sproc. izzit i have to change # table to permenent tmp table?
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 11/11/2005 :  20:37:12  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

azmi
Starting Member

Malaysia
37 Posts

Posted - 11/11/2005 :  20:41:26  Show Profile  Reply with Quote
thanks for your information. i will try your suggestion...
Go to Top of Page

azmi
Starting Member

Malaysia
37 Posts

Posted - 11/18/2005 :  03:15:03  Show Profile  Reply with Quote
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..
Go to Top of Page

azmi
Starting Member

Malaysia
37 Posts

Posted - 11/21/2005 :  03:17:59  Show Profile  Reply with Quote
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.
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 11/21/2005 :  04:14:07  Show Profile  Visit spirit1's Homepage  Reply with Quote

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


Go with the flow & have fun! Else fight the flow
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000