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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
11752 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
11752 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
11752 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
11752 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  
 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.09 seconds. Powered By: Snitz Forums 2000