Post reply

Time out issues when opening a View

  • June 27, 2008 at 6:29 am

    #122284

    Hi All,

    Very strange problem i am having on one of our servers.

    Running SQL 2005 we have a Database with views.

    I log into the server as Administrator and launch management studio

    I try and open a View and All views open fine except for one which gets the below error.

    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.

    When making a ODBC connection via Access and running the view from there then all is fine.

    But when i go back into Management Studio i get the error again.

    Now what is very odd is that there is a local windows account setup on the server for another user which has admin privlidges. If we log in as that user and connect and open studio management with either windows or sql authentication then again this view causing problems runs fine.

    But anyone else logging into Studio management can not run the view.

    Any help appreciated.

    P.S I have tried changing the execution time out option but to no avail.

    Why does this work with no timeouts in Access and only by studio management when logged into windows as a particular user?

    Thanks

  • June 27, 2008 at 7:38 am

    #835339

    You can do one this modify the view using definition and recreate the view.

  • June 27, 2008 at 8:20 am

    #835374

    Abhijit

    Thanks for the reply but please can you explain how to modify and recreate the view,

    Sorry should have said i am a newby LOL

  • June 29, 2008 at 11:09 pm

    #835972

    SELECT object_name(id), text FROM sysComments where text like '%CREATE VIEW%'

    you will get the definition from text copy it to Query analyzer, update the view definition as per your requirements.

    -Abhijit(MCP)

  • July 1, 2008 at 4:04 am

    #836530

    I have recreated the view as an administrator but it still times out with errors

    Again if ran under a particular account it runs fine.

    But we need this run under all accounts not just one.

  • July 1, 2008 at 4:59 am

    #836547

    I get that at times also, but I too have noticed that the same query, if run from a procedure (or from Access or some other external source) will complete. So I assume there is a time limit built into the Management Studio, but have not been able to find it. Even in BOL, while the error is described (search "timeout expired"), I see no limit parameter mentioned.

    (Probably, not many of us complain, cause we are ashamed to admit that we have a query that runs that long 🙂 )

  • July 1, 2008 at 8:00 am

    #836699

    If you have Query Analyser installed use that instead of Management Studio as I find that things that time out in Management Studio always complete in Query Analyser such as reindexing.

  • July 1, 2008 at 9:05 am

    #836759

    Thanks for the advise but I already have work arounds, and infact if i do a select * from View from a query window then all works but it does not give the answer to my customer as to why it is failing from the open view option for everyone except 1 particular user.

  • July 1, 2008 at 10:10 am

    #836797

    Good insight. I can confirm I also have a case where select * from view will run (about 00:01:35) but "Open View" times out.

    The problem is discussed in this thread:

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=943398&SiteID=1

    (but not solved, as near as I see.)

  • July 27, 2008 at 9:01 pm

    #848944

    I must say my thanks to Buxton69 for suggesting the query analyzer solution and jim for sharing the ms forum. My problem is similar ybol, but im using ODBC connection thru sql server.

    I used to do my view modification in the view designer without any trouble, that is with less than 20,000 data returns. Now that when i want to view around 100,000 data and above timeout expired error will appear. And normally i would haver to wait more than 2mins hoping it will work (which it did'nt!)

    With Query Analyser, it work effortlessly, my data returns around 19xxxx rows in 2:08mins! I hope this can help you too ybol

    ps: i do suspect that the management studio do have capacity limitation in its view designer...

  • July 27, 2008 at 10:33 pm

    #848951

    Under Tools | Options | Designers there is a transaction time-out setting. It should be set to 30 seconds. You can try changing the timeout here and see if that has any affect.

    Not sure it will have any affect, but it is worth a try.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • July 30, 2008 at 12:52 am

    #850270

    Yup I've tried it and yes it did gave me various results. The conclusion i can make is the higher the timeout was set, the longer it takes to extract data (about 18xxxx rows). I set it to 60000 and it return rows in 3:06mins and 1:57mins when timeout was set to 1.

    Like you've said, it's worth a try. Im just thrill that query designer shows data return rather than in the view designer.

    Thanks Jeffrey!

  • July 31, 2008 at 10:26 pm

    #851645

    It is worth to check what actually your view is (I mean the complexity of the select and joins within that view). See from the Execution Plan and try to find ways to improve the query or missing indexes. For me, increasing time out is not the best solution. Find out the problem first and use the time out setting as the last resort. You may find that you can reduce the query cost/time from 30 s to merely 2-3 s. If the tables underlying this view are rarely updated then you may consider using indexed view instead.

    Ivan Budiono

    Ivan Budiono

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply