none
Intermittent “[DBNETLIB][ConnectionOpen (Connect()).]" Error

    問題

  • Hi,


    I have a classic ASP app that loops through a record set and calls another query based on each record returned. Something like:

    sSql = User Query

    rsUser = conn.execute(sSql)

    do while not rsUser.eof

    sSql = Select [Type] From [User Types Display] Where UserId = rsUser(“Userid”)

    rsType = conn.execute(sSql)

    ‘ Put information into HTML table

    rsType.Close

    rsUser.MoveNext

    loop

    In general, at around 3575-4000 loops, the system kicks back :

    Microsoft OLE DB Provider for SQL Server error '80004005'

    [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

    UserList.asp Line 300


    I get no error message in the SQL Server Logs (according to the server, nothing happened), just this message on my ASP page. This happens about 90% of the time. 5% it runs completely, the other 5% bombs after 1-100 loops.


    This only started happening when we took our app from a local Server 2000/SQL 2000 setup to separate Server 2003/SQL 2005 setup.


    In SQL Profiler I see the loops hitting with only two reads per batch (TCP/IP). This seems very low compared to named pipes (when in a local server configuration) reads of 15-30 reads per batch.


    I know it is not a hardware issue; this behavior is observed in both our test and production environments. All of our other ASP page queries work fine, just the 2 or 3 of hundreds that loop like above. cause the error.


    Client Details (as best I could):


    Connection String (in .inc file): conn.Open "Provider=SQLOLEDB;Network=DBMSSOCN;Data Source=10.0.1.100;Initial Catalog=’TestDB';User ID='XXXX';Password='XXXX'"


    The Client is Remote to the SQL machine and connects through TCP/IP only.


    I force no encryption on the back and forth between the servers.


    Server Details:
    SQL Server 2005 Standard with SP1


    Only Named Pipes and TCP/IP are enabled


    I think this is all the info relative to my issue.


    Thank you so much for any help,

    Smith


    2007年4月23日 22:28

解答

  • Hi Smith,

    What is probably happening in your case is the so called "implicit connections" - since you are using the same conn object for both the rsUser and the rsType, the connection is considered "occupied" and ADO silently opens a new one under the hood. Once you exceed the 4000 loops what is probably happening is you run out of sockets on the client machine. The default is 5000 and is controlled via MaxUserPort (you could easily find how to modify it in the documentation).

    It would be much better instead of increasing the # of ports, to redesign the ASP app to avoid the implicit connections. The issue is well described in the following KB article:

     

    You cannot access a SQL Server database by using the OLE DB provider for SQL Server when your application is in a high-stress scenario

    http://support.microsoft.com/kb/907264

     

    HTH,
    Jivko Dobrev - MSFT
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.

    2007年4月25日 19:56
    版主

所有回覆

  • Hi Smith,

    What is probably happening in your case is the so called "implicit connections" - since you are using the same conn object for both the rsUser and the rsType, the connection is considered "occupied" and ADO silently opens a new one under the hood. Once you exceed the 4000 loops what is probably happening is you run out of sockets on the client machine. The default is 5000 and is controlled via MaxUserPort (you could easily find how to modify it in the documentation).

    It would be much better instead of increasing the # of ports, to redesign the ASP app to avoid the implicit connections. The issue is well described in the following KB article:

     

    You cannot access a SQL Server database by using the OLE DB provider for SQL Server when your application is in a high-stress scenario

    http://support.microsoft.com/kb/907264

     

    HTH,
    Jivko Dobrev - MSFT
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.

    2007年4月25日 19:56
    版主
  • Jivko - Thank You!  I can't even begin to tell you how much this has helped me!

    This was most definitely it.  I ended up creating another explicit connection to handle the loop, then closing the connection once the read is done.  Not only did it solve the error messages, but it also increased performance significantly.  Makes perfect sense...

    Not to poke a stick in the eye of those that helped me out so much, but can you please forward the other part of the error:

    Microsoft OLE DB Provider for SQL Server error '80004005'
    [DBNETLIB][ConnectionOpen (Connect()).]

    ...to the people that handle the KB articles so they may include it in this KB article?  I went through the dozens of KB articles around this part of the error before posting.  May help someone in a future search.

    Thanks Again!

    Smith

    2007年4月26日 14:08
  • Thanks for the feedback, Smith. I am glad the problem is resolved - I will pass the feedback.

    Jivko

    2007年4月28日 0:29
    版主
  • Thank you for the information Jivko but I have a little bit of a different issue.

     

    IIS 6 on Server 2003 Standard Edition

    I have .NET framework 1.1, 2, and 3 installed.   I do not have any version of SQL or even SQL Tools on the Webserver.

     

    My applications runs just fine on Server 2000 w/SP4 and accessing a remote SQL 2000 w/SP4 database.   All my other applications, site run well on the new Server 2003 that I'm building as a replacement.  This one application though gives a very similar error.

     

    Microsoft OLE DB Provider for SQL Server error '80004005'

    [DBNETLIB][ConnectionOpen (Invalid Instance()).]Invalid connection.

    /rma/index.asp, line 101

     

     

    line 101 is:

    rs.ActiveConnection = MM_Connection_STRING

     

    in the connection ASP page it has:

    MM_Connection_STRING = "Provider=SQLOLEDB.1;Password=xxxxx;Persist Security Info=True;User ID=sa;Initial Catalog=MSE;Data Source=serverxyz"

     

    The KB article:  http://support.microsoft.com/kb/907264 has this

     

    WORKAROUND

    To work around this issue, use one of the following methods:
    Avoid implicit connections in large enterprise application scenarios. Instead, explicitly create additional OLE DB data source/session pairs, or explicitly create ADO connections.
    Avoid using the firehose cursor on a SQLOLEDB session or an ADO connection that will process multiple result sets at the same time.
    Set the multiple connections property of the ADO connection to false, or set the OLE DB DBPROP_MULTIPLECONNECTIONS property to VARIANT_FALSE.

     

    I'm not exactly sure how to implement these changes as I'm not a programmer and this is a bit new area for me on setting up the IIS/SQL connections.

     

    Can you assist or point me to another document that might provide more details on the changes required.  What I don't understand though is that if I put it on a 2000 Server it runs just fine, so I'm pretty sure something in IIS6 or Server 2003 is the actual culprit here and not SQL.

     

    Thanks for assistance.

     

    Ron

    2007年5月18日 1:56
  • Well as it turns out this apparently is not related to the KB fixes (for my issue)  As it turns out the connection string was not quite valid.  I found this by working with a programmer friend who created a .UDL file and built the connection link on the fly.  Apparently the instance name of SQL was double (at one point in time they had some failure on SQL and created a new instance with the name doubled) now why Windows 2000 has no problem finding it I'm not sure but by adding the trailing name again it connected immediately.

     

    Right click, Add New Text File, F2 rename file to what ever ( newconnection.UDL)  then double-click and fill in the parameters of the connection.  Test it and if/when successful you can then edit it in notepad and look at the connection string used.

     

     

    2007年5月18日 18:15
  • This was unbelievably helpful.  I can confirm this is our exact problem.  Every article I could find focused on "non intermittent" causes for the error.  This is still the only article I could find that explains our problem. So thank you.

    Now I am hoping you can explain something else that is related, which is something we would really like to know.

    This issue can be resolved without fixing the code, but rather by changing the connection string to OLDB.

    e.g. Driver={SQL Server};Server=<server>;Database=<database>;Uid=<user>;Pwd=<password>;

    Can anyone please explain why ODBC is able to handle the implicit connections and the OLE DB has intermittent errors?

    Thank you very much.

    2007年10月4日 21:45
  •  

    Hi,

     

    I am facing a pretty similar issue with an application in my company.

     

    The error message is:

    Microsoft OLE DB Provider for ODBC Drivers error '80004005'

    [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied.

    blablabla\AgendaHebdo.asp, line 751

     

    Server details:

    Windows Server 2003 Enterprise Edition SP1 with SQL 2005 SP2. IIS 6.0 is on the same server.

     

    Is there anyone who can help me with this issue?

     

    Thanks.

     

    G

    2007年11月28日 13:39
  • This was most helpful and enabled us to solve our problem.  We have an older VB 6 program that connects using ADO and loops through several thousand updates.  On our old client and server setup it ran just fine but these machines were older and slower.  We just upgraded to SQL 2005 and brand new servers processing on SAN disk arrays.  The program suddenly started having intermittent failures but always at about the same point in the recordset after about 4000 loops give or take a few.  Our theory was that the Windows 2003 TCPIP defaults were set to 4 minutes to clear out idle ports and the maximum user ports by default was somewhere around 5000 as indicated.  Given the original slower processing speed of the older machine these defaults were probably not an issue.

    We decided to take this advice and did the following:

    1. Changed the settings on the client machine (which was a Windows 2003 server) to MaxUserPort = 65534 and  TCPTimedWaitDelay to 60 (seconds).  We read elsewhere that Microsoft does not recommend taking the TCPTimedWaitDelay below the 60 second threshold even though you can.

    2. Changed the settings on the SQL Server which also was running on Windows 2003 server to these same settings.

    3. Rebooted both machines to ensure the parameters "took".

    The settings to change are in the registry and you have to create a new DWORD to enter these keyword parameter names and the actual values.  The values have to be entered in DECIMAL format (not hexadecimal). 

    To change the settings run Regedt32.exe from each of the machines command prompt.

    The settings have to be changed in HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\Tcpip\Parameters


    2008年6月6日 18:22