X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> SQL Server Timeout Settings, SQL Server 2012    
post May 30 2019, 12:30 PM

UtterAccess VIP
Posts: 1,700
Joined: 25-June 04
From: Northern Virginia

As a follow-up, related, but different to my earlier post today:
In SQL Server Management Studio, there is a "Connection Setting" for "Remote query timeout (in seconds)"
Default is 600 seconds (10 minutes).

Under "Advanced" there is a setting under "Parallelism" called "Query Wait"
Default is -1, which the description says means:

"Specify the time in seconds that a query waits for resources before timing out.
If the default value of -1 is used, then the time-out is calculated as 25 times of the estimated query cost."

Do these values apply to "bound Access forms" accessing the Data via linked tables (ODBC, DSN-less)??
I'm thinking when you have a form with 1 record and you try to update 1 field via the form, (via accmdSaveRecord, or moving to the Next row, etc),
that this is such a trivial action that the query would never timeout due to complexity (which, I imagine the "600 second" setting is for), but if its
timing out, it must be due to a resource lock (can't update the table because someone else has something going on with that record or a record with a relationship to that record?),
so the "Query wait" is probably taking effect.

So for my particular situation, when we get a timeout on a bound form update, (let's assume the estimated query cost is 1 second for this example).
Does this mean (with my default SQL Server value for that setting a -1) that it would timeout in 25 seconds and tell the Access Front-end that it timed out?

I'm guessing that over in the Access front-end "the number of update retries set to the default of 2" and the "Update retry interval being .250 seconds" DO NOT??? impact this process as Access would attempt the update twice in .5 of a second and then timeout???

Would LOVE to hear from someone with real SQL Server knowledge that has worked with bound Access forms (linked tables) odbc, DSN-less connections about this.
I'm pretty much shooting it the dark with these settings, as I can't seem to find reliable, detailed explanations on this.

Go to the top of the page
post Jun 13 2019, 03:15 PM

Posts: 11
Joined: 2-October 15

Most settings in SSMS only affect connecting through SSMS.
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    18th September 2019 - 06:28 AM