[RESOLVED] Error 08004: Too many client tasks

[rjbudz]

I'm not sure if this should be posted in VB.NET, Databases, or MS Office. But let's see...

I have an app (written in VB 2005) that accesses an MS Access database.

I have 2800+ text files to process and write to this database. They each have a unique number in their names, so it's just a matter of parsing it to check for the entry in the table. Unfortunately, the numbers are not sequential, and range from 60 to 19910.

So i need to check for a record for each one prior to processing it. If the record exists, I update its information.

Now, here is the problem:

After about, oh, 14000 times (and that's an estimate, I'm not really sure where it started), I begin getting the error 08004:Too many client tasks. on conn.open()

The breakdowns seem to occur progressively quicker (that is, for example, at 200, 250, 278, 290, 295 etc. (not actual number examples).

I can limp past it by doing a try block with a conn.close() then conn.open() in the catch block. But that's cludge, and it only works for a while, then I get the same error in the Catch block. I can move back to conn.close() and continually try F5 until it works. But it's an ugly thing to have to do.

If I stop debugging (this is all in the IDE, and thankfully not compiled) and start over with the next number in the series, it works for a while, then breaks again.

    Function NotInDatabase(ByVal ReportNumber As Integer) As Boolean
        Dim conn As OdbcConnection
        Dim comm As OdbcCommand
        Dim dr As OdbcDataReader
        Dim connectionString As String
        Dim sql As String

        connectionString = "DSN=MS Access Database;DBQ= " & DATABASE_LOC
        sql = "SELECT ReportNumber FROM Reports WHERE ReportNumber = " & ReportNumber
        conn = New OdbcConnection(connectionString)

        conn.Open()         ' <== Error 08004: Too many client tasks

        ' shamefull attempt to fix
        'Try
        '    conn.Open()
        'Catch
        '    conn.Close()
        '    conn.Open()
        'End Try

        comm = New OdbcCommand(sql, conn)
        dr = comm.ExecuteReader()

        Try
            dr.Read()
            If dr.GetInt16(0) = ReportNumber Then
                Return False
            Else
                Return True
            End If
        Catch
            Return True
        End Try

        conn.Close()
    End Function

I do access the database in another procedure, with similar code:

    Sub UpdateRecord(ByVal ReportNumber As Integer, ByVal FieldName As String, ByVal FieldValue As String)
        Dim conn As OdbcConnection

        Dim connectionString As String
        Dim sql As String
        connectionString = "DSN=MS Access Database;DBQ= " & DATABASE_LOC
        sql = "UPDATE Reports SET " & FieldName & " = '" & FieldValue & "' WHERE ReportNumber = " & ReportNumber

        conn = New OdbcConnection(connectionString)
        conn.Open()

        Dim cmd As New OdbcCommand(sql, conn)

        cmd.ExecuteNonQuery()
        conn.Close()

    End Sub

but it never breaks in this here. I include it to show that I do, indeed, close the connection everywhere.

BTW, I did limp all the way through to the end. I got all the files processed. But it was hellish, and I will eventually have to do this again. I can't find the answer elsewhere, and I've little doubt that if it happened to me it'll happen to someone else.

[kleinma]

You are not calling .dispose on the objects that implement IDisposable, like the command and connection classes. This can cause underlying unmanaged code that the .NET framework interacts with to not be properly cleaned up. This cause lead to connections staying open and resources being tied up when you are done with them.

That being said, and assuming that you are going to be processing all these text files one after another at a given time, I would think you could refactor your code to

Open Database Connection
Run loop on textfiles
either insert or update records in database
end loop
Close Database Connection

It seems what you are doing now looks like

run loop on textfiles
open connection
either insert or update records in database
close connection
end loop

which is a lot of open closing of connections when you know you need an open connection from start to finish on this operation...

[rjbudz]

So you're saying I should open the database connection, keep it open through the life of the program run, then close it after I'm done?

That makes sense. Now, should the connection be accessible globally, rather than in the NotInDatabase() function, so UpdateRecord() can use it too? Will this cause other problems (it doesn't look like it, but the answer should save me starting another thread about it later )This would mean one connection, and a ton of accessing rather than a ton of connections with one access.

Also, the break occurred only in the one procedure (the one that connected 19,000+ times, rather than the one that connected 2,000+ times). I kind of have a feel for what is happening, on a subconscious level I detect a pattern here, but don't fully grasp it.

Can you explain what is happening?

[kleinma]

Well I don't know all the internal workings of your app, but from the sound of things, when it runs, it loops through a whole bunch of files and processes them one by one correct?

So you could open a connection to the database, then start your loop. You shouldn't need to actually make a global connection to the database, as you could simply pass the connection into the routines that process the file as a parameter.

As far as keeping the connection open for the file of the program run, I would say yes if the life of the program is simply to process these files (aka it doesn't sit there idle when there is no work to do with an open connection to the database)

Also, you are probably hitting either an actual or theoretical limit for connections or resource consumption when you connect 19000 times versus 2000 times. By theoretical, I mean it could be something based on the system resources (amount of ram, etc). Either way, it is an inefficient design.

You should be able to clean it up to make minimal open/close calls to the database since this sounds like a tightly looped operation you are performing.

[Shaggy Hiker]

The issue is clearly one of resources being used up, just from the description of the problem. You are also using that connection ALOT, so as Kleinma pointed out, there is no advantage, and some issues with re-opening.

The way I have handled things like this in the past (though there was always one and only one user) was that the database was wrapped in a class that managed the connection. I used a simple GetCommand() method to get a command object from the connection, and a method that handled the database interaction. In your case, that method might take a text file, or the data from a textfile (whichever makes more sense in your case) and performs the check on the database, and the UPDATE if needed.

This would mean that your loop would consist of getting each file in sequence, and calling the method with the relevant data.

[Shaggy Hiker]

Ah, too slow, but hopefully it helps your thoughts in some manner.

[rjbudz]

I did as kleinma suggested:

Open Database Connection
Run loop on textfiles
either insert or update records in database
end loop
Close Database Connection

and hopefully it will do the trick (running it again to check seems like a gigantic time waster, so I won't, though I did verify both functions can read from and update the database).
I made this global:

    Dim conn As New OdbcConnection
    Dim connectionString As String = "DSN=MS Access Database;DBQ= " & DATABASE_LOC
    Dim comm As OdbcCommand

and called:

    Sub OpenDatabaseConnection()
        conn = New OdbcConnection(connectionString)
        conn.Open()
    End Sub

and removed the corresponding code from both functions.


BTW, I was a long-time hold of of VB 6 and have finally bit the bullet to VB 2005.

You were right, kleinma, after the inital learning curve, I find I like it here!

[kleinma]

you should always have a folder of "test/dummy" files and a "dummy" database so you can easily test code changes without having to touch the real production data or database.

That way you could run your code to make sure everything works without actually messing up your production environment.

[rjbudz]

I agree. This particular project was a personal one that is not of a critical nature or in a corpoate environment.

But having a Dev environment/sandbox to test non-production code is a Must Have in 'Real world' development.