SQL Runtime Error Timeouts

We added a new module to a custom VBA app recently. We are polling a mixture of data from Macola (ERP) tables and 20-odd custom tables within the Macola database. The application is for order monitoring, release & distribution monitoring, & active dispatch. We use custom grids (not Office) to display data to the user.

App & SQL servers are Win 2008 R2. Default instance of SQL. Workstations are Win7. MSSQL 2008 STD SP1. Edges are Cisco ASAs, switches are Cisco Catalysts.  I have eliminated x86 vs x64 WS, SQL timeout setting has been manipulated up & down, security restrictions are not at issue, have installed SQL client (or not), have no free memory constraints that I am aware of, and the processors are never seriously challenged here.

The new app module is for the exclusive use of a single small department (3-4 users). This department is not a transaction-intensive area, rather they are in R/O or View mode most of the time. However, during peak processing periods they are receiving intermittent runtime errors with the very common -2147467259 connection error code, consistently at the 30 second mark. I have seen this code my share of times over the years, however, it has always been a go/no go situation, and seldom an  intermittent scenario.

The progammer has been over this area of the code repeatedly to no avail. The VBA project size is ~60Mb, so it is not exactly a trivial piece of software.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
The default connection timeout is set to 30 seconds.  If you occasionally have queries that take longer than 30 seconds to execute then depending on your error handling that could cause the error you're seeing.  It might explain why it is intermittent as the same query may run fine most of the time but occasionally when the server is under load it would timeout.

Try lengthening the timeout value and see if that helps first.  It's an easy first step.
mmshanerAuthor Commented:
See para 2 above, "...SQL timeout setting has been manipulated up & down...". Even lengthening the timeout setting had no effect. It's almost as if there another different layer of admin going on. I asked the programmer to assist in that effort, from the dev platform side. We have had no luck, thus far.

Thank you for the input. We do appreciate it.
Brian CroweDatabase AdministratorCommented:
Apologies for not reading thoroughly...I've been doing some searching but I'm probably just finding the same stuff you've already seen and tried.

Sorry I couldn't be of more help.
mmshanerAuthor Commented:
Added another index to one of the tables (one of the ones with the most records) that included a unique combination of fields utilized by the query for the new module app. The app is quite "snappy" now and was able to fully render its resultant recordset to the grid in less than a second or so on Friday afternoon, even during peak production.

Thank you.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mmshanerAuthor Commented:
It worked, obviously. Not much response (1) in 4 calendar days. And the suggestion had already been tried and was stated as such.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.