Solved

rowcount is not working in SQL Server 2000

Posted on 2004-10-20
5
864 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:
ScottPletcher 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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
Viewers will learn how the fundamental information of how to create a table.

910 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now