sql server programming:
Need Help! Connection Count to Linked DB2 Server Reaches limit
(SQL1040N) when execute dynamic generated OPENQUERY T-SQL
Hello, every body.
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:
CREATE PROCEDURE p_FetchRawData variable_list
AS
BEGIN
..=2E..
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 =3D 0
BEGIN
SET @Dynamic_SQL =3D 'SET IMPLICIT_TRANSACTIONS OFF INSERT INTO
SQLServer_A SELECT * FROM OPENQUERY (GRR_DB2Server, ' + @Dynamic_STR +
')'
EXEC @Dynamic_SQL
END
..=2E..
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
=E4=B8=8E=E6=95=B0=E6=8D=AE=E5=BA=93=E7=9B=B8=E8=BF=9E=E7=9A=84=E5=BA=94=E7=
=94=A8=E7=A8=8B=E5=BA=8F=E5=B7=B2=E8=BE=BE=E5=88=B0=E6=9C=80=E5=A4=A7=E6=95=
=B0=E7=9B=AE=E3=80=82
SQLSTATE=3D57030]
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
