SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server Error S1T00


SQL Server Error S1T00

Author
Message
Andrew Kernodle
Andrew Kernodle
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1336 Visits: 8135
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 Smile.

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.

- :-D
patrickmcginnis59
patrickmcginnis59
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1543 Visits: 2333
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.
Andrew Kernodle
Andrew Kernodle
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1336 Visits: 8135
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.

- :-D
Stephanie Giovannini
Stephanie Giovannini
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2431 Visits: 1541
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.
Andrew Kernodle
Andrew Kernodle
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1336 Visits: 8135
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 :-D

- :-D
Andrew Kernodle
Andrew Kernodle
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1336 Visits: 8135
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

- :-D
immartianx
immartianx
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 11
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
Andrew Kernodle
Andrew Kernodle
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1336 Visits: 8135
Hrm. It's been quite some time since I had to work on this issue; I believe this MSDN article may have some pointers:

Article

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 :-P

- :-D
xliang
xliang
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 7
Andrew Kernodle - Thursday, February 2, 2012 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

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!




Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search