February 25, 2011 at 9:28 am
Hello, I have a big problem with standard SQL odbc driver and SQL Native Client 10.0.
I have an Access2003 application with tables linked to SQL Server2000, sometime some queries fails, after a timout of 60sec I recieve the error ODBC: Connection to 'servername' failed. If I retry for 3-4 times all works then the error again.
I have set query timeout to 0 in every access query and in the register key of windows but nothing change. With expansive non MS odbc sql driver I don't have the problem. Someone know this bug? What can I do? Thanks.
February 25, 2011 at 10:59 am
So you have set the timeout for the DSN (odbc defined connection) you are using to link those tables to 0 and are still getting timeouts?
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 25, 2011 at 11:15 am
Yes, in my Access query (on SQL view) I set the odbc timeout to 0 and then in ODBC.INI registy key and in Access Settings options too. Then I trayed with different version of sqlsrv32.dll.
Sometimes, after some test and errors on a pc the error disappears and the query works fast every time I run it, then after a while the prob returns. The prob depends by the clients not sql server because if I have this prob in some pc others runs well and viceversa!
While the query is thinking, before you see the error, telnet to port 1433 does not work.
February 25, 2011 at 12:07 pm
I was more thinking of changing it in the odbc component of the individual machines. So, under control panel, administrative tools, data sources, then either under system or file dsn tab. Set the option for the DSN you are using.
When you say that you are changing it in access I'm assuming then you have a file DSN that gets shared around on the other computers. What I don't know is if that setting becomes universal when run on the other computers.
I'm grasping at straws a bit here but trying to be helpful. Thanks for being patient. 🙂
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 25, 2011 at 1:24 pm
Thanks David for this brainstorm, no, under odbc data sources I've created a dsn file configuration using SQL Server driver (on DSN driver definition I don't see an timeout setting), then I saved the dsn file on my server so I used it to connect the sql tables to Access2003 application. This Access application is deploy in different clients. In this Access app ther's some queries that join the sql tables. If you open the structure and propriety of Access query you can set the timeout in sec., the default is 60, I set this to 0 (no limit), but despite this sometime (only for big queries) the client lose the connection to sql server, after +-60sec the 'ODBC:connection to 'sqlservername' failed' appears. :\
February 25, 2011 at 1:30 pm
And is that true on every machine that runs large queries that when they hit 60 seconds they get a timeout? Do you get the same?
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 25, 2011 at 1:43 pm
yes but not always, if you run&close&run the query for 3-4 times the query runs well then the next time stops and after 60secs the error. This on different clients in different times at random. Very strange damn problem! I'm thinking if is a bug of MS Sql odbc client... Other strange things is that sometime the error disappear (you can run the query whenever you want without errors).
February 25, 2011 at 1:47 pm
Same problem on win7 or winXP on sql2000 or sql2005, with office access 2003 or 2007 or 2010.... could be a odbc client driver problem.. I dont know what think...
February 25, 2011 at 1:48 pm
I guess what I am asking is - If any query from this MS Access program on any machine runs longer than 60 seconds will you ALWAYS get the timeout? If so, then there is still a timeout setting that is not correct.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 25, 2011 at 1:51 pm
no not always, you can launch the same long query well for three times and then the 4th time fails
February 25, 2011 at 1:55 pm
Sorry if my questions are seemingly nitpicky, I'm just trying to make sure that I understand completely. 🙂 Thanks for your patience.
Have you ever had the query run longer than 60 seconds and come back successful?
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 25, 2011 at 2:05 pm
I dont have query longer then 60 sec., this query have more jons but runs in 20-30 secs, but some time the query stop and after 60-70 seconds the error appear
February 25, 2011 at 2:22 pm
simone.alessandri (2/25/2011)
I dont have query longer then 60 sec., this query have more jons but runs in 20-30 secs, but some time the query stop and after 60-70 seconds the error appear
Right but if the query hasn't completed returning the results then it is still running. So, if you are always seeing a timeout after 60 seconds of a query running then there is still a timeout setting that is off somewhere.
Does what I am saying make sense? Let me know if not.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply