title: ADODB.Recordset error '800a0e78 Operation is not allowed when the object is closed: stored proc against temp table requiring SET NOCOUNT ON
body: <p>I'm exploring full text index searching using SQL Server 2008 and encounter two sets of errors. </p>
  
  <p>It stems from a  a stored procedure I call with VBScript which generates would generate search hit list recordset. The stored procedure runs fine in SQL Server Management studio and basically generates a search hit list. Arguments are keyword, and style for highlighting.</p>
  
  <p>Initially error: </p>
  
  <blockquote>
    <p><strong>Error One:</strong>  ADODB.Recordset error '800a0e78 Operation is not allowed when the object is closed</p>
  </blockquote>
  
  <p>at the <code>If not recordset.EOF</code> line in the to ASP code. Then a bit of reading and searching pointed  having <code>SET NOCOUNT ON;</code> especially when referencing temporary tables (<a href="http://support.microsoft.com/kb/q235340/" rel="nofollow">KB235340</a>).</p>
  
  <p>However when I specify <code>SET NOCOUNT ON</code> I get the error listed in "error two". NB regarding permissions I have EXECUTE permission assigned to the account running the stored procedure to highlight the search hits.</p>
  
  <blockquote>
    <p><strong>Error Two:</strong> Microsoft OLE DB Provider for SQL Serve error '80040e14'
    The user does not have permission to perform this action</p>
  </blockquote>
  
  <p>Error Two occurs when add the <code>SET NOCOUNT ON</code>.</p>
  
  <p>ASP Code: Line causing the error is highlighted</p>
  
  <pre class="lang-vb prettyprint-override"><code>    Dim cmd
      Dim newParameter
      Dim recordset
      Dim SearchTerm
      Dim Style
  
      SearchTerm = ""
      SearchTerm = Request("searchTerm")
      Style = "background-color:yellow; font-weight:bold"
  
      Dim objConnectionMICenter
      Set objConnectionMICenter = Server.CreateObject("ADODB.Connection") 
      objConnectionMICenter.Open Session("ConnectMICenter") 
  
      Set cmd  = Server.CreateObject("ADODB.Command")
      Set cmd.ActiveConnection = objConnectionMICenter
  
     ' Define the stored procedure's inputs and outputs
     ' Question marks act as placeholders for each parameter for the
     ' stored procedure
     cmd.CommandType = 4 ' adCmdStoredProc
     cmd.CommandText = "HelpAndCalculationNoteHighlight"
  
      '--- Create and append parameter for SearchTerm 
      Set newParameter = cmd.CreateParameter("SearchTerm",203 ,1,100,SearchTerm)
      cmd.Parameters.Append newParameter
  
      '--- Create and append parameter for SearchTerm 
      Set newParameter = cmd.CreateParameter("Style",203 ,1,200,Style)
      cmd.Parameters.Append newParameter
  
      Set recordset = cmd.Execute()
  
       **If not recordset.EOF Then**
          While Not recordset.EOF
              response.Write "&lt;div&gt;" &amp; recordset.Fields("Snippet") &amp; "&lt;/div&gt;"
              recordset.MoveNext
          Wend
      end if
  
      Response.Write strPreviewContents
  
      Set objConnectionMICenter = Nothing
      Set newParameter = Nothing
      Set cmd = Nothing
  
      recordset.Close
      Set recordset = Nothing
  </code></pre>
  
  <p>Stored Procedure:</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[HelpAndCalculationNoteHighlight]
  @SearchTerm nvarchar(100),
  @Style nvarchar(200)
  AS
  BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;
  
  CREATE TABLE #match_docs
  (
  doc_id bigint NOT NULL PRIMARY KEY
  );
  
  INSERT INTO #match_docs
  (
  doc_id
  )
  SELECT DISTINCT
  id
  FROM IntegratedHelpNotes_ChildSectionPage
  WHERE FREETEXT
  (
  content,
  @SearchTerm,
  LANGUAGE N'English'
  );
  
  -- Begin Second Block
  DECLARE @db_id int = DB_ID(),
  @table_id int = OBJECT_ID(N'IntegratedHelpNotes_ChildSectionPage'),
  @column_id int =
  (
  SELECT
  column_id
  FROM sys.columns
  WHERE object_id = OBJECT_ID(N'IntegratedHelpNotes_ChildSectionPage')
  AND name = N'content'
  );
  
  -- Begin Third Block
  SELECT
      s.id,
      MIN
      (
          N'...' + SUBSTRING
          (
              REPLACE
                  (   
                      c.content,
                      s.Display_Term,
                      N'&lt;span style="' + @Style + '"&gt;' + s.Display_Term + '&lt;/span&gt;'
                  ),
              s.Pos - 512,
              s.Length + 1024
          ) + N'...'
      ) AS Snippet
      FROM
      (
          SELECT DISTINCT
              c.id,
              w.Display_Term,
              PATINDEX
                  (
                      N'%[^a-z]' + w.Display_Term + N'[^a-z]%',
                      c.content
                  ) AS Pos,
              LEN(w.Display_Term) AS Length
          FROM sys.dm_fts_index_keywords_by_document
              (
                  @db_id,
                  @table_id
              ) w
          INNER JOIN dbo.IntegratedHelpNotes_ChildSectionPage c
              ON w.document_id = c.id
              WHERE w.column_id = @column_id
                  AND EXISTS
                      (
                          SELECT 1
                          FROM #match_docs m
                          WHERE m.doc_id = w.document_id
                      )
                  AND EXISTS
                  (
                      SELECT 1
                      FROM sys.dm_fts_parser
                          (
                              N'FORMSOF(FREETEXT, "' + @SearchTerm + N'")',
                              1033,
                              0,
                              1
                          ) p
                      WHERE p.Display_Term = w.Display_Term
                      )
                  ) s
              INNER JOIN dbo.IntegratedHelpNotes_ChildSectionPage c
              ON s.id = c.id
              GROUP BY
              s.id;
  DROP TABLE #match_docs;
  END;
  </code></pre>