Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

rowcount is not working in SQL Server 2000

Posted on 2004-10-20
5
Medium Priority
?
884 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
[X]
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
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 70

Accepted Solution

by:
Scott Pletcher earned 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

597 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