Hello, every body.

When execute dynamic generated multiple OPENQUERY statements (which
linkes to DB2)  in SQLServer, I always got SQL1040N The maximum number
of applications is already connected to the database. SQLSTATE=57030.

Background:
I created a linked server to DB2 8.1 database which called
GRR_DB2Server. In my stored procedure p_FetchRawData, I need to read
some data from this linked server GRR_DB2Server and insert them into
local SQLServer table SQLServer_A.

Query to GRR_DB2Server joins 3 large DB2 tables DB2_A, DB2_B, DB2_C
(every table has about 1 million records), and part of the query
condition stored as record in table SQLServer_B in local SQLServer.

At first I directly join these 4 tables in one T-SQL statements, but to
my disappointment I found the performance very low afer some practice.

So I changed the T-SQL to use cursor to loop for fetching every row
data in SQLServer_D condition table to some procedure variables, and
then in this loop I generated dynamic T-SQL string which orgnize the
condition and form one OPENQUERY statement.

The pseud code something like this (just pseud code, in case someone
will question the pseud code validity):

CREATE PROCEDURE p_FetchRawData variable_list
AS
BEGIN
....
 DECLARE condition_cursor CURSOR LOCAL  FORWARD_ONLY  FOR
      SELECT * FROM local_condition_table

 OPEN condition_cursor
 FETCH NEXT FROM condition_cursor INTO
      local_variables

 WHILE @@FETCH_STATUS = 0
 BEGIN
     SET @Dynamic_SQL = 'SET IMPLICIT_TRANSACTIONS OFF INSERT INTO
SQLServer_A SELECT * FROM OPENQUERY (GRR_DB2Server, ' + @Dynamic_STR +
')'
     EXEC (@Dynamic_SQL)

     FETCH NEXT FROM condition_cursor INTO
        local_variables
 END
....
END

But when execute this stored procedure p_FetchRawData, when the loop
count is too big, then I got the error:
[OLE/DB provider returned message:  SQL1040N
The maximum number of applications is already connected to the
database. SQLSTATE=57030]
OLE DB error trace [OLE/DB Provider 'IBMDADB2'
IDBInitialize::Initialize returned 0x80040e69].

I understood this error meaning which said too many OPENQUERY
connection. I just wonder why every DYNAMIC T-SQL EXECECUTION keeps
their connections to linked server? How to fail these connections when
every OPENQUERY execution finished?

Thanks.

Regards,
Ling, Xiao-li