We help IT Professionals succeed at work.

How to increase maximum open cursors in mssql server 2005

How can I increase the maximum number of open cursors in
microsoft sql server 2005
Comment
Watch Question

Is there limit on number of open cursors? Other than not having memory usage, I don't think there is any restriction on number of open curosrs.
Cursors are the slowest data objects on SQL Server and you should minimize their usage as much as possible.

I've been trying to open 5000 cursors (without reading data) in SQL Express 2005 and it did not fail. How many cursors do you need?
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2019

Commented:
>>  How can I increase the maximum number of open cursors in microsoft sql server 2005

Completely depends upon your hardware resources available ( as mentioned above).
Besides SQL Server is not performance wise good in handling Cursors and hence recommended to go for alternative logics if available..
Don't use cursors -- they're far too slow... it's more likely that you're just hitting a wall (very slowly) and overloading the system. Maybe show the code and we can show you another way -- Temp tables are a huge improvement - might be a good place to start.

Author

Commented:
this is the query :
select apm1.project_num

from (SELECT ADP.PROJECT_NUM as PROJECT_NUM,

t.WO_NUM as CHILD_NUM,

(case when adp.project_num =t.WO_NUM then '0. DEVELOPMENT REQUEST'

        when t.WOTYPE2 = 'ERROR LOG' then 'ERROR LOG'

        else t.WOTYPE3 end) as MILESTONE,

t.OPENDATE as OPEN_DATE,

t.DUEDATE as DUE_DATE,

t.CLSDDATE as COMPLETED_DATE

FROM

(SELECT t.WO_NUM as "PROJECT_NUM",

t.type as "TYPE",

t.WOTYPE2 as "SUB_TYPE",

t.OPENDATE AS "OPEN_DATE",

t.DUEDATE as "DUE_DATE",

t.CLSDDATE as "COMPLETED_DATE"

from dbo.tasks@trackit t

where t.TYPE = 'DEVELOPMENT'

and t.WOTYPE2 in ('DEVELOPMENT REQUEST','RELEASE')) ADP,

dbo.tasks@trackit t

where

ADP.PROJECT_NUM = t.PARENTWOID

and ((case when adp.project_num =t.WO_NUM then '0. DEVELOPMENT REQUEST' else t.WOTYPE3 end) In ('0. DEVELOPMENT REQUEST','1. REQUEST','2. KICK-OFF','3. SLR','4. DEV OFFER','5. PROJECT DESIGN','6. DEV SIGN-OFF','7. UAT','8. GO-LIVE','9. CLOSING MEETING')

or t.WOTYPE2 = 'ERROR LOG')

and t."WorkOrderStatusId" <> 101) APM1,

(SELECT ADP.PROJECT_NUM as PROJECT_NUM,

t.WO_NUM as CHILD_NUM,

(case when adp.project_num =t.WO_NUM then '0. DEVELOPMENT REQUEST'

        when t.WOTYPE2 = 'ERROR LOG' then 'ERROR LOG'

        else t.WOTYPE3 end) as MILESTONE,

t.OPENDATE as OPEN_DATE,

t.DUEDATE as DUE_DATE,

t.CLSDDATE as COMPLETED_DATE

FROM

(SELECT t.WO_NUM as "PROJECT_NUM",

t.type as "TYPE",

t.WOTYPE2 as "SUB_TYPE",

t.OPENDATE AS "OPEN_DATE",

t.DUEDATE as "DUE_DATE",

t.CLSDDATE as "COMPLETED_DATE"

from dbo.tasks@trackit t

where t.TYPE = 'DEVELOPMENT'

and t.WOTYPE2 in ('DEVELOPMENT REQUEST','RELEASE')) ADP,

dbo.tasks@trackit t

where

ADP.PROJECT_NUM = t.PARENTWOID

and ((case when adp.project_num =t.WO_NUM then '0. DEVELOPMENT REQUEST' else t.WOTYPE3 end) In ('0. DEVELOPMENT REQUEST','1. REQUEST','2. KICK-OFF','3. SLR','4. DEV OFFER','5. PROJECT DESIGN','6. DEV SIGN-OFF','7. UAT','8. GO-LIVE','9. CLOSING MEETING')

or t.WOTYPE2 = 'ERROR LOG')

and t."WorkOrderStatusId" <> 101) APM2

where apm1.completed_date is not null

and apm1.MILESTONE = '2. KICK-OFF'

