Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

rowcount is not working in SQL Server 2000

Posted on 2004-10-20
5
871 Views
Last Modified: 2008-03-10
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

 
0
Comment
Question by:ExpertPro
5 Comments
 
LVL 13

Expert Comment

by:BlackTigerX
ID: 12361620
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
 
LVL 15

Expert Comment

by:mcmonap
ID: 12361683
Hi ExpertPro,

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

SET ROWCOUNT 0 --Zero
0
 
LVL 1

Author Comment

by:ExpertPro
ID: 12361972
Thank you BlackTigerX and mcmonap. I try to use [] but still i am getting error where set rowcount ' + @vcCount +

How to slove this...
0
 
LVL 15

Expert Comment

by:mcmonap
ID: 12362205
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
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 12362388
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question