paulomiranda
asked on
Stored Procedure works only in QA
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
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
could it be a timing issue?
maybe you could show, how you do this?
did you get any exception?
meikl ;-)
did you get any exception?
meikl ;-)
are you using transactions?
what do you mean it doesn't work at all?
do you get an exception?
what do you mean it doesn't work at all?
do you get an exception?
ASKER
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
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
ASKER
Yes, Eddie.
I think it could be a timing issue.
It seems to be.
But I dont know how to deal with it.
I think it could be a timing issue.
It seems to be.
But I dont know how to deal with it.
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?)
and if you can, the code that you use to call this procedure (are you calling it from more than one place?)
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?
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?
ASKER
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').asIn teger := IDMov;
If EdtNumDoc.Text <> '' then
ParamByName('@NumDoc').asI nteger := StrToInt(EdtNumDoc.Text)
else
ParamByName('@NumDoc').Cle ar;
ParamByName('@codTipoDoc') .asString := CBTipoDoc.keyvalue;
ParamByname('@IDOperacao') .asString := CbOper.KeyValue;
ParamByName('@IDMovimento' ).AsIntege r := 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.
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').asIn
If EdtNumDoc.Text <> '' then
ParamByName('@NumDoc').asI
else
ParamByName('@NumDoc').Cle
ParamByName('@codTipoDoc')
ParamByname('@IDOperacao')
ParamByName('@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.
ASKER
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
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
Yes add the index it will make the procedure return the records much quicker
Why refund the points?
> Why refund the points?
I think the Asker has responded to all of the Experts' suggestions. Or did I miss something?
I think the Asker has responded to all of the Experts' suggestions. Or did I miss something?
Because I think the answers did answer the question, or at least point to an answer and the asker has just abandoned it
> 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
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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.