Error Rejects connection after appr. 120 records

[Dan Sullivan]

I have an old small VB6 app that I am trying to update to VB..NET 2010. Still learning the .NET coding changes. I am getting an odd "broker rejects connection" in the middle of a repetitive process. Essentially, it is a small data gathering app. I am collecting data on students to port into another application. I grab one recordset and page through it and to populate certain fields I have to run another query with some logic.

The odd thing is the second query works fine for the first 120 or so calls then when it gets to student 123 and makes the call to the second function, the SQL call to the database gets the "broker rejects connection" error.

One more wrinkle, this does not happen on my local machine. But I need to run the app remotely on a virtual copy of my local machine. The error only occurs on the remote machine.

My hunch is there is some obscure setting that is limiting the number of sql statements that can be run, but I have no idea where to look. We use the Progress OpenEdge 10.2B driver, Windows 7.

 

[TomBascom]

My hunch is that you are opening a new connection with every query and neglecting to close them. I'll be that if you go over to the db side of things and fire up PROMON you will discover a whole bunch of connections with your name on them.

 

[Dan Sullivan]

Thanks, Tom. That doesn't seem to make sense to me though for two reasons. One, it doesn't happen when I run the same code from another machine and two, my code does create a recordset in the module in question, and I do close it, but being new to .NET, I don't know exactly how the garbage collection works with old VB6 code that I haven't gotten reconfigured yet. Here's an abbreviated scrape of the code...

Public Function GetLunchCode(ByVal StuID As Integer) As String
Dim sqlL As String
Dim rstL As New ADODB.Recordset
Dim sLCode As String
Dim sLunDate As String

sqlL = " select ""fs-transaction"".""NAME-ID"", ".....
rstL.Open(sqlL, CN_Sky_DB, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockReadOnly)

If rstL.EOF = True Then ' There is no Lunch Code record
GetLunchCode = ""
Else
rstL.MoveFirst()
sLCode = rstL.Fields("fs-lun-code-id").Value
sLunDate = Format(rstL.Fields("fs-tran-effective-date").Value, "MM/dd/yyyy")
GetLunchCode = sLCode + "," + sLunDate
End If

rstL.Close()
rstL = Nothing
End Function

In essence, I only connect to the database once when the program opens.

Public Function ConnectProgress() As Short
Dim sConnect As String
Dim iRet As Short
On Error GoTo handler

sConnect = "" & "Provider=MSDASQL;DSN=Skyward Student;UID=user;PWD=pwd"

CN_Sky_DB = New ADODB.Connection
With CN_Sky_DB
.ConnectionString = sConnect
.Open()
End With

ConnectProgress = MsgBoxResult.Ok
End Function

And of course I close the connection when the program ends.

- When I gather the initial recordset of about 2500 records and just run through those without calling the separate "GetLunch" function, no errors.
- When I call the "GetLunch" funtion, it works for the first 130 or so records then starts throwing the error in the GetLunch function.
- When I run the exact same code from a different environment connecting to the same database, no errors.

So it seems to be related to the specific machine and its environment. I am certain the Progress 10.2B driver is set up the same.

 

[Dan Sullivan]

Tom. Your hunch was correct. I do see in the database log files that somewhere in the execution of the program additional connections are occurring. Haven't traced the cause yet. It does not happen in the VB6 version, only in the .NET version and it only crashes in the virtual environment, but I at least have an idea where to look. Thanks.

 

[Dan Sullivan]

What appears to be happening is that the initial connection is getting lost and I cannot seem to find a consistent pattern. One time I get through about 90+ records and wind up with a "NO No SQL servers are available" error in the log. It seems like the initial connection is getting lost so any additional SQL calls are creating a new connection. Somehow 25 new connections had been established and I max out. There should only be one connection for the life of the program execution. Another time I might get through 30-40 records and the same thing happens, so it does not appear to be a particular record or particular place in the code.

I suspect there is something I am not understanding about how .NET handles ADO connections, but I don't think that is all of it... still looking for a specific place in the code where things go south.

 

[TomBascom]

Perhaps your code is ignoring errors?

Remember that Progress data is all variable width and that 4GL programmers pay no attention at all to the defined format. It's sort of like speed limit signs on rural highways -- "just a suggestion". This frequently results in fields that are "over stuffed". SQL hates that. It pitches a fit when Progress returns a field that is wider than SQL thinks that it should be. If such errors are occurring and your VB code isn't handling them well that might explain the broken connections.

Just in case this is all new to you... the fix for the problem is to run the Progress "dbtool" utility and have it modify the "sql width" to match what is actually in the database.

 

[Dan Sullivan]

I didn't include the error handling code in what I posted above for the sake of space. I do have error trapping in there, but I haven't yet learned the "Try-Catch-Finally" syntax well enough to fully upgrade, so I still have "On Error GoTo Handler". Each of the routines that are being called has a "Case Else", so if there are any errors I have not identified it should be hitting that point in the error handler and printing out an error number and description. The first error that gets printed in my error log is "Broker Rejects Connection" and then what follows is "Operation not allowed when object is closed", which I believe happens because on a previous iteration one of the subroutines threw an error.

I have stepped through the code at a place where the new connections get established and nothing pops up as an error prior to the "Broker rejects connection". I see no place in the code where the initial connection should be getting closed, just for no apparent reason the 34th time it calls a sub function to get an additional bit of data after functioning fine for 33 records, the Open statement for the SQL creates a new connection. After about 25 times of that it runs out of connections to open, then it finally throws the "Broker Rejects Connection" error.

I suppose it is possible that the SQL Width thing is causing it, I just am not seeing any errors getting thrown and no evidence that the initial connection is ever being closed. Somehow it just seems to lose reference to the initial recordset and or ADO Connection - it seems to become invisible to the sub functions. I'll check with my Administrator to see if we can run the DBTool.

 

[Dan Sullivan]

Perhaps your code is ignoring errors?

Remember that Progress data is all variable width and that 4GL programmers pay no attention at all to the defined format. It's sort of like speed limit signs on rural highways -- "just a suggestion". This frequently results in fields that are "over stuffed". SQL hates that. It pitches a fit when Progress returns a field that is wider than SQL thinks that it should be. If such errors are occurring and your VB code isn't handling them well that might explain the broken connections.

Just in case this is all new to you... the fix for the problem is to run the Progress "dbtool" utility and have it modify the "sql width" to match what is actually in the database.

DBTool is run weekly on an automated schedule