[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Stored Procedure works only in QA

Posted on 2006-04-17
18
Medium Priority
?
178 Views
Last Modified: 2011-09-20
Hi,

I have this SP in MS SQL which does an INSERT...SELECT. I invoke it from a Delphi program.
When there's more then 25 records to insert in table it doesn't work at all.
Curiously, when I execute it from Query Analyzer everything works fine (with any amount of records).
Do you have any clues about what to do to get it working in Delphi?
Thanks in advance.

Regards.

Paulo
0
Comment
Question by:paulomiranda
  • 5
  • 4
  • 2
  • +4
16 Comments
 
LVL 26

Expert Comment

by:EddieShipman
ID: 16470081
could it be a timing issue?
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 16470394
maybe you could show, how you do this?
did you get any exception?

meikl ;-)
0
 
LVL 13

Expert Comment

by:BlackTigerX
ID: 16471343
are you using transactions?

what do you mean it doesn't work at all?
do you get an exception?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:paulomiranda
ID: 16472847
First of all, I wanna thank you for the answers.
No, I don't get any exceptions, nor I'm using any transactions. Sometimes, it simply doesn't do the job.
The fragment I think isn't working is:

INSERT INTO ITEMMOVIMENTO (IDMovimento, idArtigo, idModelo, dscItemMov, qtdItemMov,
                                            valItemMov, descItemMov, CodAliqICMS,aliqIPI,
                                            valIPI, flgOrigemDest, LocOrigDest, flgOrigem, cstItemMov,
                                            flgPropTerc, pesoTotal, precoGrama, codNCM, idProduto,
                                            CodProduto)
      SELECT @IDMovimento,idArtigo, idModelo, dscItemMov, qtdItemMov, valItemMov,
                 descItemMov, CodAliqICMS,aliqIPI, valIPI, flgOrigDest, LocOrigDest, flgOrigem,
                 cstItemMov, flgPropTerc, pesoTotal, precoGrama, codNCM, idProduto, CodProduto
        FROM ITEMMOVTEMP WHERE IDMov = @IDMov

When table ITEMMOVTEMP has less than 20, 25 records everything works fine.

Once again, I'd like to thank you.

Regards,

Paulo
0
 

Author Comment

by:paulomiranda
ID: 16473257
Yes, Eddie.
I think it could be a timing issue.
It seems to be.
But I dont know how to deal with it.
0
 
LVL 13

Expert Comment

by:BlackTigerX
ID: 16474392
can you post the full stored procedure?

and if you can, the code that you use to call this procedure (are you calling it from more than one place?)
0
 
LVL 11

Expert Comment

by:pcsentinel
ID: 16476006
Agree, could be a timing issue, try 2 things

first make sure you have an index on IDMov in the ITEMMOVTEMP table

In Query Analyzer go to tools>options bring up the connections tab and try putting in a query time out value and then reconnecting and rerunning the query, what happens?
0
 

Author Comment

by:paulomiranda
ID: 16477303
The full SP looks like this:

