locked
VB App using ADO MDAC 2.5 hanging... RRS feed

  • Question

  • We have successfully created applications in VP, ASP, etc using ADO MDAC 2.5 and SQL Server ODBC for years.  One of my programmers has come across an issue we can not figure out.  His application loops thru a table and based on that information adds information to a few other tables.

    We've done SQL traces, and tried the normal trouble shooting practices with no luck.  The problem is this.  The program runs fine and then hangs at about 350 records.  Roughly the same place but not on the same command nor on the same instruction.  We did a network sniffer trace on his PC while it was running and found that for every database access, his network trace showed a TDS 7/8 Login session.  I ran the same program on my system without any problems and I did not get the same type of network traffic.

    We verified the same SQL Driver versions, the same MDAC version, etc, etc.

    I can provide much more detail, but I was wondering if anyone had heard of this problem.  Why would an ODBC client request a new login on each SQL Server interaction (for SELECT, DELETE, and INSERTS), while in the same loop and using the same connection established at the beginning of the program.  But, only on his system not mine.

    When the application finally hangs, we see in the sniffer details that the TDS7/8 Login request is delayed.  Sorry I don't have the exact message but it was an \IPC$ error saying the server was COMPLETING_MORE_WORK.  I can see the TCP session try to logff and the server accepts it but it tries again with the same result.  This continues for a few minutes and then the program continues, but then does it again.  It's as if the login is attemtped so many times without a log off, SQL server is terminating the logins and once there are enough free, it allows the connection to continue.

    I can provide more information if needed.  Has anyone seen anything like this before?

    We are running SQL Server 2005, MDAC 2.5, ODBC Veriosn 2000.85, VB 6

    Connection String =         cn1.ConnectionString = "DSN=myDB;User Id=sa;PASSWORD=*******;Description=myDB;UID=sa;APP=Microsoft Development Environment;WSID=RALWK119;DATABASE=myDB"

    Select statments as: rs.open "Select * from mytable", cn1, adOpenStatic, adLockReadOnly

    Thank you for any help.

    Monday, May 17, 2010 10:16 PM

Answers

  • Hello Patrick,

    Thanks for the response and I did post there.

    For the record, it's not a VB problem, we determined it is an ODBC (ether Bug or by Design issue).  When the Cursor type is set to adOpenForwardOnly, the ODBC driver issues a TDS7/8 Login request for the select statement for each/every Select even after the ADODB.connection has been established.  So, if you have a loop and are processing a large amount of data (say 20,000+ records), after 5,000 connections, the application hangs for an open IP Port.  If you change the Cursor type to say adOpenStatic (or any of the other types), the ODBC driver does not issue a login for each select.

    I hope this helps and now that I know this is an ODBC problem and not a VB/SQL issue, this can be closed or moved to another thread if you like.

    Thanks!

    • Marked as answer by dschad Tuesday, May 25, 2010 5:48 PM
    Tuesday, May 25, 2010 5:48 PM

All replies

  • For classic VB6 I'd ask them here.

    http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.vb.general.discussion&cat=en_US_6fbee8da-b619-44cd-a361-5c6f36369666&lang=en&cr=US

     

     


    Regards, Dave Patrick .... Microsoft Certified Professional -Microsoft MVP [Windows]
    • Marked as answer by KJian_ Tuesday, May 25, 2010 2:33 AM
    • Unmarked as answer by dschad Tuesday, May 25, 2010 5:41 PM
    Monday, May 17, 2010 11:30 PM
  • Hello Patrick,

    Thanks for the response and I did post there.

    For the record, it's not a VB problem, we determined it is an ODBC (ether Bug or by Design issue).  When the Cursor type is set to adOpenForwardOnly, the ODBC driver issues a TDS7/8 Login request for the select statement for each/every Select even after the ADODB.connection has been established.  So, if you have a loop and are processing a large amount of data (say 20,000+ records), after 5,000 connections, the application hangs for an open IP Port.  If you change the Cursor type to say adOpenStatic (or any of the other types), the ODBC driver does not issue a login for each select.

    I hope this helps and now that I know this is an ODBC problem and not a VB/SQL issue, this can be closed or moved to another thread if you like.

    Thanks!

    • Marked as answer by dschad Tuesday, May 25, 2010 5:48 PM
    Tuesday, May 25, 2010 5:48 PM