Cursor question

[wbodger]

I have a job that runs very frequently that uses a cursor. Is there an easy way to see how many records are in that cursor? I would like to have it mail me the number of rows in the cursor every time it runs for a little while so I can get an idea how much work it is really doing.

Thanks,
Willie

 

[mrdenny]

You will need to issue the select statement again with a count instead of opening it in the cursor to get the number of records in the cursor.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Crowley16]

or you could just include a counter variable in the cursor and after the loop, just return that counter...

--------------------
Procrastinate Now!

 

[nomadicalloy]

I like "Crowley16"'s answer better.



This is Signature

http://www.xpherion.com/

 

[Crowley16]

just remembered, you could also use the @@rowcount property straight after the select statement in the opening of the cursor...

--------------------
Procrastinate Now!

 

[wbodger]

OK, I was thinking about the counter, just wasn't sure if Cursors had some special property. Thanks!

WB

 

[mrdenny]

I tested using @@ROWCOUNT just after opening the cursor and got 0 records in @@ROWCOUNT when there were 5 records in the cursor.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[nomadicalloy]

if @@ROWCOUNT doesn't work just use a variable to count records from cursor loop.


This is Signature

http://www.xpherion.com/