Solved

rowcount is not working in SQL Server 2000

Posted on 2004-10-20
5
877 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 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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

691 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