ERROR:- An INSERT EXEC statement cannot be nested.

HI,

WELL WE HAVE BEEN TRYING TO AUTOMATE A PROCEDURE OUT HERE,AND WE ARE TRYING TO CONVERT MOST OF THE THINGS INTO PROCEDURES.

BUT WE ARE GETTING A FEW HICCUPS. PLS HELP

THIS IS HOW IT GOES :-

CREATE PROCEDURE MY_PROC1
AS
BEGIN
ST1 .......;
ST2........;
END

CREATE PROCEDURE MY_PROC2
AS
BEGIN

CREATE TABLE #TMP2
(COL1 DATATYPE
COL2 DATATYPE)

INSERT INTO #TMP2
EXEC MY_PROC1

ST1 .......;
ST2........;

END

THIS PROCEDURE TOO RUNS WELL ,AFTER TAKING THE DATA FROM THE FIRST PROC IT MANIPUATES THE DATA ACCORDING TO THE CRITERIA SPECIFIED

NO PROBLEM TILL NOW......

BUT,

CREATE PROCEDURE MY_PROC3
AS
BEGIN

CREATE TABLE #TMP3
(COL1 DATATYPE
COL2 DATATYPE)

INSERT INTO #TMP3
EXEC MY_PROC2

ST1 .......;
ST2........;

END

THEN IT GIVES AN ERROR AS :-

"An INSERT EXEC statement cannot be nested."

CAN'T WE , FROM A PROCEDURE CALL A PROCEDURE WHICH CALLS A PROCEDURE......

WHAT IS THE NESTING LEVEL OF A PROCEDURE ?

IS THERE ANY WAY AROUND IT OR CAN IT BE DONE BY CHANGING SOME SETTINGS ?

PLS HELP ME OUT IN THIS

