February 1, 2012 at 10:03 am
Hello there. My company converted to SQL Server 2008 R2 as the backend for its inventory database about a month ago, and since then, we've been having Timeout Expired errors with error code S1T00 occasionally. The frontend we're using to interface with SQL Server is Microsoft Access. We can't seem to pinpoint the cause of the errors, and I've been trying various fixes, though they don't seem to be producing results. The other oddity about the problem is that the error will appear on occasion and halt whatever process is running, but upon retrying the process, it continues without any complaints. Other times, however, the error will stop all functionality for a very long amount of time.
Fixes attempted so far:
1. Ensuring that ports are opened on the server firewall for SQL Server; ports are opened, and SQL Server is set to listen on those ports.
2. Checked the authentication scheme for SQL Server (currently using NTLM); I've read that using Kerberos is more secure, but I'm not entirely sure how to set it up correctly. I'm essentially the only IT person at this company, and networking isn't quite my forte, so if this seems like the way to get this fixed, support on enabling Kerberos functionality would be greatly appreciated :).
3. Checked the ODBC DSN we're using to connect; the connection tests using the DSN always seem to return successful results, even when the problem is ongoing.
4. Setting up a task to run an ipconfig /flushdns command regularly on each computer; this was done after I noted that, in the case of one of the long-running service halts, I tried flushing the DNS cache just on a whim, and the process proceeded to run along fine after that. However, either the interval of the task isn't set low enough, or this actually isn't doing anything, as the problems don't seem to have abated.
5. Configuring various timeouts; the SQL Server timeouts are set at 600 seconds, and the timeouts in Access for ODBC are set to 600 seconds as well. The oddity here is that the error will appear immediately when a function is called whenever it does happen, so it doesn't seem to be an actual timeout error. (For clarity, the error usually appears as such: Someone pushes a button, and the error immediately appears).
6. Voodoo rituals.
So far, I haven't had any luck getting rid of this error, and while it isn't harmful most of the time, the occasions when it does stop our functionality for a good while are understandably not ideal. Would anyone have any idea how to fix this problem?
For further information, the server has 16GB memory, SQL Server is configured to use 14GB of that memory, and other processes only use up around 1-1.5GB of memory at any given time. The server is a quad-core with good processing speed, and the data we're working on isn't particularly huge; our largest table is about 300,000 rows, and the vast majority of queries that we use on a day-to-day basis complete in less than a second. The longest-running queries we use take roughly 5 seconds at the worst. We support 10 or fewer users at any given time simulatenously. DMV queries don't show anything particularly wrong with memory, processor, or I/O pressure. I'm quite stumped, personally.
- 😀
February 1, 2012 at 10:26 am
I'm thinking that it might not be timing out waiting on the sql server, rather it might be odbc timing out from trying to get the network connection to work, and you might want to check that your networking between the front end and sql server is not flakey.
February 1, 2012 at 10:47 am
Hm. I'd considered that possibility previously; searches I've done suggested that a look at the NIC card and the general networking setup could be necessary also. Would these be what you're recommending? Looking at the network setup, it seems we have... Maybe six different routers here to provide the connections to the 9 machines in the office. That seems like it could be quite problematic, potentially, but my networking knowledge is rather poor overall. Thank you for the suggestion, though; it reinforces my idea on what the root cause might be.
- 😀
February 1, 2012 at 12:06 pm
It's been years since I've had to deal with ODBC, but I think I can point you in the right direction.
MS Access is holding an ODBC connection that it believes is open. When you attempt to use that connection (call a function), it discovers the connection is no longer open and throws you the S1T00 error.
This issue will happen when a single connection is held open indefinitely. ASFAIK, you can't prevent it from happening.
I remember writing a C++ program that held open a single ODBC connection. This was one of the codes I checked for on error. When this one came up, I disposed the connection, opened a new one and retried the statement. I don't know if you have this kind of code capability in MS Access.
Later on, I stopped using a single connection. Whenever possible, I use connection pooling. You should be able to turn on connection pooling in ODBC. At that point, ODBC should handle getting you a usable open connection from the pool.
February 1, 2012 at 12:42 pm
Excellent suggestion, Stephanie. Your comment reminded me to check the ODBC administration settings, and I discovered that connection pooling was on for SQL Server, as I'd thought; however, it was not enabled for Microsoft Access, which is quite an oversight. I'll be enabling pooling on all ODBC connections to Access and seeing if that fixes the problem. Thanks for the advice 😀
- 😀
February 2, 2012 at 7:24 am
Hrm. Seems like the previous solution may or may not have worked. I'm still getting reports of connection timeouts, but the majority users aren't saving the details of the errors for me to view. However, the one report that was saved is displaying a 08001 error; I don't know if this is what the others are experiencing or not, but it seems we've shifted to a new error type. Would someone have some suggestions on how to approach this problem?
EDIT: After much digging around, it seems as though the problem's cause was in the ODBC connection we were using to connect to the SQL Server. The server's name was being used as the connection location, which worked fine and let us connect to the server normally most of the time; however, on occasion, the server's name would be associated with our ISP's IP address rather than the server's IP address, for whatever bizarre reason. Changing the connection location to the server's static IP address seems to have elimiated the problem entirely. The support in this thread was much appreciated, though; without it, I probably would've continued to assume all was well with the DSN :hehe:
- 😀
July 22, 2014 at 8:55 pm
Hi Andrew,
How did you exactly do this? Enable MS Access for Connection Pooling in ODBC Administrator?
I have Windows 7 and could not seem to see any MS ACCESS as a driver in the Connection Pooling Tab. Listed are only SQL Server and SQL Server Native Client.
Your help would be greatly appreciated.
FYI, I am currently having the same S1T00 issues with my MS Access Front End saving to SQL Server.
Thanks
July 23, 2014 at 10:06 pm
Hrm. It's been quite some time since I had to work on this issue; I believe this MSDN article may have some pointers:
There's two different ODBC Data Source clients in 64-bit Windows systems, it seems; if you're using the wrong one, you may not be seeing the Access client in the Connection Pooling tab.
Ulitmately, though, I don't believe connection pooling fixed the issue I was having back then; it stemmed from the network configuration at the time. We had a router, plugged into a switch, plugged into a bunch of computers and a switch, and that switch went to a bunch of computers and another switch, which went... Yep. It wasn't pretty 😛
- 😀
January 26, 2017 at 12:55 pm
Andrew Kernodle - Thursday, February 2, 2012 7:24 AMHrm. Seems like the previous solution may or may not have worked. I'm still getting reports of connection timeouts, but the majority users aren't saving the details of the errors for me to view. However, the one report that was saved is displaying a 08001 error; I don't know if this is what the others are experiencing or not, but it seems we've shifted to a new error type. Would someone have some suggestions on how to approach this problem?EDIT: After much digging around, it seems as though the problem's cause was in the ODBC connection we were using to connect to the SQL Server. The server's name was being used as the connection location, which worked fine and let us connect to the server normally most of the time; however, on occasion, the server's name would be associated with our ISP's IP address rather than the server's IP address, for whatever bizarre reason. Changing the connection location to the server's static IP address seems to have elimiated the problem entirely. The support in this thread was much appreciated, though; without it, I probably would've continued to assume all was well with the DSN :hehe:
it is very useful information. we have the samw issue. would you please give me more detail about changing the connection location to the server's static IP addess. is it the PC running the application? thank you so much!
March 6, 2021 at 2:55 am
What worked for me was to set the server's firewall setting to always allow port 1433 connections.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply