Executing a View timesout through Open View

Hello All,

I have created a view that will potentially return a huge number of records around 500,000 rows. When i execute this view by clicking Open View from the SQL Management studio i get the following 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

However the query executes when paste the generated query of the view in the query designer window. It returns the 500,000 rows. I am wondering if there is some option that i will have to configure to return a large size result set in view designer. I also want to know if this is an inherent problem with view designer.

Any help regarding this will be really appreciated.

Satya

Open View creates a fully editable grid of the rows returned by the view so it is not surprising that it is choking on that much data.

First - this is not the view designer, the view designer opens when you select Modify, and that will let you edit the view definition.

If you do want to work with the data in the view through Open View, then after you select Open View, click the stop button down at the bottom of the window, next to the message that says Retrieving Data... It will stop and show whatever data it retrieved before you clicked the button. Now click the Show SQL Pane button on the toolbar (or Query Designer/Pane menu). In the SQL pane, edit the query by adding a WHERE clause to the SELECT statement that is selecting the data from you view. Now click the Execute SQL button (the exclamation mark) and it will run and return only the rows that match. Now you can selectively pull only a portion of the 500,000 rows at a time and work on them, then change the WHERE clause to select other rows. I can't believe you really want all 500,000 rows on screen at the time - and SQL Server doesn't think it's a good idea either :-)

|||

Hi,

Thanks for your reply. I didnt intend to see all the 500,000 rows of data at a time but did want to know the maximum volume that i can see when i click Open View. The stop button is also disabled when i click Open view and i get this error directly and so i cannot see a partial set of the data the way you had specified. Also i wrote <select a top 1 * > query enveloping the query generated by the view so that i could get atleast the first tuple of the query result but even this was aborted by the same error. The error comes immediately after a minute and no option is enabled for me to do anything once i click Open View or execute.

I was trying to see if this was a configuration issue in VIEW or if i was missing something else. The same query does give the intended results when i run in the query designer. Is there a capacity constraint for the Results grid pane in the View Designer as compared to normal query window.

Thanks again for your help and would appreciate if you can clarify the above too

satya

|||Do you by any chance have a TOP and an ORDER BY in the view definition, or is the view just a standard SELECT?|||It just seems that the CommandTimeout property is not updated in Management Studio when using View, and that it is always stuck at 30 sec. I have the same problem, but it seems impossible to fix.|||I have a view that returns over 500,000 rows, it takes about 5 minutes and never times out. I can click the Stop button at any time. I haven't changed anything from default so I'm not sure what's different.sql