March 9, 2009 at 9:17 am
Im attaching to files with screen images. Both were obtained using the same SQL Server Management Studio session. The first one (ResultOK.rtf) shows the correct execution of a SQL query. The second one (ResultNOK.rtf) shows the failure of the same query after around 30 seconds of execution. The two files are zipped into one file (Results.zip)
This result corresponds to the infamous Query Time Out (sorry it is in Spanish) problem. I have made many seaches on Internet about this problem. To many answers but none of them effective. I tried all of them without solving the problem.
My problem is I'm getting the same problem with several application programs. In my seach to find a solution I found the above mentioned situation. Maybe some of you may tell me I found a workaround but this is not the case. I just cut and paste the SELECT statement form my application to run it on SSMS. I have the problem in my application. However I hope if I can solve the SSMS problem then my application will run flawlessly.
Just in case: Everything is beeing run on the same machine with Windows 2003 Server R2. The database is SQL Server 2005 Standard. SSMS run in the same machine as SQL Server 2005 Standard.
Jorge Cabrera
March 9, 2009 at 9:20 am
It looks like one of the connections has a 30-second timeout enforced on it, while the other doesn't. Per the screen shot, the one that worked took over a minute to finish (if I'm reading it correctly).
Check if SSMS has a timeout value set for queries. It's in Options.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 9, 2009 at 9:26 am
Thanks for your interest. I checked the query time out in option and it is set to 0 meaning unlimited.
Remember the two screen shots are from the same SSMS session. How can they have different time out values?
Regards
Jorge
March 9, 2009 at 10:48 am
Just because the connections are from the same SSMS instance doesn't mean they have the same settings. One of those connections is showing what I consider normal color coding on the TSQL and the other is not. They look like they're coming from different locations. Even though the queries are the same against the same table, there is a difference in those two connections. What I can see of the properties windows between the two is different as well.
Identify the differences and I think you'll identify the problem.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 9, 2009 at 5:14 pm
The query with the problem was obtained by right clicking on the IN_Solicitudes table, selecting open table, rigth click on the results and selecting Sql Pane. The upper panel showed the Sql statement and I modifed it with the WHERE Clause.
The ok query was obtained by openning a New Query, copy and paste the original query and adding the use Indicadores statement.
I was trying the whole day to find any differences between the two without any success. The only thing I have noticed is that the timeout seem to be tied to the execution time between the query execution and the display of the first row. It has nothing to do with the table size because if I execute a SELECT * FROM IN_Solicitud (without the ORDER BY seleccion_fecha_evento) the query runs quite well.
However I have not found any argument or parameter for something like timeout before displaying the first row.
March 10, 2009 at 5:28 am
I suspect using OPEN TABLE is the issue. If you go out & bounce around in Google, there are a lot of people experiencing problems with it. I couldn't find anything that said whether or not it uses a different connection than the TSQL query window, but I think it must.
I don't have the 2005 interface, but I tried it out using the 2008 interface. If you put Profiler on to see the connections as they come in, you'd be able to see the network protocols and settings for the connection. Using the 2008 interface, they're all the same from the edit table and a select statement.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply