rowcount is not working in SQL Server 2000

Hi Everyone!

I am newbie, trying to learning SQL Server,  I am trying to run a script but I am getting error message for rowcount here is the code... any body can help



/*

****Delete the duplicate records from category table ****

*/


-- Declare veriables

declare @iErrorCheck int
declare @iCount int
declare @vcCount varchar(5)
declare @vcCategory varchar(100)
declare @nvcCommand nvarchar(4000)


set ROWCOUNT on <<<<<<<< Problem
set nocount on



declare Dups cursor for
      
Select Count(*) as DuplicateData, CategoryName
from tsqljions.dbo.category
group by CategoryName
Having count(*) > 1


open Dups

fetch next from dups into @iCount, @vcCategory

while (@@fetch_status = 0)

begin


select @iCount = @iCount-1
select @vcCount = convert(varchar(5), @iCount)


select   @nvcCommand = n' set rowcount  ' + @vcCount +                 <<<<<< Problem
      ' Delete tsqljions.dbo.category '  +
               ' Where Category = ' + char (34) + @vcCategory + char(34)
      

      -- print the sql command to see if its right.

print @nvcCommand


      -- execute the statement

exec sp_executesql @nvcCommand

select  @iErrorCheck = @@Error

if @ierrorcheck <> 0

begin

return

end

fetch next from dups into @iCount, @vcCategory

end

close dups

deallocate dups

return

 
LVL 1
ExpertProAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
You should [always] use single quotes instead of double quotes for literal values in SQL Server; so:


select   @nvcCommand = n' set rowcount  ' + @vcCount +    
       ' Delete tsqljions.dbo.category '  +
              ' Where Category = ' + char (39) + @vcCategory + char(39)  
--<<-- chg'd char(34) [dbl quote] to char(39) [single quote]
0
 
BlackTigerXCommented:
you are supposed to use it like:

set rowcount [Number]

that tells SQL to stop processing after the specified number of rows

so if you do
set rowcount 1

it'll only do 1 row for your INSERT, UPDATE, DELETE statements
0
 
mcmonapCommented:
Hi ExpertPro,

In addition to BlackTigerX, the below will switch the rowcount limit off again:

SET ROWCOUNT 0 --Zero
0
 
ExpertProAuthor Commented:
Thank you BlackTigerX and mcmonap. I try to use [] but still i am getting error where set rowcount ' + @vcCount +

How to slove this...
0
 
mcmonapCommented:
Hi ExpertPro,

Sorry missed your second problem line, this doesn't look like a valid SQL statement to me - you could try something like:

SELECT @nvcCommand = N'set rowcount ' + @vcCount + '; Delete tsqljions.dbo.category Where Category = ''' + @vcCategory + ''''

This works like below:
DECLARE @rc VARCHAR(10)
SET @rc = 10

DECLARE @v VARCHAR(255)
SET @v = 'SET ROWCOUNT ' + @rc + ';SELECT * FROM pubs..authors'
EXEC(@v)

SET ROWCOUNT 0
SELECT * FROM pubs..authors
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.