I have a custom screen with a filter and a grid, where the grid displays a list of Shippers that have been invoiced.
The purpose of the screen is to export the invoices in both PDF and CSV format. The grid has an Export check box column field that the user can select a range with, and push the Export button at the bottom.
The PDF file is created using ROI, printing the invoice report as a PDF file. The CSV file is created by creating a separate SQL Connection, pulling down a view, and using a data reader to read the results and write them to a file.
Recently we tried exporting a large number of invoices and started getting a bulletproof error somewhere between the 30th and 40th invoice being exported.
Programming Bulletproof Error 10097 - Too many connections for internal array size.
I commented out the logic for the CSV file (including the separate SQL connection) to prove that it is the calls to ROI that cause the issue.
My first guess was that ROI was releasing connections at a slower rate than I was opening new ones, and I was going to query SQL periodically for the number of connections and pause when it got so high.
I used the following query to watch the number of connections established by the process (we are using Windows Authentication)
SELECT loginame, COUNT(loginame) FROM sys.sysprocessesWHERE db_name(dbid) = 'AppDB'GROUP BY loginameORDER BY COUNT(loginame) DESC
Running that query periodically while it is going through the invoices, I can see the number of connections increasing. When it gets to 50, the error occurs. When I push OK on the error and press OK on the Process screen, the connections go back down to normal.
After that test, I added breaks every 15 invoices so I could watch how fast the connections decreased...and they never did.Since closing the process window caused the connections to go away after the error occurred, I tried closing and reopening the process window every 15 invoices, but that didn't cause the connections to go away either.
I just selected 12 invoices to export and it ran through the entire process successfully. When I looked at the number of open connections afterwards I saw that they had not been released, even after the process completed. They didn't release until I closed the screen.
So I guess my question is.. how can I run a bunch of calls to ROI without it continuously opening up new connections without closing them?
Other details:SL7 FP1 with Windows AuthenticationSQL Server 2008
How are you firing up ROI?
Are you using CallApplicWait?
Call ApplSetParmValue("", "", strReport)
Call ApplSetParmValue("", "", strFormat)
Call ApplSetParmValue("", "", "0/EXPFORMAT")
Call ApplSetParmValue("", "", strWorkDir.Trim & FileName.Trim & ".pdf" & "/EXPFILENAME")
Call ApplSetParmValue("", "", "SOShipHeader.InvcNbr=" + SParm(strInvcNbrArg) + "/WHERE")
Call CallApplicWait("ROI", "")
Other Microsoft Sites
I'm a Customer
I'm a Partner
Use the official Twitter tags:
#MSDYNCOMM | #CONV13