jwcolby
jwcolby at colbyconsulting.com
Wed May 14 04:59:57 CDT 2008
Stuart, This one is fascinating: > Enterprise Manager, select the Server, right click and select properties, check the "query timeout" field on the "Connection" tab ? http://blogs.msdn.com/ialonso/archive/2007/12/04/sql-execution-error-timeout-expired-the-timeout-period-elapsed-while-opening-view-from-ssms.aspx Apparently SQL Server uses some third party code in it which references a registry key for this value. However... while the value does exist in the registry, and I can change the value, and the value shows that it changed when I close and reopen RegEdit... After closing and reopening SSMS, the value is set back to 30. I guess SSMS REALLY wants that value set to 30? ;-) John W. Colby www.ColbyConsulting.com jwcolby wrote: > Stuart, > > > Enterprise Manager, select the Server, right click and select > properties, check the "query timeout" field on the "Connection" tab ? > > Query timeout is set to 600 (seconds). These views are only taking > ~2:40 minutes to run. I have been down this road before. Apparently > that setting is for QUERIES, and apparently the SSMS treats views by a > different set of rules than queries. > > > Use "SELECT TOP (99.9999999999999) PERCENT..." > > Interesting. Even more interesting, once I did that in one query, the > rest of the queries sort "properly" even though the underlying view has > no TOP() statement at all. > > Perhaps behind the scenes SSMS is leaving the last used TOP statement > hanging around (sounds like another bug). > > So the takeaway is that it is a "feature" of SSMS that a sorted view > works at all and don't depend on it. > > Just to straighten me out on this though... the theory is that a view is > "just another table" and tables are by their nature unsorted? I do > understand the "tables are by their nature unsorted" thing. Because a > view is based on a SQL Statement, I always considered a view "a query" > (a la Access), which appears to be untrue for whatever reason. > > John W. Colby > www.ColbyConsulting.com > > > Stuart McLachlan wrote: >> On 13 May 2008 at 16:34, jwcolby wrote: >> >>> Well, we are talking about a GroupBy Cnt query here. The GroupBy field >>> is what is ordered on, and is specifically ordered on that field in the >>> underlying view. >>> >>> Unfortunately I cannot execute the view since the view takes about 40 >>> seconds to complete and MS times out in 30, which no one here has >>> managed to show me how to prevent. >>> >> Enterprise Manager, select the Server, right click and select properties, check the "query >> timeout" field on the "Connection" tab ? >> >> >>> Therefore I am forced to create a query and paste the query into that in >>> a SELECT * FROM SomeView SQL statement. Given that the view is ordered, >>> you (I) would expect a query directly based on that to be ordered unles >>> specifically reordered in some manner. Not so for whatever reason. >>> >> Use "SELECT TOP (99.9999999999999) PERCENT..." >> see >> http://executioniseverything.blogspot.com/2007/01/order-by-in-views-sql-server-2005.html >> >> >> _______________________________________________ >> dba-SQLServer mailing list >> dba-SQLServer at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >> http://www.databaseadvisors.com >> >> > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >