Share via

SQL Native Client Query Timeout

  • Article
  • 04/30/2009

Question

Thursday, April 30, 2009 11:29 AM

HI,
I have an Access appication that is linked to a SQL Server backend, I have instances of this application running both SQL 2008 and SQL 2005 using both the SQL Native Client and Native Client 10.

in both of these i have resently been getting reports of a Time out error "ODBC--Call Failed Microsoft Native Client Query Timeout Expired(#0)" .

The linked Table that i am currently testing this with is a SQL View, it seems that if the View takes longer than 60 seconds to return it's data then i get this error message.

I have tried paluing aroudn with the ODBC connection string 
orginal it was
ODBC;DRIVER=SQL Server Native Client;SERVER=Edge-devsrv\sql2008;Trusted_Connection=Yes;DATABASE=devmaster;
I cahnge this to
ODBC;DRIVER=SQL Server Native Client 10.0;Connection Timeout=600;Timeout=600SERVER=Edge-devsrv\sql2008;
Trusted_Connection=Yes;DATABASE=devmaster;

but is still get the same error.

can any one help ?

Regards Dagz

All replies (10)

Thursday, April 30, 2009 1:51 PM ✅Answered | 1 vote

Connection Timeout is not same as query timeout. Query timeout cannot be specified in a connection string.
You can use SQL Profiler to check which SQL query was executed to further troubleshoot it.

Thursday, April 30, 2009 3:29 PM

Hello,

Zheng is absolutely right : no quert timeout on the level of the connection string.

But you may have a solution :

- for a SqlCommand ( System.Data.Sqlclient ) :
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout.aspx

in the both case, the default query timeout is 30 seconds ( you may increase it but it is better to have a look on your query to see whether it is "well done", as
Zheng suggested you ).
In SQL Server 2008, even SQL Server Management Studio Express (SSMSE ) is able to help you with the generated plans.

Have a nice day

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Thursday, April 30, 2009 5:52 PM

You should first see if you can make a trusted connection using ODBCAD32.exe and the connection settings you're trying (driver, server, database, etc.). Once you can make a connection, try building your connection string again.

Note also that you need to be more careful using connection string keywords. I don't understand what the "ODBC;" is doing in your connection string. Also, be careful to not leave out the semi-colon separator between keyword values. For the driver name keywords:

For the SQL 2005 driver, use:

DRIVER=SQL Native Client;

 

For The SQL 2008 Driver, use:

DRIVER=SQL Server Native Client 10.0;

~Warren

 

Thursday, April 30, 2009 10:25 PM

Hi,

Papy has provided great links, but they are related to SqlClient in the .net framework and not to the ODBC driver in SQL Server Native Client.
Zheng is pointing in the right direction - the query timeout is a property of the ODBC statement, not the ODBC connection. The good news for Access is that the Queries have "ODBC Timeout" property (at least in Access 2007 where I am looking at). The default seems to be 60 (seconds), just increase it to accomodate your queries.

In addition, as Zheng pointed out, it's a good idea to investigate the query performance (using SQL Profiler, looking at its execution plan, etc) and optimize it via creation of indexes, etc.

HTH,
Jivko Dobrev - MSFT

This posting is provided "AS IS" with no warranties, and confers no rights.

Friday, May 1, 2009 6:36 AM

Hello Jivko,

I have given the link towards the ODBC command Timeout property in my post ( this link was not related to SQLClient but ODBC, i know that each driver has a specific namespace derived from the same "generic" namespace , in fact , the CommandTimeout is coming from this generic namespace.

A general remark for the MSFT applying only to the new moderators MSFT ( the "old" ones like Mike Wachal, Jens SuessMeyer or Michiel Worries are not doing this error , some new ones like Alok Parmesh are behaving correctly) : don't post and propose your post as answer, i consider that's a bad behavior trying to close quickly a thread.
I consider that we are here to help people having problems, not to increase the percentage of answered threads.For me, if you mark your post as a proposed answer , you are trying to oblige the OP to mark your post as the good answer and the OPs don't like that behaviour : they may go on non Microsoft Forums to get answers.
Moreover, you are spoiling the opinion about Microsoft.
I am sorry to complain against you, but it will be great to "warn" the other MSTF that such behaviour is not pleasant ( you are one of the better new MSFT  moderators and i have appreciated many of your posts )
** added
This remark is also for some MVP .
I generally appreciate the answers of the MSFT ( and MVP ) but i have discovered that sometimes their posts are not answering to the problem of the OP or are not enough precise and to propose their posts as answers may be considered as "tag intimidation". And i hate this behaviour.
As i am saying often, i am only a tiny moderator but i am trying to do my moderator work in the less bad way ( i am an human and i am not perfect ). And it is true for other people.

I prefer that this disput stops here.

Have a nice day

PS : my first post was just a little add-in to the short post of Zheng and i gave 2 links because this forum is for all drivers and the SqlClient dll is also freqently used.
**Command.Timeout is coming from :
http://msdn.microsoft.com/en-us/library/system.data.idbcommand.commandtimeout.aspx
which is described as having a default value of 30 seconds

This question appears already several times
http://social.msdn.microsoft.com/Forums/de-DE/sqldataaccess/thread/b03f0724-8e85-4263-9ab5-31bf4e1b9f8f
http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/07c0582b-7d79-4242-8047-6b4090090748/
( with an excellent post of Zlatko MSFT )
http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/3cb765b6-a697-460a-8d29-db27154c54c8/
( answer form a MVP )
http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/cccea79b-f7c8-4fd4-85b8-7182d92d8f91/
That's the result of a little research

Have a nice day

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Friday, May 1, 2009 7:46 AM

HI, yes you can set the time out on access query, sadly the query that i am executing is a basic select statement on a Linked table and as far as i know you can not set the timeout on the linked table.

the error will occur if i try to open the table which is linked to a SQL View.

the query optimisation is worth looking at but the issue occurs due to the amount of data involved we are talking 10's of millions of records that are being pulled back. any thin under 5 million seems ok but when we go over that we start getting problems.

Regards Dagz

Friday, May 1, 2009 9:20 AM

Hello Dagz,

Please, could you tell us how you have build your table to your SQL view ? ( i believe to have seen a thread about a similar topic answered by Zlatko, but i may go wrong ).

It is sure that when you are selecting 10,000,000 records , the execution can need time.

Some explanations from you are necessary to try to help you more efficiently. We are waiting for your feedback.

Have a nice day

PS : a study of the execution plan would be useful as there are (maybe) ways to simplify your view ( to speed it up ).
Please, could you also explain what you mean with "the error will occur if i try to open the table which is linked to a SQL View. " ?
English is not my mother tongue , so i am not sure to have understood your sentence ( it is not you who is not clear, i am asking precisions to be sure that you will get a correct and complete answer in the quickest time ).
Are you sure that all the parameters for the server and the database are correctly set up on the level of memory ? ( i have met this problem )

To obtain help on the way to optimize your view ( and the fact you are using a linked table ), there are 3 possible forums :

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Friday, May 1, 2009 1:04 PM

Hi,

i might have found the issue and it does look like it might be a design issue.

The view is summarising data which somes from a UDF, this UDF gets it data from an other udf which get's it's data froma table that has 10,000,000 records in it. so i think we need to look and and re think this section of the software as it not working very well at all.

thanks for the help.

Regards Dagz

Friday, May 1, 2009 5:46 PM | 1 vote

Hi Papy,

Thanks a lot for your comments and feedback. I will do my best to follow your advices. I think your feedback would be very valuable for all of us in the MSFT team who are trying to help our Customers and answer their questions. I have to disagree with some of your points though and because I don't believe this is the place to discuss them, I've created a temporary e-mail for the discussion, please e-mail me at jdobrev@hotmail.com. I will not use the account for anything else.

One point would be useful for our Customers here though: anyone who has posted a question can unmark a proposed answer if he doesn't feel it's right. I don't believe the moderators need to do that instead of the person posting. I haven't unmarked anyone's answer ever - it is the poster's judgment that is right here. Customer is always right, correct?

One technical clarification here: your link (http://msdn.microsoft.com/en-us/library/system.data.idbcommand.commandtimeout.aspx) points to the .NET SqlClient (System.Data) implementation of the command timeout. Dagz is using ODBC with SQL Server Native Client. The relevant timeout is a property (SQL_ATTR_QUERY_TIMEOUT) of the ODBC Statement object and is documented here: http://msdn.microsoft.com/en-us/library/ms712631.aspx. The implementation is entirely different despite the default value being the same.

Regarding the ODBC timeout in the MS Access, here's what the documentation says:

===============================================
You can use the ODBCTimeout property to specify the number of seconds Microsoft Access waits before a time-out error occurs when a query is run on an Open Database Connectivity (ODBC) (ODBC database: A database for which an Open Database Connectivity (ODBC) driver that you can use for importing, linking to, or exporting data is supplied.) database.

Setting
The ODBCTimeout property is an Integer value representing the number of seconds Microsoft Access waits. The default is 60 seconds. When this property is set to 0, no time-out error occurs.

You can set this property by using the query's property sheet or Data Access Objects (DAO) (Data Access Objects (DAO): A programming interface to access and manipulate database objects.) in Visual Basic code.

Remarks When you're using an ODBC database, such as Microsoft SQL Server, there may be delays due to network traffic or heavy use of the ODBC server. The ODBCTimeout property lets you specify how long Microsoft Access waits for a network connection before a time-out error occurs.

Please correct me if I am wrong, but this seems to match the statement timeout which would be used in ODBC when running a query against an ODBC driver (in this case - SQL Server). Marking the recommendation as a possible answer seems perfectly logical to me. You are right - we are not in it for the metrics and the # of answered topics, but for the good of the Customer. However, if a thread is unanswered we have the entire team redundantly reading it over and over, which is unproductive. If I believe I have provided the answer I let the Customer be the judge for that.

Anyway, as Zheng and I both recommended, Dagz has gone in the right direction: optimize the query instead of try to accomodate its long execution on the client.

Again, I very much appreciate your feedback, I will make sure to share it with our team. If you want to continue the discussion, let's do it over the designated e-mail account above.

Thanks,
Jivko Dobrev - MSFT

This posting is provided "AS IS" with no warranties, and confers no rights.

Friday, May 15, 2009 8:59 PM

It is my opinion that an answerer should not propose their own remarks as "answered" but rather let that be the determination of the user or other moderators. I have been guilty of this, but I have chosen to let others decide the fate of my response rather then me.

Just my humble opinion.John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com