title: T-SQL Stored Procedure failing w/ message Msg 7321
body: <p>I'm having some issues with a stored procedure I've written in my SQL2000 database to query an Oracle database via OpenQuery().</p>
  
  <p>The procedure 
  1) creates a cursor from a table (Job) on my SQL2000 database, 
  2) then iterates through that cursor one at a time, passing the cursor value (JobID) to a OpenQuery().
  3) for each JobID in the Job table, it will execute the OpenQuery to query the Oracle database to get data for that JobID. 
  4)The returned data from each OpenQuery execution is inserted into a table called Part in my SQL2000 database.</p>
  
  <p>The query works for a while, then consistently fails after running successfully for a few thousand JobID's. It returns this error:</p>
  
  <p>Server: Msg 7321, Level 16, State 2, Line 1
  An error occurred while preparing a query for execution against OLE DB provider 'MSDAORA'. 
  OLE DB error trace [OLE/DB Provider 'MSDAORA' ICommandPrepare::Prepare returned 0x800706be].</p>
  
  <p>Any idea what I could be doing wrong? On my most recent run, it ran successfully for 16,186 loops through the cursor before failing. The JobID that failed was only 1 integer larger than the previous JobID that was successful and it didn't return a much larger query result either, only one more row.</p>
  
  <pre><code>--DECLARE Variables
  DECLARE @JobID      int
  DECLARE @OpenQuerySQL       varchar(6000)
  DECLARE @INSERTSQL          varchar(500)
  DECLARE @Count              int
  
  
  --INSERT SQL stays static 
  --PRINT 'SET @INSERTSQL' 
  SET @Count = 0
  SET @INSERTSQL = 'INSERT INTO [Part]
             ([JobID]
             ,[PartID]'
  
      BEGIN
  
          --Declare cursor
          DECLARE JobCursor CURSOR FOR
              SELECT DISTINCT JobID
              FROM Job
              ORDER BY JobID
  
          --Open the cursor and get first Job for Program
          OPEN JobCursor
          FETCH NEXT FROM JobCursor
              INTO @JobID
  
          WHILE @@FETCH_STATUS = 0
              BEGIN
                  SET @Count = @Count + 1
                  SET @OpenQuerySQL = 'SELECT * FROM OpenQuery(linkedserver, ''' + 
                                      'SELECT DISTINCT JobID, PartID
                                       FROM OraclePartTable
                                      WHERE JobID = ' + CAST(@JobID AS varchar(20)) + ''')'
  
                      PRINT CAST(@Count AS varchar(9)) + ') JobID = ' + CAST(@JobID AS varchar(13))
                      PRINT @OpenQuerySQL
                      EXEC (@INSERTSQL + @OpenQuerySQL)
                      FETCH NEXT FROM JobCursor
                      INTO @JobID
                  END
  
  
          CLOSE JobCursor
          DEALLOCATE JobCursor
      END
  </code></pre>