THANKSYou can nest stored procedures pretty deep (I think it's 32 levels or so). What you cannot do is have an INSERT #TMP3 EXEC proc1 in one procedure and have the next procedure that calls it with an INSERT #TEMP EXEC proc2.

You have to architect around this limitation. There is no setting to change the above that I'm aware of.|||Hi,

Thanks Derrick, But Is There A Way You Know To Get Around This One.

I Could Use Permanent Tables To Get Around This But It Takes Too Much Space,which Is A Constraint In Our Case.

Have To Use Temp Table :-

Is There A Way Around It ,as I Am Using The Results Of The First Procedure To Drive The Second One And The Results Of The Second One To Drive The Third .

Pls , If You Know Of Anything Pls Let Me Know.

Thanks.|||Without manual intervention, one level of INSERT...EXECUTE is the limit.

To do what you want, you really need an N-tier server. You can sort of kludge it via multiple instances, but you run out of RAM pretty quickly. You can also kludge it by compounding your cursor (rolling up all of the logically nested SELECTs into a single monster query).

There are a number of choices available, but due to your tight restrictions on RAM and disk, very few of those choices make good sense.

-PatP|||Hi,

I Got The Point Of Set -level Processing Rather Than Row-level Processing As You Had Said Before.so I Might Do Away With The Cursor Thing Altogether.but Is There Any Way To Use Mulitple Insert..exec Statement.

Now,that I Am Not Using Cursors ,i Don't Think Ram And Disk Matters Much Now.

Is There Any Way Out Of It Now ? Pls Do Help

Thanks.|||No. That's what we're trying to tell you. What you can do is have multiple queries that INSERT into a regular "process" table, which is just a normal table that records processes. You can then have a wrapper query that runs these in order. As long as all of your inserts are occurring on the second level, as opposed to the first order of queries meaning the wrapper query, you can run these infinitely.

Our EMC SnapClone process uses this methodology.|||Hi,

Thanks Derrick For The Help,but Frankly Speaking Being Still A Newcomer In The Field Of Ms-sql Databases.some Of The Words You Have Said Have Escaped My Vivid Imagination. Could You Pls Explain Me.

First,

What Do You Mean By A Wrapper Query ?

Second,

How To Implement It In Out Here ?

Could You Pls Explain This ?

Thanks.|||First,

What Do You Mean By A Wrapper Query ?
--This is just a query that call several subqueries and has overall control of a process.

Second,

How To Implement It In Out Here ?

Have each query right the results to a regular table. The query controls the overall process by reading these tables and deciding which query it needs to run next. As long as you stay one level under the wrapper query, you can run as many of these INSERT EXEC statements as you need to. If you post your query, I should be able to help you out more.|||DROP PROCEDURE PROC1
CREATE PROCEDURE PROC1
AS
BEGIN
SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC
FROM CUSTOMER A
INNER JOIN CUSTOMERPREFERENCE B
ON A.INTCUSTOMERID = B.INTCUSTOMERID
INNER JOIN TMPREFERENCE C
ON B.INTPREFERENCEID = C.INTPREFERENCEID
WHERE B.INTPREFERENCEID IN (6,7,2,3,12,10)
ORDER BY B.INTCUSTOMERID
END

DROP PROCEDURE PROC2
CREATE PROCEDURE PROC2
AS
BEGIN

CREATE TABLE #SAATHI(INTCUSTOMERID INT,CHREMAIL NVARCHAR(60),INTPREFERENCEID INT,CHRPREFERENCEDESC NVARCHAR(50))

INSERT INTO #SAATHI
EXEC PROC1

SELECT A.INTCUSTOMERID,MAX(case when A.intpreferenceid = 6 then '1'
else '0' end) +
MAX(case when A.intpreferenceid = 7 then '1'
else '0' end) +
MAX(case when A.intpreferenceid = 2 then '1'
else '0' end) +
MAX(case when A.intpreferenceid = 3 then '1'
else '0' end) +
MAX(case when A.intpreferenceid = 12 then '1'
else '0' end) +
MAX(case when A.intpreferenceid = 10 then '1'
else '0' end) AS PREFER
FROM #SAATHI A
GROUP BY A.INTCUSTOMERID
ORDER BY A.INTCUSTOMERID
END

DROP PROCEDURE PROC3
CREATE PROCEDURE PROC3
AS
BEGIN
CREATE TABLE #SAATH2(INTCUSTOMERID INT,TOTAL_COUNTS INT)

INSERT INTO #SAATH2
EXEC PROC2

DECLARE @.EKEK INT
DECLARE @.KAUNSARE VARCHAR(100)
SET @.EKEK = 1
SET @.KAUNSARE = 'International Pop'
WHILE @.EKEK <= 3
BEGIN
SELECT @.KAUNSARE AS NAAMRE,COUNT(*) AS TOTAL_COUNTS
FROM SAATH
WHERE SUBSTRING(PREFER,@.EKEK,1) = 1
GROUP BY SUBSTRING(PREFER,@.EKEK,1)
SET @.EKEK = @.EKEK + 1
IF @.EKEK=2
BEGIN
SET @.KAUNSARE = 'International Rock'
END
IF @.EKEK=3
BEGIN
SET @.KAUNSARE = 'Hindi Pop'
END
END
END

OUT HERE, THE PROBLEM ARISES IN PROC3 ,

THE THING IS THE RESULT OF 1 PROC IS I/P TO SECOND ONE AND THE RESULT OF 2 PROC IS I/P TO THIRD ONE.

COULD YOU DO SOMETHING ABOUT IT ?

HOW COULD WE IMPLEMENT WRAPPER QUERY OUT HERE, AS MAINTAINING ALL 3 PROCS ARE NECESSARY AS THEY ARE ALSO I/P'S TO OTHER PROCS.

AND SO ON ....

THANKS|||Here is an example of how to do it. ME is a linked server back to the same.

create proc a
as
select a = 'a'
go

create proc b
as
create table #b (b varchar(32) not null)
insert #b exec ME.master.dbo.a
select * from #b
go

create proc c
as
create table #c (c varchar(32) not null)
insert #c exec ME.master.dbo.b
select * from #c
go

create proc d
as
create table #d (d varchar(32) not null)
insert #d exec ME.master.dbo.c
select * from #d
go

exec d
go|||HI,

WHAT DO YOU MEAN BY :-
"ME is a linked server back to the same."

AND ANY WAY IF I AM USING : -

create proc a
as
select a = 'a'
go

create proc b
as
create table #b (b varchar(32) not null)
insert #b exec a
select * from #b
go

create proc c
as
create table #c (c varchar(32) not null)
insert #c exec b
select * from #c
go

create proc d
as
create table #d (d varchar(32) not null)
insert #d exec C
select * from #d
go

exec d
go

I AM GETTING AN ERROR WHICH IWAS GETTING BEFORE :-

"An INSERT EXEC statement cannot be nested."

HASN'T MADE MUCH DIFFERENCE ?

WHAT IS ME .. IS IT PRESENT IN ALL OF THE SQL SERVER 2000 ?

OR IS IT SOMETHING VIRTUAL THAT YOU HAVE CREATED ?

THANKS.|||"ME" is a linked server. In Enterprise Manager, open the Security folder, right click on Linked Servers, choose New Linked Server from the menu. I named the server "ME." Select "Microsoft OLE DB Provider for SQL Server" as the provider. Enter the actual name of your server in the Data Source field. On the security tab, select "Be made using the login's current security context." On the server options tab, check all of the check boxes.|||HI,

I AM GETTING AN ERROR THAT SAYS :-

"Could not find stored procedure 'master.dbo.a'.
Could not relay results of procedure 'a' from remote server 'ME'."

COULD YOU PLS EXPLAIN ME THE FUNDA OF LINKED SERVERS .?

I AM JUST BLINDLY F9OLLOWING YOU. AND GETTING NOWHERE .

PLS EXPLAIN IT TO ME.

THANKS,

CHETAN B.|||HI,

I AM GETTING AN ERROR THAT SAYS :-

"Could not find stored procedure 'master.dbo.a'.
Could not relay results of procedure 'a' from remote server 'ME'."

COULD YOU PLS EXPLAIN ME THE FUNDA OF LINKED SERVERS .?

I AM JUST BLINDLY FOLLOWING YOU. AND GETTING NOWHERE .

PLS EXPLAIN IT TO ME.

THANKS,

CHETAN B.