cursor problem

hi,
I have a stored procedure in which i have a cursor cur1.
when there is an error in the SP the cursor is not closed on exit of the SP.
i am currently looping syscursors to find the currently open cursor n then
deallocating it as shown below.. but i realize the user needs perm to access
the syscursors which i dont want to give. what other way can i deallocate
these cursors?
IF EXISTS
(SELECT * FROM MASTER..SYSCURSORS WHERE cursor_name LIKE 'UpdateCursor')
DEALLOCATE UpdateCursor
thanks
IChorCursors are evil. do not use them!
OK. Seriously 99% of the time, there will be a set based alternative.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"ichor" <ichor@.hotmail.com> wrote in message
news:O5i0d3LmFHA.2444@.tk2msftngp13.phx.gbl...
> hi,
> I have a stored procedure in which i have a cursor cur1.
> when there is an error in the SP the cursor is not closed on exit of the
> SP.
> i am currently looping syscursors to find the currently open cursor n then
> deallocating it as shown below.. but i realize the user needs perm to
> access the syscursors which i dont want to give. what other way can i
> deallocate these cursors?
>
> IF EXISTS
> (SELECT * FROM MASTER..SYSCURSORS WHERE cursor_name LIKE 'UpdateCursor')
> DEALLOCATE UpdateCursor
>
> thanks
> IChor
>|||ichor
You can try on your risk dbcc activecursors to find an active cursor/s. I'd
not recommend you using this command in the production.
"ichor" <ichor@.hotmail.com> wrote in message
news:O5i0d3LmFHA.2444@.tk2msftngp13.phx.gbl...
> hi,
> I have a stored procedure in which i have a cursor cur1.
> when there is an error in the SP the cursor is not closed on exit of the
> SP.
> i am currently looping syscursors to find the currently open cursor n then
> deallocating it as shown below.. but i realize the user needs perm to
> access the syscursors which i dont want to give. what other way can i
> deallocate these cursors?
>
> IF EXISTS
> (SELECT * FROM MASTER..SYSCURSORS WHERE cursor_name LIKE 'UpdateCursor')
> DEALLOCATE UpdateCursor
>
> thanks
> IChor
>|||Hi
Posting DDL for the procedure may help to answer this question, you may want
to read http://www.aspfaq.com/etiquette.asp?id=5006 and
http://www.aspfaq.com/show.asp?id=2081
If the error can be handled in T-SQL then you should know which cursors are
open and close them!
For error handling check out:
http://www.sommarskog.se/error-handling-II.html
and
http://www.sommarskog.se/error-handling-I.html
A set based solution is usually more efficient than a cursor based one
therefore you may want to see about changing the design to use less cursors.
John
"ichor" wrote:

> hi,
> I have a stored procedure in which i have a cursor cur1.
> when there is an error in the SP the cursor is not closed on exit of the S
P.
> i am currently looping syscursors to find the currently open cursor n then
> deallocating it as shown below.. but i realize the user needs perm to acce
ss
> the syscursors which i dont want to give. what other way can i deallocate
> these cursors?
>
> IF EXISTS
> (SELECT * FROM MASTER..SYSCURSORS WHERE cursor_name LIKE 'UpdateCursor')
> DEALLOCATE UpdateCursor
>
> thanks
> IChor
>
>|||1. Rewrite your error handling so that the SP terminates more easily.
2. Get rid of the cursors altogether.
David Portas
SQL Server MVP
--|||hi where can i learn more about set based solutions?
i would like to avoid cursors completely.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uyIeW$LmFHA.2156@.TK2MSFTNGP14.phx.gbl...
> ichor
> You can try on your risk dbcc activecursors to find an active cursor/s.
> I'd not recommend you using this command in the production.
>
> "ichor" <ichor@.hotmail.com> wrote in message
> news:O5i0d3LmFHA.2444@.tk2msftngp13.phx.gbl...
>|||Hi
I am not sure if there us any one place where you can do this!
A starter may be to look at itzik Ben-Gan's articles in SQL Server magazine:
http://www.windowsitpro.com/Authors...ID/638/638.html
And the book that was co-authored with Tom Moreau
"Advanced Transact-SQL for SQL Server 2000" ISBN: 1893115828
http://search.barnesandnoble.com/bo...3115828

John
"ichor" wrote:

> hi where can i learn more about set based solutions?
> i would like to avoid cursors completely.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uyIeW$LmFHA.2156@.TK2MSFTNGP14.phx.gbl...
>
>|||Hi
Donot use Cursor . They hurt performance badly.
But if you need to use cursor , you may use
CURSOR_STATUS
(
{ 'local' , 'cursor_name' }
| { 'global' , 'cursor_name' }
| { 'variable' , 'cursor_variable' }
)
to find the status of the cursor
and then close it and deallocate it
May this help you solve the problem
With warm regards
Jatinder Singh|||Use a local cursor instead of a global cursor. Local cursors are implicitly
deallocated when the batch that created it terminates. If they are declared
in a stored procedure, then they are deallocated when the stored procedure
exits, unless returned as an output paramenter from the procedure.
DECLARE @.X CURSOR
SET @.X = CURSOR LOCAL [other options] FOR ...
"ichor" <ichor@.hotmail.com> wrote in message
news:O5i0d3LmFHA.2444@.tk2msftngp13.phx.gbl...
> hi,
> I have a stored procedure in which i have a cursor cur1.
> when there is an error in the SP the cursor is not closed on exit of the
SP.
> i am currently looping syscursors to find the currently open cursor n then
> deallocating it as shown below.. but i realize the user needs perm to
access
> the syscursors which i dont want to give. what other way can i deallocate
> these cursors?
>
> IF EXISTS
> (SELECT * FROM MASTER..SYSCURSORS WHERE cursor_name LIKE 'UpdateCursor')
> DEALLOCATE UpdateCursor
>
> thanks
> IChor
>|||SQL PROGRAMMING STYLE, chapters 8, 9 and 10 for some help.