how can we know cursor is already open or not

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how can we know cursor is already open or not

Author

Topic

pmotewar
Yak Posting Veteran

62 Posts

Posted - 2009-06-22 : 07:49:57

hi,

i want to know cursor is already open or not ?

( like oracle having keyword %isopen)

any help appriciated.

Pankaj

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-22 : 07:52:15

Try @@CURSOR_ROWS

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-22 : 08:41:37

or

select is_open from sys.dm_exec_cursors(@@spid)

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-22 : 08:45:01

Please take note that the status returned is for LAST cursor only in that SPID or connection.

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-22 : 09:03:44

quote:
Originally posted by Peso

Please take note that the status returned is for LAST cursor only in that SPID or connection.

E 12°55'05.63"
N 56°04'39.26"

Yes it is. Better usage is

select name,is_open from sys.dm_exec_cursors(0)

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-22 : 09:28:40

Great post and using the function need VIEW SERVER STATE permission on the server.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-22 : 09:37:25

[code]select ec.name,
ec.is_open,
q.text
from sys.dm_exec_cursors(0) AS ec
OUTER APPLY sys.dm_exec_sql_text(ec.sql_handle) AS q[/code]

E 12°55'05.63"
N 56°04'39.26"