Re: timeout expired on View

I have a customer and their coding department experience this problem as well and I checked with them the Connection Timeout (in the client app's connection string and in SqlConnection' s Timeout property) and the problem persists.

At SQL Server side, the default value for time out is indefinite (unless you change it manually). So when I join the pieces of the puzzle, this makes me think the problem is not actually a time-out problem which occurs at client side or server side. Because the application sometimes throws this error in 5 seconds and sometimes after 1 minute.

I believe that the reason of this problem is something else (anything, maybe a missing object at the SQL Server side or a deathlock etc.) than a direct timeout problem.

--
Ekrem Önsoy



"Denny" <Denny@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:B97AE32B-BE5D-4B3A-8203-D0296B428FE5@xxxxxxxxxxxxxxxx
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



.

Loading