I'm supporting an IIS web application that constructs and sends SELECT statements to SQL Server. Sometimes the statements are not very efficient or are against quite large tables so they take three or four minutes to complete when run from SQL Management Studio. When the statements are sent from the application, the following time-out is reported by it:
ERROR [HYT00] [Microsoft][ODBC SQL Server Driver]Timeout expired SQL: SELECT ... large statement here ...
It's not possible to (immediately) improve the SQL statements sent so I need to temporarily increase whatever time-outs are being hit. But I cannot seem to find a time-out that corresponds to this error message. I am hoping that someone here can tell me what time-out it refers to and where it can be viewed/changed?
You can alter your connection string and add Timeout=[seconds] to your connection string Connection String MSDN.
Be aware though that the HTTP request can time out too, so make sure that your SQL is not more than that. Then there is the user, very annoying factor ;-) this implementation can also time out- loose interest in your site.
better fix the issue by splitting the table over several disk files and add CPU or Ram. one thing that also helps is to query against a view with the same name as the table and remove access to the table. like this you can tune the access on a location that does not have you changing the application code.
There are lots of things we DBA's do to fix programmer's errors, the encapsulation method mentioned is only one of many options.
Hope it helps
Walter