Time out issues when opening a View

Question

Post reply

Time out issues when opening a View

ybol

Ten Centuries

Points: 1325
More actions
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

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

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

abmore Hall of Fame Points: 3628 More actions · Answer 1

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

ybol Ten Centuries Points: 1325 More actions · Answer 2

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

abmore Hall of Fame Points: 3628 More actions · Answer 3

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)

ybol Ten Centuries Points: 1325 More actions · Answer 4

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.

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 5

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 🙂 )

Buxton69-502449 SSCrazy Points: 2026 More actions · Answer 6

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.

ybol Ten Centuries Points: 1325 More actions · Answer 7

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.

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 8

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

tqlashot_kk Valued Member Points: 52 More actions · Answer 9

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

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 10

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
Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

tqlashot_kk Valued Member Points: 52 More actions · Answer 11

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!

i6004835 Mr or Mrs. 500 Points: 529 More actions · Answer 12

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