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

The error comes from DB2, not SQL Server. I Googled below:
SQLSTATE 57030

And found for instance http://dbforums.com/t521957.html.

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.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/


<alingsjtu@gmail.com> wrote in message news:1158839897.242268.248990@i42g2000cwa.googlegr oups.com...
> 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
>

Yes, it comes from DB2. But I think the reason is every SQL Server
OPENQUERY statement can't timely release connection then lead to this
problem.

So wether there are some ways to get OPENQUERY release their
connection? Changing Query timeout of LInked Server is not reasonable
because query takes long time depending on DB2 data scale.

Thanks for your reply.

Tibor Karaszi wrote:
> The error comes from DB2, not SQL Server. I Googled below:
> SQLSTATE 57030
>
> And found for instance http://dbforums.com/t521957.html.
>
> 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.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
>
> <alingsjtu@gmail.com> wrote in message news:1158839897.242268.248990@i42g2000cwa.googlegr oups.com...
> > 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
> >

(alingsjtu@gmail.com) writes:
> Yes, it comes from DB2. But I think the reason is every SQL Server
> OPENQUERY statement can't timely release connection then lead to this
> problem.

This is not impossible, as OLE DB employs connection pooling. That is,
if you connect to a server and disconnect, OLE DB lingers to the connection
for some time, typically 60 seconds. A new connection with the same
properties will reuse that connection.

To verify that there may be a problem with connection pooling run a
query with OPENQUERY from SQL Server, and look in DB2 if this cause a
new connection. Then run a new query from SQL Server and see if you get
a new connection. (Note: I have never seen DB2, so I cannot assist with
that part.)

In the definition of the linked server, you can try to add this to the
connection string:

"OLE DB Services = -2;"

This turns off connection pooling. But be careful with this, as could
result in DB2 being swamped in connection attempts when you run your
cursor.

A better approach may be to replace the cursor with a single query,
but without the details, it's difficult to say how this should be done.
But generally, running cursors is very expensive.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Thanks, Erland.

I can't use a single query, because linked DB2 server has large scale
data (million level). I had practised to join linked DB2 server tables
with local table, the result was very frustrating, the performance very
low, because local SQL Server fetched all data from linked DB2 server,
then executed the distributed query.

I used the IBM DB2 OLE DB Provider instead of Microsoft OLE DB Provider
For DB2.
Don't know whether this provider support "OLE DB Services = -2;"
property. I should wait to next Monday to test.

Do you think whether things will be better if I use Microsoft OLE DB
Provider For DB2?
And where to get the Microsoft OLE DB Provider For DB2? Could you give
me a link?

Many Thanks.

Tibor Karaszi wrote:
> The error comes from DB2, not SQL Server. I Googled below:
> SQLSTATE 57030
>
> And found for instance http://dbforums.com/t521957.html.
>
> 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.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
>
> <alingsjtu@gmail.com> wrote in message news:1158839897.242268.248990@i42g2000cwa.googlegr oups.com...
> > 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
> >

(alingsjtu@gmail.com) writes:
> I used the IBM DB2 OLE DB Provider instead of Microsoft OLE DB Provider
> For DB2.
> Don't know whether this provider support "OLE DB Services = -2;"
> property. I should wait to next Monday to test.

Since this property is in OLE DB Core Services, I would expect so,
although I cannot vouch for it.

> Do you think whether things will be better if I use Microsoft OLE DB
> Provider For DB2?

Didn't I say that I have no experience of DB2? I don't want to speculate
about software I don't know about. But if you try the other provider,
you may want to try to run the full query again. If the provider provides
the SQL Server optimizer with better statistics, the query plan may be
better.

> And where to get the Microsoft OLE DB Provider For DB2? Could you give
> me a link?

A couple of clicks at www.google.com lead me to:
http://www.microsoft.com/downloads/d...d60-a13c-4479-
9b91-9e8b9d835cdc&displaylang=en


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx