T-SQL Stored Procedure failing w/ message Msg 7321

Question

I'm having some issues with a stored procedure I've written in my SQL2000 database to query an Oracle database via OpenQuery().

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.

The query works for a while, then consistently fails after running successfully for a few thousand JobID's. It returns this error:

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].

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.

--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

Answer 1

I've an idea, why not just get rid of the cursor and all the dynamic stuff.

I would try something like this:

INSERT INTO [Part]  ([JobID],[PartID])
   SELECT oResults.JobID, oResults.PartID 
   FROM OpenQuery(linkedserver, 'SELECT DISTINCT JobID, PartID FROM OraclePartTable') as oResults
   INNER JOIN Job J ON oResults.JobID = J.JobID

Now you don't have to make "1000s" of connections -- just one. I imagine you won't have as many errors.

As a side benefit you get something that will run 1000s of times faster.

---

Update based on comment below

Given that the Oracle table has millions of records and you only want 10 of thousands of records I would try this (I'm guessing you are only looking at a sub-set jobs and this is reflected in the id ordering -- I'm sure there is a better way to do this if I knew more about your schema.)

DECLARE @maxID int
DECLARE @minID int

SELECT @maxID = max(JobID), @minID = min(JobID) FROM Job

INSERT INTO [Part]  ([JobID],[PartID])
   SELECT oResults.JobID, oResults.PartID 
   FROM OpenQuery(linkedserver, 'SELECT DISTINCT JobID, PartID ' +
                                'FROM OraclePartTable ' +
                                'WHERE JobID <= '+cast(@maxID as varchar(20)+
                                ' AND JobID >= '+case(@minID as varchar(20)) as oResults
   INNER JOIN Job J ON oResults.JobID = J.JobID

---

Update based on comment 2 below

I have some ideas but I don't know for sure. I've seen something like this in the ASP.NET connection pool. When I had that problem it was because connections were not being cleared correctly when they had an error. It would retry the connection and succeed on a connection, but the bad connection would be put back in the pool. Then when it got used again it would fail. It might be something like that.

Since it is not hard to try, I recommend trying the JobID range. See what happens. Or if there is some other criteria you can use to limit selecting all the rows you need (eg date) use that. I think one request is much better than many and I expect it will use much less time in total than the multiple queries.