On 2003 cluster (SQL 2000) connection pooling causes crash

For performance reasons we have (successfully) used pooling in our apps and
services on almost one hundred installations, NT 4.0 to 2000 servers (SQL
7.0 - 8.0), NT 4.0 to XP workstations without a problem in any combination.
Now with 5.2 (2003) server a local client is unable to connect and crashes
within the SQLDriverConnect call (SQLConnect as well). Does not return at
all.
When the same runs on any other client (4.0, 2000, XP), everything is OK.
The problem comes and goes with SQL_ATTR_CONNECTION_POOLING
100% sure the bug is not in our code - the pure MS-supplied code as shown in
Q164221 crashes precisely the same way.
When SQLSetEnvAttr(...SQL_ATTR_CONNECTION_POOLING...) is not used, it's ok,
BUT:
To quit using benefits of pooling is a solution with severe impact on
performance.
Any hint?
TIA
Jaroslav Pivonka
mailto:jpivonka@.centrum.cz
// Windows Server 2003, 2 in a cluster, SQL Server 2000 EE SP4 (8.00.2039)
Hi
I am assuming that your are not using AWE memory, as you should apply hotfix
2040 for SQL Server SP4
(http://support.microsoft.com/default...b;en-us;899761)
As to the connection pooling, check the versions of the ODBC drivers between
your various platforms. You will notice Windows 2003 has the most recent
one.
It may be a bug in the driver, or it may be that the feature is not working
anymore.
Do you have the same problem with Windows XP SP2 or Windows 2003 SP1?
If so, I would open a case with Microsoft Support Services.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jaroslav" <Jaroslav@.discussions.microsoft.com> wrote in message
news:1D39DE4D-FA1C-4A8B-A4D7-CBFCBB60D29A@.microsoft.com...
> For performance reasons we have (successfully) used pooling in our apps
> and
> services on almost one hundred installations, NT 4.0 to 2000 servers (SQL
> 7.0 - 8.0), NT 4.0 to XP workstations without a problem in any
> combination.
> Now with 5.2 (2003) server a local client is unable to connect and crashes
> within the SQLDriverConnect call (SQLConnect as well). Does not return at
> all.
> When the same runs on any other client (4.0, 2000, XP), everything is OK.
> The problem comes and goes with SQL_ATTR_CONNECTION_POOLING
> 100% sure the bug is not in our code - the pure MS-supplied code as shown
> in
> Q164221 crashes precisely the same way.
> When SQLSetEnvAttr(...SQL_ATTR_CONNECTION_POOLING...) is not used, it's
> ok,
> BUT:
> To quit using benefits of pooling is a solution with severe impact on
> performance.
> Any hint?
> TIA
> Jaroslav Pivonka
> mailto:jpivonka@.centrum.cz
> // Windows Server 2003, 2 in a cluster, SQL Server 2000 EE SP4 (8.00.2039)
>
|||"Mike Epprecht (SQL MVP)" wrote:

> Hi
> I am assuming that your are not using AWE memory, as you should apply hotfix
> 2040 for SQL Server SP4
> (http://support.microsoft.com/default...b;en-us;899761)
Negative. 2GB each server.
Moreover it seems to be an ODBC problem, not server.

> As to the connection pooling, check the versions of the ODBC drivers between
> your various platforms. You will notice Windows 2003 has the most recent
> one.
> It may be a bug in the driver, or it may be that the feature is not working
> anymore.
Hopefully the first is true and somebody will fix it soon.
So far we have put a switch outside, enabling us not to use pooling (default
is yes), into a couple of generic services running on the cluster so we can
leave all but two new systems as they were, and on the new installations
we'll keep waiting until a patch comes.

> Do you have the same problem with Windows XP SP2 or Windows 2003 SP1?
Up to XP SP2 using MDAC 2.8 SP1 everything works fine.
The problem came with Windows 2003 Server SP1 having MDAC 2.8. SP2

> If so, I would open a case with Microsoft Support Services.
Please try to check it. I have proven that freshly installed systems
(nothing but Win2003 SP1 w MDAC 2.8 SP2, SQL 2000 EE (8.00.2039)) fail to run
locally even the sample code shown in Q164221 (MS only, not a line of ours).
Remote clients (pre-2003) are ok.
Thanks
Jaroslav Pivonka
sql

OLE DB Destination Table/View Drop Down

Is there any way around (or will there be) using the drop-down? It takes several minutes when running against an Oracle Apps database to populate that dropdown with the several hundreds of tables and views.

TIA.

Yes, just write a SQL statement instead. You should be doing this anyway to be honest, selecting from the dropdown is very bad practice.

-Jamie

|||

For an OLE DB Destination? I always write SQL for the sources, but don't quite follow how to do that for a destination.

Thanks for the quick reply!

-Chad

|||

DAMN. You said Destination. Sorry, I should read posts more clearly.

However, you can still do this. Just select 'SQL Command' as your Data Access Mode and your SQL statement just selects all the columns that you want to insert into from the table that you want to insert into.

Ignore what I said about best practice though. its best practice for sources, not destinations.

-Jamie