Here goes: We have situation at a customer site where users are suddenly unable to connect to SQL 2K via ASP using ODBC via DSN. While the customer is in this state, our Windows clients, which connect using OLE DB (ADO) to the same DB as the ASP, seem to be connecting without problems. Therefore, the problem seems to be with ODBC.
Additional details about the problem site:
SQL ODBC 3.50
Win2K Server SP 4.
<100 users total
Moderate usage of our ASP interface
Note: SQL2k and IIS are on the same LAN-side server.
FYI: Another part of the problem is that I'm a developer and am not that familiar with the tools available to help me troubleshoot this kind of problem.
We've tried using Perfmon to monitor connection pooling, but nothing stood-out.
We've tried using netstat (with various switches), but nothing stood-out.
Ensure both client and server are enabled with similar protocol, service packs as referred.
That's some great information! But won't this only trace activity across an open connection (implicit or explicit)? If so, I'm not sure it'll help because the problems is that at some point, the ASP are simply unable to connect via DSN using ODBC.
Given that it's as though SQL is simply inaccessable via the DSN, even when the ADO OLE DB client app is connecting fine, we've been speculating (with help from Microsoft) that the problem is with ODBC's connection pooling. We've tried to set-up performance logging of connection pooling in-house in an effort to determine it's potential usefulness to our scenario in production, but our in-house graphs show no activity...probably due to the relatively low activity in our development environment. We walked the customer through setting it up on their server anyhow.
Just recently, the problem occurred again on the customer's server. Before they could reboot the server, we took the opportunity to run NETSTAT to view the sockets for clues, but there were less than 20 results. Possibly because they have SQL and IIS on the same server?
This sucks because all we want to do is get an idea of how to duplicate the problem in a controlled environment so we can either a) adjust our ASP accordingly or b) give them a solution that we know addresses the problem.