CREATE PROCEDURE DBS01.SPBAIXA
@IDMov       Int,
@IdMovimento Int,
@Msg  VarChar(50) OutPut
As
  DECLARE
    @IDProduto  Int,
    @QtdSaidas Decimal(6,2),
    @QtdEntradas Decimal(6,2),
    @Saldo Decimal(6,2),
    @Qtd Decimal(6,2),
    @Mensagem varchar(60),
    @TipoProduto Char(1),
    @IDArtigo Int,
    @Destino Char(1),
    @IDOperacao Char(2),
    @CodProduto Int,
    @QtdSaidasPAux Decimal(6,2),
    @IDDestino Int,
    @IDMovimento Int,
    @Flg_NF Char(1),
    @Flg_Status Char(1),
    @NumDoc Int,
    @FlgFiscal Char(1),
    @TipoDoc Char(3),
    @IDLoja Int,
    @NumSeq Int,
    @dtMov DateTime,
    @ValTotal Decimal(10,2),
    @ValDescto Decimal(10,2),
    @PercDescto Decimal(5,3),
    @ValorItem Decimal(10,2),
    @ValDescItem Decimal(10,2),
    @SaldoDescto Decimal(10,2),
    @CodProd Int

    SELECT @IDOperacao = IDOperacao,
                @IDDestino = IDDestino,
                @TipoDoc = CodTipoDoc,
                @IDLoja = idLoja,
                @dtMov = dtMov,
                @NumDoc = numDocto,
                @ValTotal = ValTotal,
                @ValDescto = ValDescto  
        FROM MovTemp Where IDMov = @IDMov

    SELECT @PercDescto = 0
    SELECT @SaldoDescto = 0

    IF @ValDescto > 0
    BEGIN
        SELECT @PercDescto = @ValDescto / @ValTotal
        SELECT @SaldoDescto = @ValDescto
    END

    SELECT @Destino = Destino
        FROM Operacao
      WHERE IdOperacao = @IDoperacao


    INSERT INTO ITEMMOVIMENTO (IDMovimento, idArtigo, idModelo, dscItemMov,
                      qtdItemMov, valItemMov, descItemMov, CodAliqICMS,aliqIPI,
                      valIPI, flgOrigemDest, LocOrigDest, flgOrigem, cstItemMov, flgPropTerc,
                      pesoTotal, precoGrama, codNCM, idProduto, CodProduto)
      SELECT @IDMovimento,idArtigo, idModelo, dscItemMov, qtdItemMov, valItemMov,
                  descItemMov, CodAliqICMS,aliqIPI, valIPI, flgOrigDest, LocOrigDest, flgOrigem,
                  cstItemMov, flgPropTerc, pesoTotal, precoGrama, codNCM, idProduto, CodProduto
        FROM ITEMMOVTEMP
        WHERE IDMov = @IDMov

   
    DECLARE CURITEMMOV1 CURSOR
      FOR SELECT IDProduto, CodProduto, QtdItemMov, ValItemMov, IDArtigo
              FROM ItemMovTemp
            Where IDMov = @IDMov

    OPEN CURITEMMOV1

    FETCH NEXT FROM CURITEMMOV1
        INTO @IDProduto,  @CodProduto, @Qtd, @ValorItem, @IDArtigo

    WHILE (@@FETCH_STATUS = 0)
    BEGIN

       IF @ValDescto > 0
       BEGIN
           SELECT @ValDescItem = @ValorItem * @PercDescto
           IF @ValDescItem - @SaldoDescto >= 0
               SELECT @ValDescItem = @SaldoDescto
           ELSE
               IF @SaldoDescto - @ValDescItem < 5
               BEGIN
                  SELECT @ValDescItem = @SaldoDescto
               END
           SELECT @SaldoDescto = @SaldoDescto - @ValDescItem
       END

       IF NOT @CodProduto  IS NULL
          BEGIN
               UPDATE ItemMovimento SET descItemMov = @ValDescItem
                   WHERE IdMovimento = @IDMovimento AND
                                 CodProduto = @CodProduto
               SELECT @Saldo = qtdSaidas,
                           @QtdEntradas = QtdEntradas,
                           @Flg_NF = Flg_NF,
                           @Flg_Status = Flg_Status,
                           @IDProduto = idProduto
                   FROM ProdutoAux
                   WHERE CodProduto = @CodProduto
               IF @Saldo is NULL   SELECT @Saldo = 0
               IF @Flg_Status <> 'S'
               BEGIN
                 SELECT @Saldo = @Saldo + @Qtd
               END
          END
      ELSE
         BEGIN
              SELECT @Saldo = qtdSaidas,
                         @QtdEntradas = QtdEntradas
                  FROM Produto
                  WHERE IDProduto = @IDProduto
              IF @Saldo is NULL  SELECT @Saldo = 0
              SELECT @Saldo = @Saldo + @Qtd
         END
           
      SELECT @TipoProduto = FlgTipo From TipoProduto Where IDArtigo = @IDArtigo
                       
      IF @TipoProduto = 'J'
         BEGIN      
           SELECT @NumSeq = NULL
           SELECT @NumSeq = NumSequencial FROM ProdutoAux
                   WHERE codProduto = @CodProduto
           IF NOT @NumSeq IS NULL
           BEGIN
                SELECT @CodProd = CodProduto FROM Estoque
                        WHERE IdLoja = @IdLoja AND NumSequencial = @NumSeq
                IF @CodProd = @CodProduto
                    UPDATE Estoque
                       SET dtSaida = @dtMov,
                              saldo = 0,
                              IDMovSaida = @IDMovimento
                     WHERE idLoja = @IDLoja AND
                              NumSequencial = @NumSeq
           END
           IF @Destino = 'C'  
           BEGIN                
             UPDATE  ProdutoAux
                 SET QtdSaidas  = @Saldo,
                        Flg_Status = 'V',
                        numnfv     = @NumDoc
               WHERE CodProduto = @CodProduto
           END    
           ELSE  
           BEGIN                
             UPDATE ProdutoAux
                 SET QtdSaidas  = 1,
                       Flg_Status = 'O'                              
               WHERE CodProduto = @CodProduto
           END    
           SELECT @QtdSaidas = QtdSaidas FROM Produto Where IDProduto = @IDProduto
           IF @QtdSaidas is Null
               SELECT @QtdSaidas = 0
           UPDATE PRODUTO
               SET QtdSaidas = @QtdSaidas + 1
             WHERE IDProduto = @IDProduto
         END
       ELSE
         BEGIN
           IF @Destino = 'C'  
           BEGIN
             UPDATE  Produto
                   SET QtdSaidas = @Saldo
               WHERE IDProduto = @IDProduto                          
           END    
           ELSE
           BEGIN
             UPDATE  Produto
                   SET QtdSaidas = @Saldo
               WHERE IDProduto = @IDProduto          
           END    
           IF @Saldo = @QtdEntradas
           BEGIN
             UPDATE  Produto
                 SET FlgStatus     = 'O'                              
               WHERE IDProduto = @IDProduto
           END

         END

       FETCH NEXT FROM CURITEMMOV1
        INTO @IDProduto,  @CodProduto, @Qtd, @ValorItem, @IDArtigo
    END

       
    CLOSE CURITEMMOV1
    DEALLOCATE CURITEMMOV1
