deleting old cursors

Hello. How can I delete an old cursor which appears when I execute the statement

select * from sys.dm_exec_cursors(0) ?

Thanks.

KILL session_id

...or alternatively if you can issue commands against the connection reported in the session_id column (for instance if the problem is with a SSMS connection that you have open) then you can issue:

CLOSE <cursor name>

DEALLOCATE <cursor name>

Chris

|||Thanks a lot.|||

Just to add that the first example I gave was really an 'emergency' measure. It would be advisable to track and fix the cause of the problem.

Chris

|||That was exactly what I was looking for, an emergency measure. I couldn't use close and deallocate. I forgot to mention that I am using SQL SERVER 2005. In a stored procedure I commented out a block together with close and deallocate for the respective cursor. The weird thing is that the procedure has only the declaration/definition of the cursor, otherwise there is no reference to it. I realized that it still exists when I received the error "cursor ... already exists". Thanks again for your answer.

deleting old cursors

Hello. How can I delete an old cursor which appears when I execute the statement

select * from sys.dm_exec_cursors(0) ?

Thanks.

KILL session_id

...or alternatively if you can issue commands against the connection reported in the session_id column (for instance if the problem is with a SSMS connection that you have open) then you can issue:

CLOSE <cursor name>

DEALLOCATE <cursor name>

Chris

|||Thanks a lot.|||

Just to add that the first example I gave was really an 'emergency' measure. It would be advisable to track and fix the cause of the problem.

Chris

|||That was exactly what I was looking for, an emergency measure. I couldn't use close and deallocate. I forgot to mention that I am using SQL SERVER 2005. In a stored procedure I commented out a block together with close and deallocate for the respective cursor. The weird thing is that the procedure has only the declaration/definition of the cursor, otherwise there is no reference to it. I realized that it still exists when I received the error "cursor ... already exists". Thanks again for your answer.

Delete syntax

Can one do a 'Delete from Select' SQL Statement?
SELECT distinct Vh_make_id FROM vht_lu_vehicle(nolock) WHERE vh_make_id IN
(select distinct Vh_make_id from marc1) AND vh_make_id != '' -- 154 Rows
I want to delete all rows from the vehicle table that have a matching
Vh_make_id in the marc1 table but also have a Vh_make_id not equal to blank?
I am as to what exactly will occur with the following delete
statement:
DELETE FROM vht_lu_vehicle(nolock) WHERE vh_make_id IN (SELECT distinct
Vh_make_id FROM marc1) AND vh_make_id != ''> DELETE FROM vht_lu_vehicle(nolock) WHERE vh_make_id IN (SELECT distinct
> Vh_make_id FROM marc1) AND vh_make_id !=
You will delete every record where the vh_make_id in vht_lu_vehicle is empty
(not null, which is probably what you want) that has a vh_make_id in the
marc1 table.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
****************************************
*******
Think Outside the Box!
****************************************
*******
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:2ED21B32-3DF4-42B4-B32C-5568BBDBDDC4@.microsoft.com...
> Can one do a 'Delete from Select' SQL Statement?
> SELECT distinct Vh_make_id FROM vht_lu_vehicle(nolock) WHERE vh_make_id IN
> (select distinct Vh_make_id from marc1) AND vh_make_id != '' -- 154 Rows
> I want to delete all rows from the vehicle table that have a matching
> Vh_make_id in the marc1 table but also have a Vh_make_id not equal to
> blank?
> I am as to what exactly will occur with the following delete
> statement:
> DELETE FROM vht_lu_vehicle(nolock) WHERE vh_make_id IN (SELECT distinct
> Vh_make_id FROM marc1) AND vh_make_id != ''
>