Row limit in fetch - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Row limit in fetch

Author

Topic

SergioCosta
Starting Member

5 Posts

Posted - 2004-03-02 : 05:50:46

Hi,

i'm having a problem:
I have a SP with a cursor that reads data from a table, and for each record returned updated information in the same table.
This SP works fine when a call it directly from query analyser, but when i execute it from VB6 using ADO connection it only works for the first 50 rows of the table.
No error is returned, and i have no command timeout.

Can anyone help me?

Thanks

SC

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-03-02 : 05:55:28

Chances are you don't have SET NOCOUNT ON inside your proc. But I think your bigger problem is that you are using a cursor at all. I bet you can do it with something set based and dramatically increase your performance.

Damian

SergioCosta
Starting Member

5 Posts

Posted - 2004-03-02 : 06:04:59

I Have SET NOCOUNT OFF in the SP.
I also use a temporary table to store the key of the records to be updated in the original table.

I don't think that the problem is in the SP but in the ADO.

I'm not using transactions, and the @@Fetch_Status is still 0 when the loop ends.

SergioCosta
Starting Member

5 Posts

Posted - 2004-03-02 : 06:13:04

I meant, i have SET NOCOUNT ON.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-03-02 : 06:23:16

Given that millions of developers use ADO every day, and I've never seen your particular problem, I would guess that the problem lies in your proc.

Damian

SergioCosta
Starting Member

5 Posts

Posted - 2004-03-02 : 06:46:27

Merkin,

Here comes my SP.
Please see if you can find something wrong.

Thank's

CREATE PROCEDURE sp_Artigos
AS

declare @CodArtigo as nvarchar(25)
Declare @Sexo as nvarchar(12)
declare @Produto as nvarchar(12)
declare @SubProduto as nvarchar(12)
declare @TipoProduto as nvarchar(12)

Declare @SexoDescr as nvarchar(25)
Declare @ProdutoDescr as nvarchar(25)
Declare @TipoProdutoDescr as nvarchar(25)
Declare @SubProdutoDescr as nvarchar(100)

select CodArtigo, Sexo, Produto, SubProduto, TipoProduto into #Temp from Artigos

SET NOCOUNT ON

DECLARE curs CURSOR FOR
select CodArtigo, Sexo, Produto, SubProduto, TipoProduto from #Temp
open curs
FETCH NEXT FROM curs into @CodArtigo, @Sexo, @Produto, @SubProduto, @TipoProduto
WHILE (@@FETCH_STATUS = 0)
Begin
select @SexoDescr =
case @Sexo
when '1' then 'H'
when '2' then 'M'
else 'U'
end

select @ProdutoDescr =
case @Produto
when '0' then 'Diversos'
when '1' then 'Calças'
else 'Sem Descrição Produto'
end

select @TipoProdutoDescr =
case @TipoProduto
when '1' then 'Matérias Primas'
when '2' then 'Acessórios'
else 'Sem Tipo Produto'
end

set @SubProdutoDescr=''

update Artigos set SexoDescr=@SexoDescr, ProdutoDescr=@ProdutoDescr, SubProdutoDescr=left(@SubProdutoDescr,25) , TipoProdutoDescr=@TipoProdutoDescr where CodArtigo=@CodArtigo

FETCH NEXT FROM curs into @CodArtigo, @Sexo, @Produto, @SubProduto, @TipoProduto

End

CLOSE curs
DEALLOCATE curs
GO

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-03-02 : 06:52:25

I stand by my first statement

UPDATE Artigos
SET
SexoDesc = CASE Sexo WHEN '1' THEN 'H' WHEN '2' THEN 'M' ELSE 'U' END,
ProdutoDescr = CASE Produto WHEN '0' THEN 'Diversos' WHEN '1' THEN 'Calças' ELSE 'Sem Descrição Produto' END,
TipoProdutoDescr = CASE TipProduto WHEN '1' then 'Matérias Primas' when '2' then 'Acessórios' else 'Sem Tipo Produto' END
SubProdutoDescr=left(SubProdutoDescr,25)

Damian

SergioCosta
Starting Member

5 Posts

Posted - 2004-03-02 : 07:14:05

Using your statement the SP works fine. And it's 10 times faster.
Thanks

But i still wonder why the one with the cursor didn't work, in VB

Once Again,
Thanks

SC

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-02 : 12:05:26

Don't wonder too long....and start taking Damains advice to heart....

There's very little that requires a cursor...

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources