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.

Deleting duplicate rows in a table

In Sql Server table , there are some duplicate rows
i want to delete the duplicate rows without using Cursors and Temp TableCREATE PROCEDURE RemoveDuplicate AS
Begin
SET NOCOUNT ON
--
DECLARE @.iErrorVar int,
@.CertificateID INT,
@.ClientID INT,
@.iCount int,
@.chCount char(3),
@.nvchCommand nvarchar(4000)
-- set initial environment
SET ROWCOUNT 0
-- Build cursor to find duplicated information

--Change this from a cursor to a table variable. This is an early version
-- of the code before I tarted it up a bit.

DECLARE DelDupe CURSOR FOR
SELECT COUNT(*) AS Amount,
fields that make the row distinct
FROM Table
GROUP BY Fields
HAVING COUNT(*) > 1

OPEN DelDupe
FETCH NEXT FROM DelDupe
INTO @.FieldVars,

WHILE (@.@.fetch_status = 0)
BEGIN
-- Calculate number of rows to delete for each grouping by subtracting
-- 1 from the total count for a given group.

SELECT @.iCount = @.iCount - 1
SELECT @.chCount = CONVERT(char(3),@.iCount)
-- now build the rowcount and delete statements.
SELECT @.nvchCommand = N'SET ROWCOUNT ' + @.chCount +
'DELETE FROM Table ' +
' WHERE All Fields match those in the field variables. = ' + convert(varchar,@.Field1) +
' AND Etc = ' + convert(varchar,@.Field2)

--
-- print @.nvchCommand --Use this to check the syntax
EXEC sp_executesql @.nvchCommand --Comment out until happy.
--
FETCH NEXT FROM DelDupe
INTO @.iCount,
@.CertificateID,
@.ClientID
END
--
CLOSE DelDupe
DEALLOCATE DelDupe
End

This is the easiest way I've found to do removal of dups. Hope it helps.

Cheers
C|||

Quote:

Originally Posted by thithu

In Sql Server table , there are some duplicate rows
i want to delete the duplicate rows without using Cursors and Temp Table

try the following and adapt it to your needs:

create table #t1 (id int identity(1,1), x char(5))
insert into #t1 (x) values ('a')
insert into #t1 (x) values ('a')
insert into #t1 (x) values ('a')
insert into #t1 (x) values ('b')
insert into #t1 (x) values ('b')
insert into #t1 (x) values ('b')
insert into #t1 (x) values ('c')
insert into #t1 (x) values ('d')
insert into #t1 (x) values ('d')

select * from #t1

delete from #t1
where x in (select x from #t1 group by x having count(*) > 1)
and id not in (select min(id) from #t1 group by x having count(*) > 1)

select * from #t1