The error comes from DB2, not SQL Server. I Googled below:
.
Perhaps each OPENQUERY opens a new connection (or what it is called in DB2 language)? One thing you
can check is the different settings for remote servers available with sp_configure. But you will
also want a DB" technician available to troubleshoot this.
<alingsjtu@gmail.comwrote in message news:1158839897.242268.248990@i42g2000cwa.googlegr oups.com...
Quote:
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
>
