I'm running SSMS with SQL Server 2008.  Under SSMS Options, Execution
time-out is set to 0 seconds (i.e. no time-out), and SET LOCK TIMEOUT is set
to -1 milliseconds. I have a certain SQL command that takes a while to
execute.  If I run it in the Query window as a straigth SQL command, it will
execute after a minute or two.  However, if I create a View using the exact
same SQL command, pull up the View in SSMS in Design mode, and execute the
View, the following happens:  First, at the bottom of the screen, it says
seconds, a window comes up with the error message: "SQL Execution Error." ...
The timeout period elapsed prior to completion of the operation or the server
is not responding."  First Question:  How can I tell SSMS not to timeout on
the View?

Incidentally, one reason I use a View is to be able to edit the rows that
are returned.  If I use a straight SQL command in the Query window, I cannot
edit the rows returned.  Second Question: Is there a way to run a query in
the Query window and tell SSMS to allow me to edit the results?
--
Ed

To perform long-running operations via SSMS, go to Tools->Options->Designers
and in the Table Options, set the "Transaction time-out after" value to
something more (such as 90 if your operation takes less than 3 mins.)

You can't edit rows that way. You better create your custome application to
modify your data according to your needs.

--
Ekrem Önsoy

Hi Ed,

Thank you for using Microsoft MSDN Managed Newsgroup. My name is Mark Han.
I am glad to work with you on this issue.

From your description, I understand that when you run query in view design
mode, a timeout expired error happens. If I have misunderstood, please let
me know.

in order to address your concern, I would like to explain the following.
1 Based on your description, I know that the reason for creating a view is
that you would like to modify the result of the query manually. however, it
is not allowed for us to modify the result of the query directly. we need
to save the result first.

2 For your convenience, it is suggest to use select into command to save
the result of thequery into a table and we can execute the sql command in
the the Query window. there is an article to share with
you:http://msdn.microsoft.com/en-us/library/ms189499.aspx

3 also, please To perform long-running operations via SSMS, go to
Tools->Options->Designers and in the Table Options, set the "Transaction
time-out after" value to something more (such as 90 if your operation takes
less than 3 mins.)

Besides, please tell me if you have concern on the time the query executes.

If there is anything unclear, let me know.

Have a nice day!

Best regards,
Mark Han
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@microsoft.com.
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or
a Microsoft Support Engineer within 2 business day is acceptable. Please
note that each follow up response may take approximately
2 business days as the support professional working with you may need
further investigation to reach the most efficient resolution.
The offering is not appropriate for situations that require urgent,
real-time or phone-based interactions. Issues of this nature are
best handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx

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

OK on points 1 and 2.  Regarding your point 2, it's just as easy to create a
View to modify the data as it is to put the data into a new Table that can be
edited.

The Designers option was set to 60 seconds, and I increased it to 120.
Sometimes I still get the timeout error after about 30 seconds, but other
times I do not.
--
Ed

Hi Ed,

Thank you for the update. I'm glad that my point 2 is helpful. Since we can
run the query(select into) in Query window to work around the issue, could
you please tell me your concern on the work around?

Besides, based on my experience, the issue might be by-design. The query
can always run successfully in Query window ,but in the View Design mode,
timeout expired  might happen. The difference with the 2 scenario is the
platform the qurey run; and the 2 mode is designed; it is not allowed to
change them.

Hope the above helpful. Thanks.

Best regards,
Mark Han
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@microsoft.com.
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

Hi, everybody
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.
What shell I do? Any suggestions?

Denny (Denny@) writes:

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@sommarskog.se

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

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

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@) writes:

I see now that in your original post that you mentioned an application.
In a .Net app, you set the query timeout on either the Connection object
or the Command object with the CommandTimeout property.


If you are using ADO, the answer is the same for ADO .Net. If you are
using something else, I don't know the answer, and it's more of an
Access question.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

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

I solve the problem with clients with MS Access by increasing the ODBC
timeout from 60 (by default) to 300. This can be done by opening the MS
Access SQL-Pass-Through query in Design mode and right clicking on the window
then accessing the properties. One of the properties is ODBC timeout.

But it still not resolve the problem with MS SQL Server Management Studio. I
have only MS SQL Management studio on client PC and when I executing a view I
have the same problem! Where I can change that query timeout on that client
PC in Management Studio?

SSMS| Tools | Options|Query Execution|SQL Server| Query Timeout.

hth

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205  (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com  http://betav.com/blog/billva
____________________________________________________________________________________________