An INSERT EXEC statement cannot be nested.

• SeeCoolGuy

  SSCrazy

  Points: 2902

  More actions

  July 15, 2004 at 1:41 pm

  #108500

  Here is the issue

  I have a sproc that "spits" back a list of data, I need a few fields out of the result, however I already use the technique of inserting into a temp table out of a sproc within a sproc...

  so I'd like to be able to do the old

  INSERT INTO #TEST (Field1,Field2,Field3)

  Exec Sproc_Favorites @Parameter1

  but now I get this error:

  Server: Msg 8164, Level 16, State 1, Procedure stp_MyFavorites, Line 210

  An INSERT EXEC statement cannot be nested.

  any Ideas?

  -- Francisco

• JourneymanProgrammer

  SSCommitted

  Points: 1546

  More actions

  July 15, 2004 at 3:39 pm

  #514762

  hey francisco,

  my suggestions would be the following:

  (1) Try getting rid of the INSERT statements inside the proc

  (2) If suggestion 1 is not feasible, then the other option is to try to do an OPENQUERY on the sproc exec call.

    2i) First, you would need to add a linked server that points to itself by calling the following system stored proc:  sp_addlinkedserver @@SERVERNAME

  2ii) Then, you would need to modify your INSERT statement to look like this:

  INSERT INTO #TEST (Field1,Field2,Field3)

  SELECT * FROM OPENQUERY(@@SERVERNAME, 'EXEC Sproc_Favorites ' + @Parameter1)

  (Note: If @Parameter1 is not some sort of string, you would need to cast it as such)

   

   

• SeeCoolGuy

  SSCrazy

  Points: 2902

  More actions

  July 16, 2004 at 2:58 pm

  #514958

  JourneymanProgrammer, thanks for the ideas, I ended up using a 2nd sproc that does all but the actual INSERT, so for now I'm over this... I'm going to SAVE, your suggestion just in case this problem re-crops back up and there is no other way to do this....

  ( oops sorry I typed in grasshopper thinking that was the username sorry about that did not mean to offend anyone, thanks for the tip tho )

  -- Francisco

• gljjr

  SSChampion

  Points: 10080

  More actions

  July 16, 2004 at 3:14 pm

  #514960

  Francisco,

  You might think about the possibility of turning the sproc into a udf. In this way you would then simply do the following.

  INSERT INTO #Foo(...)

  SELECT ... FROM dbo.f_YourNewUDF(...)

   

  
  
  
  Gary Johnson
  Microsoft Natural Language Group
  DBA, Sr. DB Engineer

  This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

• SeeCoolGuy

  SSCrazy

  Points: 2902

  More actions

  July 16, 2004 at 4:53 pm

  #514972

  Thanks,

  I tried the udf route, but it simply was not feasable. Here is the situation

  (*caution more detail*)

  The primary sproc calls a *dos* command line utility, I INSERT INTO a temp table to capture the results of the *dos* command, and then feed the results into the response from the sproc. This is why I did an INSERT INTO in this sproc.

  I have not investigated the sp_OAxxx route, as I'm not sure how to negotiate the security permissions as opposed to the app role that is currently assigned to the primary sproc.

  (*END Details*)

  Thanks,

  -- Francisco

• schleep

  SSChampion

  Points: 12585

  More actions

  July 20, 2004 at 9:03 am

  #515287

  Another suggestion is output paramaters to the sproc, if that can be applied in this situation.

  P