title: INSERT INTO with dynamic pivot statement yields Unknown Table error in certain circumstances
body: <p>I'm using SQL Server and I have a dynamically built INSERT INTO statement in a stored procedure. The code works without a problem when I execute it manually, but when I execute the SP it returns an Unknown Table error where it tries to create the table from the select statement.</p>
  <p>The problem is only on the production server.  On the development server, it runs fine either way you execute it.  So on my development server, my web app is able to display the desired results.  But I'm unable to determine what settings or configuration is relevant and might be different. Development server is running a different version of SQL than production, but I'm unsure about which versions.</p>
  <p>What I'm trying to accomplish is this:  My database stores a certain type of form. Header information for each form submitted is stored in a parent table. Detailed line item answers are in the child table. (i.e. answers for each numbered line item: 1.01, 1.02, 1.03, etc.)  Each answer consists of a value indicating their Yes/No choice and a text field for notes/comments.  My customer needs to see the data in a form where one row displays all info for the submission. So I'm using a pivot to turn multi-row answers into columns on the same row.</p>
  <p>The reason I need to use dynamic SQL here is that there are actually different form types in the database. Each type has their own number of child rows where questions are broken out differently into sections and numbered accordingly. I've simplified my stored procedure shown here for readability.  But the procedure accepts a form type value and uses it to limit the results to only that type.  Otherwise, if the structure didn't vary, I would not have needed dynamic SQL.</p>
  <p>Here is the code:</p>
  <pre><code>ALTER PROCEDURE [dbo].[sp_Get_Assessment_List_Details] 
  
  AS
  BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
  
  
      -- ================================================================================================================================================
      --  Build tables that transposes answers and comments from the detail records into columns. 
      --  So multiple child records become single detail records with answer values across in columns.
      -- ================================================================================================================================================
      declare @collist nvarchar(max)
      declare @q nvarchar(max)
  
      -- Create a string containing all the line numbers for answers in this set.
      SET @collist = stuff((select distinct ',' + QUOTENAME(Dtl_Line_Number) 
                  FROM dbo.Form_Details as D
                  FOR XML PATH(''), TYPE
                  ).value('.', 'NVARCHAR(MAX)') 
              ,1,1,'')
  
      -- Once for answers
      set @q = '
      select * INTO _Temp_Form_Details from (
          select Dtl_Form_ID, Dtl_Line_Number, Dtl_Answer
          from ( select * from dbo.Form_Details ) as x
                    ) as source
      pivot (
          max(Dtl_Answer)
          for Dtl_Line_Number in (' + @collist + ')
      ) as pvt'
  
      exec (@q)
  
      -- Again for comments/notes
      SET @collist = stuff((select distinct ',' + QUOTENAME(Dtl_Line_Number)
                  FROM dbo.Form_Details as D
                  FOR XML PATH(''), TYPE
                  ).value('.', 'NVARCHAR(MAX)') 
              ,1,1,'')
      set @q = '
      select * INTO _Temp_Form_Details_Notes from (
          select Dtl_Form_ID, Dtl_Line_Number, Dtl_Comment
          from ( select * from dbo.Form_Details ) as x
                    ) as source
      pivot (
          max(Dtl_Comment)
          for Dtl_Line_Number in (' + @collist + ')
      ) as pvt'
  
      exec (@q)
  
  
      -- ================================================================================================================================================
      --  Finally, return the Form header data joined with the transposed detail columns
      -- ================================================================================================================================================
      -- Omitted here for simplicity
  
      DROP TABLE _Temp_Form_Details
      DROP TABLE _Temp_Form_Details_Notes
  
  END
  </code></pre>
  <p>To summarize again: When executed as a stored procedure (<code>exec sp_Get_Assessment_List_Details</code>), it returns the &quot;Unknown Table&quot; error on the first line that says &quot;<code>exec (@q)</code>&quot;.  So it doesn't seem to like the tables _Temp_Form_Details and _Temp_Form_Details_Notes.</p>
  <p>But if I highlight and execute the code manually in a query window, it runs fine and returns the pivoted results with no problem. Also, the error only occurs on the production server. On the development machine, it runs fine either way.</p>
  <p>I'm open to ways to correct this problem or other approaches that would accomplish the same goal.</p>
  <p>Thanks!</p>