and APM1.PROJECT_NUM = APM2.PROJECT_NUM




-- it actually works when removing the ‘and apm1.MILESTONE = '2. KICK-OFF'’ in the where clause.

 






SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2019
Commented:
Few question:

1. Above query doesn't involves cursors at all as described in your question
2. -- it actually works when removing the ‘and apm1.MILESTONE = '2. KICK-OFF'’ in the where clause.

then create appropriate index on that MILESTONE column so that it would work faster..
Posting Execution plan would help us guide you to create appropriate indexes..
select apm1.project_num , apm1.milestone, apm2.milestone
from (
            SELECT ADP.PROJECT_NUM as PROJECT_NUM, t.WO_NUM as CHILD_NUM,
            (case when adp.project_num =t.WO_NUM then '0. DEVELOPMENT REQUEST'
                    when t.WOTYPE2 = 'ERROR LOG' then 'ERROR LOG'
                    else t.WOTYPE3 end) as MILESTONE, t.OPENDATE as OPEN_DATE, t.DUEDATE as DUE_DATE, t.CLSDDATE as COMPLETED_DATE
            FROM
            (
                  SELECT t.WO_NUM as "PROJECT_NUM", t.type as "TYPE", t.WOTYPE2 as "SUB_TYPE",
                  t.OPENDATE AS "OPEN_DATE", t.DUEDATE as "DUE_DATE", t.CLSDDATE as "COMPLETED_DATE"
                  from dbo.tasks@trackit t
                  where t.TYPE = 'DEVELOPMENT' and t.WOTYPE2 in ('DEVELOPMENT REQUEST','RELEASE')
            ) ADP,            
            dbo.tasks@trackit t
            where ADP.PROJECT_NUM = t.PARENTWOID
            and (
                  (case when adp.project_num =t.WO_NUM then '0. DEVELOPMENT REQUEST' else t.WOTYPE3 end)
                  In ('0. DEVELOPMENT REQUEST','1. REQUEST','2. KICK-OFF','3. SLR','4. DEV OFFER','5. PROJECT DESIGN','6. DEV SIGN-OFF','7. UAT','8. GO-LIVE','9. CLOSING MEETING')
            or t.WOTYPE2 = 'ERROR LOG')  
            and t."WorkOrderStatusId" <> 101
      ) APM1,
    (
            SELECT ADP.PROJECT_NUM as PROJECT_NUM,
            t.WO_NUM as CHILD_NUM,
            (case when adp.project_num =t.WO_NUM then '0. DEVELOPMENT REQUEST'
                        when t.WOTYPE2 = 'ERROR LOG' then 'ERROR LOG'
                        else t.WOTYPE3 end) as MILESTONE,
            t.OPENDATE as OPEN_DATE, t.DUEDATE as DUE_DATE, t.CLSDDATE as COMPLETED_DATE
            FROM
                  (
                  SELECT t.WO_NUM as "PROJECT_NUM",
                  t.type as "TYPE",
                  t.WOTYPE2 as "SUB_TYPE",
                  t.OPENDATE AS "OPEN_DATE",
                  t.DUEDATE as "DUE_DATE",
                  t.CLSDDATE as "COMPLETED_DATE"
                  from dbo.tasks@trackit t
                  where t.TYPE = 'DEVELOPMENT'
                  and t.WOTYPE2 in ('DEVELOPMENT REQUEST','RELEASE')
                  ) ADP,
            dbo.tasks@trackit t
            where
            ADP.PROJECT_NUM = t.PARENTWOID
            and (
                  (case when adp.project_num =t.WO_NUM then '0. DEVELOPMENT REQUEST' else t.WOTYPE3 end)
                        In ('0. DEVELOPMENT REQUEST','1. REQUEST','2. KICK-OFF','3. SLR','4. DEV OFFER','5. PROJECT DESIGN','6. DEV SIGN-OFF','7. UAT','8. GO-LIVE','9. CLOSING MEETING')
            or t.WOTYPE2 = 'ERROR LOG')      and t."WorkOrderStatusId" <> 101
      ) APM2
      where apm1.completed_date is not null
      and apm1.MILESTONE = '2. KICK-OFF' and APM1.PROJECT_NUM = APM2.PROJECT_NUM


--------- Try this one -- the apm1.MILESTONE cannot be "Seen" from within the where clause. if you return it in the original clause, you can use the where on it...