Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2004
    Posts
    49

    Question Unanswered: 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

    THANKS

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    You 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.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    Apr 2004
    Posts
    49

    Question

    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.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  5. #5
    Join Date
    Apr 2004
    Posts
    49
    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.

  6. #6
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    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.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  7. #7
    Join Date
    Apr 2004
    Posts
    49
    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.

  8. #8
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    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.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  9. #9
    Join Date
    Apr 2004
    Posts
    49
    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

  10. #10
    Join Date
    Feb 2004
    Posts
    6
    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

  11. #11
    Join Date
    Apr 2004
    Posts
    49

    Smile

    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.

  12. #12
    Join Date
    Feb 2004
    Posts
    6
    "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.

  13. #13
    Join Date
    Apr 2004
    Posts
    49

    Arrow

    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.

  14. #14
    Join Date
    Apr 2004
    Posts
    49

    Arrow

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •