"Ed White" <ewhite@newsgroups.nospam> wrote in message
news:444428B9-0C83-4F9E-A3D3-42506D2CFA29@microsoft.com...
> 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
> "Executing Query. Waiting for response from data source."  After about 30
> seconds, a window comes up with the error message: "SQL Execution 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."  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

"Mark Han[MSFT]" wrote:

> 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: msd***@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.
> =========================================================
>
>

"Mark Han[MSFT]" wrote:

> 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: msd***@microsoft.com.
> =========================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
> =========================================================
>
>

> 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.

> expired (#0))

"Erland Sommarskog" wrote:

> Denny (De***@discussions.microsoft.com) 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, esq***@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
>
>

"Denny" <De***@discussions.microsoft.com> wrote in message
news:B97AE32B-BE5D-4B3A-8203-D0296B428FE5@microsoft.com...
> 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 (De***@discussions.microsoft.com) 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, esq***@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
>>
>>

"Erland Sommarskog" wrote:

> Denny (De***@discussions.microsoft.com) writes:
> > 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. ”)
>
> 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.
> 
> > And how to solve problem with MS Access (ODBC-call failed [Microsoft][ODBC
> > SQL Server Driver]Timeout  expired (#0))
> 
> 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, esq***@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
>
>

"Denny" <De***@discussions.microsoft.com> wrote in message
news:BCCFA693-D04F-4036-A166-8A77F4B84FA3@microsoft.com...
> 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?
>
> "Erland Sommarskog" wrote:
>
>> Denny (De***@discussions.microsoft.com) writes:
>> > 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. ”)
>>
>> 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.
>>
>> > And how to solve problem with MS Access (ODBC-call failed
>> > [Microsoft][ODBC
>> > SQL Server Driver]Timeout  expired (#0))
>>
>> 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, esq***@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
>>
>>