473,695 Members | 1,853 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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.

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_curso r CURSOR LOCAL FORWARD_ONLY FOR
SELECT * FROM local_condition _table
OPEN condition_curso r
FETCH NEXT FROM condition_curso r INTO
local_variables
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Dynamic_SQL = 'SET IMPLICIT_TRANSA CTIONS OFF INSERT INTO
SQLServer_A SELECT * FROM OPENQUERY (GRR_DB2Server, ' + @Dynamic_STR +
')'
EXEC (@Dynamic_SQL)

FETCH NEXT FROM condition_curso r 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

Sep 21 '06 #1
5 12260
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/
<al*******@gmai l.comwrote in message news:11******** **************@ i42g2000cwa.goo glegroups.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_curso r CURSOR LOCAL FORWARD_ONLY FOR
SELECT * FROM local_condition _table
OPEN condition_curso r
FETCH NEXT FROM condition_curso r INTO
local_variables
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Dynamic_SQL = 'SET IMPLICIT_TRANSA CTIONS OFF INSERT INTO
SQLServer_A SELECT * FROM OPENQUERY (GRR_DB2Server, ' + @Dynamic_STR +
')'
EXEC (@Dynamic_SQL)

FETCH NEXT FROM condition_curso r 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
Sep 21 '06 #2
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/
<al*******@gmai l.comwrote in message news:11******** **************@ i42g2000cwa.goo glegroups.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_curso r CURSOR LOCAL FORWARD_ONLY FOR
SELECT * FROM local_condition _table
OPEN condition_curso r
FETCH NEXT FROM condition_curso r INTO
local_variables
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Dynamic_SQL = 'SET IMPLICIT_TRANSA CTIONS OFF INSERT INTO
SQLServer_A SELECT * FROM OPENQUERY (GRR_DB2Server, ' + @Dynamic_STR +
')'
EXEC (@Dynamic_SQL)

FETCH NEXT FROM condition_curso r 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
Sep 22 '06 #3
(al*******@gmai l.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, es****@sommarsk og.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
Sep 22 '06 #4
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/
<al*******@gmai l.comwrote in message news:11******** **************@ i42g2000cwa.goo glegroups.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_curso r CURSOR LOCAL FORWARD_ONLY FOR
SELECT * FROM local_condition _table
OPEN condition_curso r
FETCH NEXT FROM condition_curso r INTO
local_variables
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Dynamic_SQL = 'SET IMPLICIT_TRANSA CTIONS OFF INSERT INTO
SQLServer_A SELECT * FROM OPENQUERY (GRR_DB2Server, ' + @Dynamic_STR +
')'
EXEC (@Dynamic_SQL)

FETCH NEXT FROM condition_curso r 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
Sep 22 '06 #5
(al*******@gmai l.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&di splaylang=en
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.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
Sep 23 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
3730
by: hall | last post by:
Hi. I ran across a bug in one of my problems and after spending some time tracking it down i found that the problem arose in a piece of code that essentially did this: ----------- ifstream in("in.txt"); if(!in) {cout << "error\n";} in.close(); if(!in) {cout << "error\n";} in.close(); if(!in) {cout << "error\n";}
2
6008
by: mghale | last post by:
Does anyone have any idea what might be causing this error? 2005-11-02-11.23.02.769000-360 I17004595H370 LEVEL: Severe PID : 3576 TID : 4632 PROC : db2syscs.exe INSTANCE: DB2 NODE : 000 DB : CNTNTPUB APPHDL : 0-434 APPID: *LOCAL.DB2.051102172303 FUNCTION: DB2 UDB, base sys utilities, sqleSubsequentConnect, probe:40 RETCODE : ZRC=0xFFFFFBF0=-1040
3
1450
by: pkoeppe | last post by:
Hello I have the following problem. When I start my program (C#) I create an OleDbConnection to a DB2 and use this connection for different requests. But after about 8 minutes I get the following error: "OleDbException System.Data.OleDb.OleDbException: SQL0551N "xxx" does not have the privilege to perform operation "EXECUTE" on object "NULLID.yyy". SQLSTATE=42501". What does that mean??? Thanks for your help. Greets pkoeppe
4
6351
by: satish mullapudi | last post by:
Hi All, Am using Db2 v.8.2 ESE on Win 2003 Server. Am trying to connect to an already created db. But am getting the following error: db2 connect to DBNAME SQL1226N The maximum number of client connections are already started. SQLSTATE=57030 This is the entry found in the db2diag.log:
0
9115
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8982
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8845
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8825
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6491
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4340
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4579
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3003
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
1976
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.