I am not sure what client tool/application you are using. If you are using SSMS, click on options and goto connection properties. Change the value of Execution timeout.

However this option may give undesired results as a poorly written query may wait for ever and block all other connections from accessing your resources. Use it with care.

G.R.Preethiviraj Kulasingham

Here is the code for my View ..
CREATE VIEW dbo.abc
AS
SELECT a.INVOICE_DETAIL_ID, b.INVOICE_HEADER_ID, a.LINE_ITEM_CODE, c.LINE_ITEM_DESCRIPTION, d.GROUP_TITLE, a.QUANTITY, a.RATE,
                          (SELECT DATE_OCCURRED
                            FROM dbo.INVOICE_DETAIL_DATES
                            WHERE (INVOICE_DETAIL_ID = a.INVOICE_DETAIL_ID) AND (LINE_ITEM_CODE = a.LINE_ITEM_CODE) AND (LINE_ITEM_DATE_ID = 1))
                      AS date_ordered,
                          (SELECT DATE_OCCURRED
                            FROM dbo.INVOICE_DETAIL_DATES AS INVOICE_DETAIL_DATES_5
                            WHERE (INVOICE_DETAIL_ID = a.INVOICE_DETAIL_ID) AND (LINE_ITEM_CODE = a.LINE_ITEM_CODE) AND (LINE_ITEM_DATE_ID = 2))
                      AS date_completed,
                          (SELECT DATE_OCCURRED
                            FROM dbo.INVOICE_DETAIL_DATES AS INVOICE_DETAIL_DATES_4
                            WHERE (INVOICE_DETAIL_ID = a.INVOICE_DETAIL_ID) AND (LINE_ITEM_CODE = a.LINE_ITEM_CODE) AND (LINE_ITEM_DATE_ID = 3))
                      AS date_cancelled,
                          (SELECT DATE_OCCURRED
                            FROM dbo.INVOICE_DETAIL_DATES AS INVOICE_DETAIL_DATES_3
                            WHERE (INVOICE_DETAIL_ID = a.INVOICE_DETAIL_ID) AND (LINE_ITEM_CODE = a.LINE_ITEM_CODE) AND (LINE_ITEM_DATE_ID = 4))
                      AS date_service_begin,
                          (SELECT DATE_OCCURRED
                            FROM dbo.INVOICE_DETAIL_DATES AS INVOICE_DETAIL_DATES_2
                            WHERE (INVOICE_DETAIL_ID = a.INVOICE_DETAIL_ID) AND (LINE_ITEM_CODE = a.LINE_ITEM_CODE) AND (LINE_ITEM_DATE_ID = 5))
                      AS date_service_end,
                          (SELECT DATE_OCCURRED
                            FROM dbo.INVOICE_DETAIL_DATES AS INVOICE_DETAIL_DATES_1
                            WHERE (INVOICE_DETAIL_ID = a.INVOICE_DETAIL_ID) AND (LINE_ITEM_CODE = a.LINE_ITEM_CODE) AND (LINE_ITEM_DATE_ID = 6))
                      AS date_open_date, h.NOTES, a.QUANTITY * a.RATE AS TotalBeforeAdjustments, f.DISPLAY_NAME AS created_by, a.CREATED,
                      g.ADJUST_AMOUNT_BY, g.REASON_FOR_ADJUSTMENT, a.QUANTITY * a.RATE + ISNULL(g.ADJUST_AMOUNT_BY, 0) AS GrandTotal, g.ADJUSTED,
                      i.DISPLAY_NAME AS adjusted_by, k.ADJUSTMENT_REASON
FROM dbo.INVOICE_DETAIL AS a WITH (nolock) INNER JOIN
  dbo.INVOICE_HEADER AS b WITH (nolock) ON a.INVOICE_HEADER_ID = b.INVOICE_HEADER_ID INNER JOIN
  dbo.LINE_ITEMS AS c WITH (nolock) ON a.LINE_ITEM_CODE = c.LINE_ITEM_CODE INNER JOIN
  dbo.LINE_ITEM_GROUPS AS d WITH (nolock) ON a.GROUP_ID = d.GROUP_ID INNER JOIN
  dbo.INVOICE_DETAIL_DATES AS e WITH (nolock) ON a.INVOICE_DETAIL_ID = e.INVOICE_DETAIL_ID INNER JOIN
  ICLEAR_CENTRALDB.dbo.USER_CONTACT_INFO AS f WITH (nolock) ON a.CREATED_BY = f.CONTACT_INFO_ID LEFT OUTER JOIN
  dbo.INVOICE_ADJUSTMENTS AS g WITH (nolock) ON a.INVOICE_DETAIL_ID = g.INVOICE_DETAIL_ID LEFT OUTER JOIN
  dbo.INVOICE_NOTES AS h WITH (nolock) ON a.INVOICE_DETAIL_ID = h.INVOICE_DETAIL_ID LEFT OUTER JOIN
  ICLEAR_CENTRALDB.dbo.USER_ACCOUNTS AS j WITH (nolock) ON g.ADJUSTED_BY = j.ACCOUNT_ID LEFT JOIN
  ICLEAR_CENTRALDB.dbo.USER_CONTACT_INFO AS i WITH (nolock) ON j.CONTACT_INFO_ID = i.CONTACT_INFO_ID LEFT JOIN
  dbo.INVOICE_ADJUSTMENT_REASONS AS k WITH (nolock) ON g.ADJUSTMENT_REASON_ID = k.ADJUSTMENT_REASON_ID


Thanks.

Gunjan

You have 6 correlated sub-queries and that will definitely slow things down... a lot...

I recommend you convert them into a single derived table using a cross-tab. That information is in Books Online under "Cross Tab"

--Jeff Moden

Hi,
I am using SQL server 2000..
And it doesn't recognize ROW_NUMBER() function


Thanks.

Gunjan

Cross Tab has nothing to do with Row_Number()...

Also, you really should post you SQL Server 2000 questions in the SQL Serve 2000 forum so people don't waste their time trying to answer you with the wrong technology.

--Jeff Moden

I am not writing the entire code here.. My only concern is your sub queries.

You need to re-write them
This could be a way of writing. Please go and add additional columns to like to other tables

G.R.Preethiviraj Kulasingham