Re: timeout expired on View

Thanks for reply >>Erland Sommarskog
But I have already set - Connection time-out: 600 seconds and Execution
timeout:600 seconds.
Also in Tools-->Options-->Query Execution: Execution time-out:0
In Designers menu - Transaction time-out: 600 seconds
But it also not work I have the same problem - (“Error Source: .Net
SqlClient Data Provider. Error Message: Timeout expired. The timeout period
elapsed prior to completion of the operation or the server is not responding.
”)

And how to solve problem with MS Access (ODBC-call failed [Microsoft][ODBC
SQL Server Driver]Timeout
expired (#0))

"Erland Sommarskog" wrote:

Denny (Denny@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
I have the same problem I'm running SSMS with MS SQL Server 2005. When I
try to open view from SSMS interface, from local server (left mouse
button on the view?Open view) it shows me “Executing Query” and after 30
seconds write such error:
“Error Source: .Net SqlClient Data Provider.
Error Message: Timeout expired. The timeout period elapsed prior to
completion of the operation or the server is not responding. ”
When I try execute only select from this view it tooks me about 1min 20
sec and its allright-I see the results. I resolved this problem locally
when I changed in registry “SQLQueryTimeout”
(HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL
Server\90\Tools\Shell\DataProject\SQLQueryTimeout) from 30 sec. to 600 on
Server, but only locally now from SSMS I can run Open view and see the
execution result. When I run this procedure from remote PC (client) SSMS I
see the same problem
(“Error Source: .Net SqlClient Data Provider. Error Message: Timeout
expired. The timeout period elapsed prior to completion of the operation
or the server is not responding. ”)

Also when I try to run this view from remote PC from MS Access it shows me
such error: “ODBC-call failed [Microsoft][ODBC SQL Server Driver]Timeout
expired (#0)”

I can’t find any solution.

This query timeout is a client-side setting, so there is no choice but
to set it every client you use. You don't have to hack the registryu to
address it it SSMS; the setting is exposed under Tools->Option->Designers.
Most client APIs have this timeout set to 30, but the query window in
Mgmt Studio uses 0 - wait forever.

If you don't want to change the timeout everywhere, there is only way
out: speed up your view.

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx


.