GO



I call it from Delphi using:

  Try
    With SPItemMovimento do
    begin
      ParamByName('@IDMov').asInteger := IDMov;
      If EdtNumDoc.Text <> '' then
        ParamByName('@NumDoc').asInteger := StrToInt(EdtNumDoc.Text)
      else
        ParamByName('@NumDoc').Clear;
      ParamByName('@codTipoDoc').asString := CBTipoDoc.keyvalue;
      ParamByname('@IDOperacao').asString := CbOper.KeyValue;
      ParamByName('@IDMovimento').AsInteger := IDMovimento;
      Prepare;
      if not Prepared then
        begin
          ShowMessage('Erro preparando SP. Avise suporte.');
          Exit;
        end;
      ExecProc;
    end;
  except
    on E: Exception do
      ShowMessage('Erro gravando itens do movimento. Avise suporte!');
  end;


SPITEMMOV will call SPBaixa depending on @IDOPERACAO. This ever works.

Thank you all for the interest. I'm very glad.
0
 

Author Comment

by:paulomiranda
ID: 16477921
Pcsentinel,

No, I dont have an index on IDMOV in the table. Do I have to?

I tried to reproduce the problem, but I couldn't. Now, its accepting more then 30 records.
Anyways, I think nothing will happen changing query time out in QA, 'cause it allways worked fine when executed througth it.
I'll try adding more records.

Thanks
0
 
LVL 11

Expert Comment

by:pcsentinel
ID: 16478446
Yes add the index it will make the procedure return the records much quicker
0
 
LVL 11

Expert Comment

by:pcsentinel
ID: 16957690
Why refund the points?
0
 
LVL 14

Expert Comment

by:cwwkie
ID: 16961940
> Why refund the points?

I think the Asker has responded to all of the Experts' suggestions. Or did I miss something?
0
 
LVL 11

Expert Comment

by:pcsentinel
ID: 16966939
Because I think the answers did answer the question, or at least point to an answer and the asker has just abandoned it
0
 
LVL 14

Expert Comment

by:cwwkie
ID: 16971062
> Because I think the answers did answer the question,

Ok, I change my recommendation, and asume the following answer to this question:
        There is a timing issue which occurs because there is no index on IDMov in the ITEMMOVTEMP table
0
 

Author Comment

by:paulomiranda
ID: 16973467
I am sorry to say that this miserable issue is not solved yet.
My customer found a way to live with it, and, from time to time, checks if I have an answer to him. This is boring. He laughs at me every time we meet.
Anyways, I'd like to thank you all for your interest on helping me. I think this has more to do with black magic or something.
I'm thinking about asking Chuck Norris for help. Maybe he's the only one who can finally make it work.
Thanx again.
Regards.,

Paulo

0
 

Accepted Solution

by:
GranMod earned 0 total points
ID: 16978008
PAQed with points refunded (125)

GranMod
Community Support Moderator
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses
Course of the Month18 days, 23 hours left to enroll

834 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