[VB6 + ADO]Timeout bij ophalen records SQL Server

[urk_forever]

Hallo allemaal,

Wij hebben een raar probleem met onze VB6 applicatie die ADO gebruikt om records op te halen. Het vreemde is dat alles goed werkt, er kan verbinding gemaakt worden met de database etc, maar als we bijvoorbeeld een rapport willen maken, er worden dan veel records opgehaald, dan na een bepaald aantal records geeft stopt het programma, en wacht dan een 60 seconden en geeft dan de foutmelding:

[DBNETLIB]De SQL Server bestaat niet of toegang is geweigerd.

In het Engels:
[DBNETLIB]SQL Server does not exist or access denied.

Het vreemde is dat dit alleen gebeurt bij een verbinding met een SQL Server, als we lokaal naar access verbinden gaat het wel goed. We hebben al verschillende dingen geprobeerd:

* Andere sql server
* Database gechecked

Maar dit heeft allemaal dezelfde foutmelding

Nu hebben we in Access de database geopend en via een ODBC DSN de data opgehaald en dan gaat het wel goed

Waar zou dit nou aan kunnen liggen?

[arnob]

bij 'trusted' authenicatie wordt een meegeleverde username en wachtwoord genegeerd. De connection string dus. Dat is het enige wat ik zo uit m'n mouw kan schudden.

[whoami]

In eerste instantie zou ik zeggen dat je query gewoon te lang duurt, en er een timeout gegeven worden, wat best logisch is.

Als ik dan echter die foutmelding zie, dan denk ik dat je eens moet kijken of je wel connectie mag maken met die SQL Server (security settings; gebruik je integrated of sql server authentication ?

[urk_forever]

De connectie wordt wel gemaakt, en er komen ook records uit. Alleen na een bepaald aantal records krijg ik die timeout en daarna die foutmelding. Voor die foutmelding is er al data uit die connectie gekomen.
Er wordt een recordset opgehaald met 3 kolommen, die wordt doorlopen en adhv een id/foreign key wordt nog een simpele select from uitgevoerd om data op te halen.

Weinig bijzonders dus.

[urk_forever]

Ik heb na wat zoeken dit nog gevonden:

http://www.vbmonster.com/...exist-or-access-is-denied

En heb even getest of dit bij mij ook zo is, en dat is ook zo. Bij het uitdraaien van het rapport heb ik een logje gemaakt van de output van netstat, en daar staan duizenden van deze regels in:

code:

TCP    WS6790:4856            nt010-7.010.ldomein.nl:ms-sql-s  TIME_WAIT

Toch raar want ik gebruik maar 1 connectie in mijn code

[ Voor 6% gewijzigd door urk_forever op 12-07-2007 11:09 ]

[farlane]

Kan het zijn dat je door een deadlock of iets dergelijks de verbinding niet goed aflsluit waardoor opnieuw een verbinging gemaakt wordt?

Staat me vaag bij dat ik zoiets had doordat ik teveel server side cursors open had staan. Gebruik je MSDE?

[whoami]

Vergeet je soms van je connectie te sluiten ? Evt bij excepties bv dat je connectie niet gesloten wordt ?

[urk_forever]

Nope, het probleem waar ik tegen aan loop is denk ik het beste beschreven hier:

Description of TCP/IP settings that you may have to adjust when SQL Server connection pooling is disabled

Daar wordt aangegeven dat als je 4000 TIME_WAIT ports hebt dat je dan tegen de limiet aan loopt. Je kan dan het aantal poorten wat gelijktijdig geopend kan worden verhogen en de timeout verlagen. Dan zou het beter moeten gaan.

[farlane]

urk_forever schreef op donderdag 12 juli 2007 @ 14:32:
Nope, het probleem waar ik tegen aan loop is denk ik het beste beschreven hier:

Description of TCP/IP settings that you may have to adjust when SQL Server connection pooling is disabled

Daar wordt aangegeven dat als je 4000 TIME_WAIT ports hebt dat je dan tegen de limiet aan loopt. Je kan dan het aantal poorten wat gelijktijdig geopend kan worden verhogen en de timeout verlagen. Dan zou het beter moeten gaan.

Erm .. 4000? Heb toch sterk de indruk dat er dan wat aan je applicatie niet optimaal is. Waarom heb je eigenlijk connection pooling uitstaan?

[urk_forever]

Het probleem heb ik het eerst bij een klant gezien, die heeft er last van dat hij deze fouten krijgt. Het is volgens mij ook niet echt dat connection pooling uit staat, want de klant heeft getest met perfmon en het aantal actieve connecties loopt niet op. Er worden maar 2 connecties geopend. Alleen aan de kant van de client worden er 4000 tcp poorten geopend die dus 240 seconden op timewait komen te staan.

Waarom dat nou precies is, ik weet het niet Misschien dat jullie iets er vanaf weten.

[farlane]

urk_forever schreef op vrijdag 13 juli 2007 @ 10:04:
Het probleem heb ik het eerst bij een klant gezien, die heeft er last van dat hij deze fouten krijgt. Het is volgens mij ook niet echt dat connection pooling uit staat, want de klant heeft getest met perfmon en het aantal actieve connecties loopt niet op. Er worden maar 2 connecties geopend. Alleen aan de kant van de client worden er 4000 tcp poorten geopend die dus 240 seconden op timewait komen te staan.

Waarom dat nou precies is, ik weet het niet Misschien dat jullie iets er vanaf weten.

TimeWait komt volgens mij alleen vor als de client de verbinding sluit, en het OS deze openhoudt voor eventueel data die nog binnen zou kunnen komen. ( SO_LINGER en SO_REUSEADDR socket options )
Als connection pooling aanstaat worden sockets niet gesloten maar hergebruikt, het lijkt me sterk dat er dan zoveel sockets half-open kunnen blijven staan.

Het feit dat de klant maar 2 actieve verbindingen ziet zou goed kunnen kloppen; als ik er 2 open, snel sluit en weer nieuwe 2 open heb ik 2 actieve en 2 niet actieve verbindingen.

Ik vermoed toch dat je in een lus op een of andere manier snel verbindingen opent en sluit.

[Niemand_Anders]

code:

dim object as ADODB.Recordset
set object = new Adodb.RecordSet
object.CursorLocation = adUseClient
object.Open query, conn
object.ActiveConnection = nothing

do while (not object.EOF)
    ...
   object.MoveNext()
loop

object.Close()

Opmerking: Dit code blok komt oorsponkelijk uit classic ASP. Het gaat er in elk geval om dat standaard de cursor aan de server kant staat. Door de cursor naar de client te verplaatsen, wordt eerst het resultaat volledig in het RecordSet geplaatst. Dit voorkomt dat als de connection wordt verbroken (timeout) de recordset geen data meer heeft. Vooral bij grotere recordsets moet je op deze manier de data ophalen.

Een client cursor heeft als nadeel dat er meer geheugen nodig is omdat eerst alle resultaten worden opgehaalt. Bij een cursor op de server (adUseServer) maak je gebruik van een stream.

[urk_forever]

Nou, hier dan de code waarin de records naar excel weggeschreven worden:

Visual Basic:

  

  If sql <> "" Then
    rs.Open sql, cn
  Else
    rs.Open "SELECT Parent record WHERE where clausule", cn
  End If

While Not rs.EOF
    i = i + 1
    k = 5
    sht.Cells(i, 1) = rs.Fields("Id").Value
    sht.Cells(i, 2) = r.DFields("DESCRIPTION").Value
    sht.Cells(i, 3) = rs.Fields("PK_ELEMENT").Value
    
    
    ' Loop each cobjmeta and get the corresponding elementproperty record
    For j = 1 To drs.Count
      k = k + 1
      
      ' Get the property
      'If rsp.State > 0 Then rsp.Close
      'Set rsp = Nothing
      If drs.Item(j).DFields("INSPECTION").Value Then
        'Dit is een inspectie veld dus andere sql
        Set rsp = cn.Execute("SELECT Iets WHERE " & _
                 "een where clausule" & drs.Item(j).DFields("Id").Value & "))")
      Else
        Set rsp = cn.Execute("SELECT iets WHERE where clausule")
      End If
      
      ' If the property is found, add it to the sheet
      If Not rsp.EOF Then
        If IsDate(rsp.Fields("PROPERTYVALUE").Value) Then
          sht.Cells(i, k) = Format(rsp.Fields("PROPERTYVALUE").Value, "dd-mm-yyyy")
'        ElseIf IsBoolean(rsp.Fields("PROPERTYVALUE").Value) Then
'          sht.Cells(i, k) = IIf(rsp.Fields("PROPERTYVALUE").Value = "True", "Ja", "Nee")
        Else
          sht.Cells(i, k) = rsp.Fields("PROPERTYVALUE").Value
        End If
      End If
      
'Sluit recordset
      If rsp.State > 0 Then rsp.Close
      Set rsp = Nothing
    Next
      
    rs.MoveNext
  Wend

In de while clausule treed dus de timeout op en krijg ik de foutmelding.

[farlane]

- Heb je option explicit aanstaan?
- Wat is r voor een ding?
- Wat is drs voor een ding?
- Kun je deze query niet in een keer doen met een join? Je roept op elk record van rs een nieuwe query aan.
- Waar stel je in dat het een clientside cursor moet zijn bij de beide queries?
- Waar zit je foutafhandeling?

[ Voor 11% gewijzigd door farlane op 16-07-2007 13:20 ]

[farlane]

[kick]
Ben je hier al wat verder mee want ik ben eigenlijk wel benieuwd ...

[urk_forever]

Ga er morgen weer mee bezig. Als ik wat meer weet laat ik dat hier even weten.

[urk_forever]

Ik schop dit topic nogmaals omdat ik dit probleem weer een keer tegen gekomen ben. Ditmaal niet in VB6 maar in C# .Net. Een asp web pagina synchroniseert wat gegeven met een SQL server en zogauw dit gebeurt (het is simpel wat data ophalen en door die recordset heenlopen) dan krijgen we hetzelfde probleem, heel veel time_wait connecties tot de limiet van 5000 en dan kan hij geen verbinding meer maken. Ook connecties naar bijvoorbeeld MySQL werken dan niet meer

Heeft iemand nog een idee waarom hier connection pooling niet gebruikt wordt.

Hieronder even de code van de synchronisatie:

C#:

conn = new SqlConnection(ConfigurationSettings.AppSettings["connectiestring"]);
conn.Open();
string Table = ConfigurationSettings.AppSettings["tabelnaam"].Split(new char[] {'\'', '"', ' ', ';'})[0];
comm = new SqlCommand("SELECT t_boss, t_emno FROM "+ Table, conn);
reader = comm.ExecuteReader();

Gebruiker medewerker;
int leidinggevende;
int medewerker_nr;

while (reader.Read()) {

  if (reader["t_boss"] != System.DBNull.Value && reader["t_emno"] != System.DBNull.Value) {
    try {
      leidinggevende = Convert.ToInt32(reader["t_boss"]);
      medewerker_nr = Convert.ToInt32(reader["t_emno"]);

      if (medewerker_nr > 0) {
        int fk_leidinggevende = ((Gebruiker) personeelsnummers[leidinggevende]).Id;

        medewerker = (Gebruiker) personeelsnummers[medewerker_nr];
        medewerker.fk_Leidinggevende = fk_leidinggevende;
        if (medewerker.isDirty) medewerker.Save();
        medewerker = null;
      }
    } catch (Exception ex) {
      Response.Write(ex.Message);
    }
  }
}

Niks raars lijkt mij zo

[whoami]

Gaat het hier nu over SQL Server of MySQL ?
Wanneer sluit je je connecties ? Ik zie wel een conn.Open, maar nergens een conn.Close.

[urk_forever]

whoami schreef op maandag 10 september 2007 @ 15:59:
Gaat het hier nu over SQL Server of MySQL ?
Wanneer sluit je je connecties ? Ik zie wel een conn.Open, maar nergens een conn.Close.

Het gaat om connecties naar een MS SQL Server. In het stukje voorbeeld code wordt een select gedaan op een tabel en de resultaten daarvan worden naar een MySQL database weggeschreven. De connectie naar de MSSQL server spawnt voor elke keer dat de Reader.read aangeroepen wordt een nieuwe connectie naar de server. Op een gegeven moment zijn er 5000 poorten in TIME_WAIT status in Windows en kunnen er geen poorten meer geopend worden. Er kunnen dus ook geen connecties naar de MySQL server gemaakt worden!

De connectie wordt na de While loop gesloten, maar dat is het probleem niet! Het probleem is dat de connecties naar de SQL server niet gepooled worden maar steeds opnieuw geopend.

[SKiLLa]

Met C# altijd using gebruiken ala:

using (SqlCommand sqlCommand = new SqlCommand (...))

Dat geldt eigenlijk voor alle DB gerelateerde objecten in .Net Zonder de .Dispose() aanroep (die using automatisch en altijd - via try / finally - aanroept) krijg je geen correct gesloten connectie (die terug de pool in kan).

Met VB: Geen objecten binnen een loop creeeren en opruimen. En het is "Set cn.ActiveConnection = Nothing". Grote kans dat ergens een object aan een property toekent zonder de "Set", dat "functioneert" wel, maar niet zoals je bedoelt. Veel gemaakte fout met icm. .ActiveConnection, daar je daar zowel een ConnectionString alswel een AdoDB.Connection object kan gebruiken.

Verder op je SQL server kijken of je daar geen vage setting verkeerd hebt staan. Probeer het b.v. eens vanaf andere PCs te stresstesten, blijf je dan last ondervinden dan is het of je SQL server of rotte code ...

[urk_forever]

Kan je ook uitleggen waarom dit zo moet? Dit soort constructies gebruik wij wel vaker zo en geeft eigenlijk nooit geen problemen behalve nu dan.

[SKiLLa]

Jawel; het niet onder alle voorwaarden 100% correct sluiten en opruimen van je objecten leidt tot resource leaks. Vaak zijn het 'kleine lekjes', waardoor je het pas merkt onder flinke load. De resources worden vaak uiteindelijk wel weer door het OS (afhankelijk van je runtime omgeving en een boel andere factoren) geclaimed, maar als je sneller lekt dan het subsysteem 'opruimt', dan krijg je problemen. En die problemen uiten zich vaak pas onder hevige load of na een lange periode 'uptime'.

Veel apps en websites (AppPools) worden dagelijks herstart (DB natuurlijk niet), dus vaak valt het dan niet meteen op, maar het is wel degelijk een probleem. Of je resources lekt, is het makkelijkst testen met een dummy Access database. Exporteer b.v. je SQL data naar Access en laat je app daar een tijdje op los. Als de app dan na flink wat werk een tijdje"idle" is, dan mag er in de map waar de Access db staat geen .ldb file meer staan, bestaat die wel na het sluiten van je DB, dan hebbie een resource leak.
Een .ldb mag uitsluitend tijdens het queryen bestaan (wel zorgen dat je MS access zelf ook niet open hebt staan met die DB natuurlijk).

Zo'n .ldb file is altijd <x> keer 64 bytes groot. <x> is dan meteen het aantal "gelekte / open" recordsets.

[whoami]

urk_forever schreef op maandag 10 september 2007 @ 19:15:
[...]


Het gaat om connecties naar een MS SQL Server. Er kunnen dus ook geen connecties naar de MySQL server gemaakt worden!

Waarom heb je het dan steeds over MySQL ?

Met C# altijd using gebruiken ala:

using (SqlCommand sqlCommand = new SqlCommand (...))

Dat geldt eigenlijk voor alle DB gerelateerde objecten in .Net Zonder de .Dispose() aanroep (die using automatisch en altijd - via try / finally - aanroept) krijg je geen correct gesloten connectie (die terug de pool in kan).

Niet helemaal correct.
Als je de Close method van een connectie aanroept, dan is dat net hetzelfde als Dispose aanroepen. Dat using statement doet eigenlijk niets anders dan de Dispose method aanroepen als je buiten het using block gaat.
Dat using statement kan je dus voor alle classes gebruiken die IDispose implementeren.

Verder op je SQL server kijken of je daar geen vage setting verkeerd hebt staan. Probeer het b.v. eens vanaf andere PCs te stresstesten, blijf je dan last ondervinden dan is het of je SQL server of rotte code ..

Volgens mij sluit hij gewoon z'n connectie niet.

[ Voor 62% gewijzigd door whoami op 11-09-2007 09:39 ]

[urk_forever]

Ok, bedankt voor je uitleg, ik ga het eens proberen.

@whoami: Ik heb het niet steeds over MySQL Ik heb in 1 bericht gezegd dat als ik zoveel openstaande connecties heb dat dan ook bijvoorbeeld geen verbindingen naar MySQL opgezet kunnen worden.

[ Voor 65% gewijzigd door urk_forever op 11-09-2007 09:40 ]

[whoami]

urk_foreverDe connectie naar de MSSQL server spawnt voor elke keer dat de Reader.read aangeroepen wordt een nieuwe connectie naar de server.

Dit vind ik wel heel vreemd. Ben je dit helemaal zeker dat hij een nieuwe connectie opent iedere keer de Read aangeroepen wordt ?
Heb je dit in de SQL Server profiler al eens bekeken ?

[farlane]

whoami schreef op dinsdag 11 september 2007 @ 09:42:
[...]
Dit vind ik wel heel vreemd. Ben je dit helemaal zeker dat hij een nieuwe connectie opent iedere keer de Read aangeroepen wordt ?
Heb je dit in de SQL Server profiler al eens bekeken ?

Zou misschien kunnen bij een server side cursor en een lange tijd tussen de reads?