Intermittent Error! Help!

I have a sequence of 3 operations, and its repeated many times by an
aplication (timer loop)
1) Open a transaction
2) Execute 1 (ONE) insert in a "simple" table. This table has only an
auto-increment identificator and some fields numbers and chars.
3) Comitt the transaction
This proccess is executed normaly during many days, but many times this
command returns "0 rows affected" to the application, but with no exception,
just returns "0 rows affected".
The version is 2000 with all SP.
Thanks
RaphaelConfirm if there are any triggers on the table that might be causing this.
"Raphael Rodrigues" <rrodrigues@.cmsolucoes.com.br> wrote in message
news:OwPvNdq7FHA.2092@.TK2MSFTNGP12.phx.gbl...
>I have a sequence of 3 operations, and its repeated many times by an
>aplication (timer loop)
> 1) Open a transaction
> 2) Execute 1 (ONE) insert in a "simple" table. This table has only an
> auto-increment identificator and some fields numbers and chars.
> 3) Comitt the transaction
> This proccess is executed normaly during many days, but many times this
> command returns "0 rows affected" to the application, but with no
> exception, just returns "0 rows affected".
> The version is 2000 with all SP.
> Thanks
> Raphael
>|||Also confirm if there are any constraints.
"Raphael Rodrigues" <rrodrigues@.cmsolucoes.com.br> wrote in message
news:OwPvNdq7FHA.2092@.TK2MSFTNGP12.phx.gbl...
>I have a sequence of 3 operations, and its repeated many times by an
>aplication (timer loop)
> 1) Open a transaction
> 2) Execute 1 (ONE) insert in a "simple" table. This table has only an
> auto-increment identificator and some fields numbers and chars.
> 3) Comitt the transaction
> This proccess is executed normaly during many days, but many times this
> command returns "0 rows affected" to the application, but with no
> exception, just returns "0 rows affected".
> The version is 2000 with all SP.
> Thanks
> Raphael
>|||Can you show the code? Where are you getting the 0 rows affected? By the
way there is no need to wrap a single Insert in a transaction as it is
ATOMIC on it's own.
Andrew J. Kelly SQL MVP
"Raphael Rodrigues" <rrodrigues@.cmsolucoes.com.br> wrote in message
news:OwPvNdq7FHA.2092@.TK2MSFTNGP12.phx.gbl...
>I have a sequence of 3 operations, and its repeated many times by an
>aplication (timer loop)
> 1) Open a transaction
> 2) Execute 1 (ONE) insert in a "simple" table. This table has only an
> auto-increment identificator and some fields numbers and chars.
> 3) Comitt the transaction
> This proccess is executed normaly during many days, but many times this
> command returns "0 rows affected" to the application, but with no
> exception, just returns "0 rows affected".
> The version is 2000 with all SP.
> Thanks
> Raphael
>|||1) Create TABLE COMMAND
CREATE TABLE [cm].[BILHETE] (
[IDBILHETE] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[BILHETE] [varchar] (500) COLLATE Latin1_General_CI_AS NULL ,
[IDHOTEL] [numeric](18, 0) NULL ,
[DATACAPTURA] [datetime] NULL ) ON [PRIMARY]
2) Insert COMMAND executed by the application. Only an example, the error is
intermittent and random.
INSERT INTO BILHETE (BILHETE, IDHOTEL, DATACAPTURA) VALUES
('1450290000001010022161313', 1, getDate());
3) I've already tried with a Stored Procedure, but accurs the same problem.
After many times the SP returns "0 (zero) rows affected". Do not insert
nothing and no exceptions are generated.
CREATE PROCEDURE CM.SP_BILHETE(@.Bilhete AS Varchar(220),@.IdHotel AS
Numeric ) AS
INSERT INTO BILHETE (BILHETE,IDHOTEL,DATACAPTURA) VALUES
(@.Bilhete,@.IdHotel,getdate());
3) The table has the follow DELETE TRIGGER, that saves the deleteds records
in another table _BILHETE with the same structure:
CREATE TRIGGER tdBilhete
ON BILHETE
FOR DELETE AS
DECLARE @.STRBILHETE VARCHAR(120)
DECLARE @.IDHOTEL NUMERIC
SELECT @.STRBILHETE = D.BILHETE FROM DELETED D
SELECT @.IDHOTEL = D.IDHOTEL FROM DELETED D
INSERT INTO _BILHETE (BILHETE,IDHOTEL,DATACAPTURA) VALUES
(@.STRBILHETE,@.IDHOTEL,getdate());
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> escreveu na mensagem
news:efrF9oq7FHA.3416@.TK2MSFTNGP15.phx.gbl...
> Can you show the code? Where are you getting the 0 rows affected? By the
> way there is no need to wrap a single Insert in a transaction as it is
> ATOMIC on it's own.
> --
> Andrew J. Kelly SQL MVP
>
> "Raphael Rodrigues" <rrodrigues@.cmsolucoes.com.br> wrote in message
> news:OwPvNdq7FHA.2092@.TK2MSFTNGP12.phx.gbl...
>|||Well you still didn't say how you determined the result was 0. I see no
code to indicate where you are collecting @.@.ROWCOUNT or returning any value.
Why are you using Numeric(18,0) when the value is obviously an Integer? You
are using 9 bytes to store what you can in 4 bytes with an Integer. You
also do not specifyt he size in the parameter of the sp. Always specify the
size when you declare a DataType or you can get unexpected results. Are you
sure there are no INSERT triggers on this table?
Andrew J. Kelly SQL MVP
"Raphael Rodrigues" <rrodrigues@.cmsolucoes.com.br> wrote in message
news:esPC7Gr7FHA.3224@.TK2MSFTNGP09.phx.gbl...
> 1) Create TABLE COMMAND
> CREATE TABLE [cm].[BILHETE] (
> [IDBILHETE] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> [BILHETE] [varchar] (500) COLLATE Latin1_General_CI_AS NULL ,
> [IDHOTEL] [numeric](18, 0) NULL ,
> [DATACAPTURA] [datetime] NULL ) ON [PRIMARY]
> 2) Insert COMMAND executed by the application. Only an example, the error
> is
> intermittent and random.
> INSERT INTO BILHETE (BILHETE, IDHOTEL, DATACAPTURA) VALUES
> ('1450290000001010022161313', 1, getDate());
> 3) I've already tried with a Stored Procedure, but accurs the same
> problem.
> After many times the SP returns "0 (zero) rows affected". Do not insert
> nothing and no exceptions are generated.
> CREATE PROCEDURE CM.SP_BILHETE(@.Bilhete AS Varchar(220),@.IdHotel AS
> Numeric ) AS
> INSERT INTO BILHETE (BILHETE,IDHOTEL,DATACAPTURA) VALUES
> (@.Bilhete,@.IdHotel,getdate());
> 3) The table has the follow DELETE TRIGGER, that saves the deleteds
> records
> in another table _BILHETE with the same structure:
> CREATE TRIGGER tdBilhete
> ON BILHETE
> FOR DELETE AS
> DECLARE @.STRBILHETE VARCHAR(120)
> DECLARE @.IDHOTEL NUMERIC
> SELECT @.STRBILHETE = D.BILHETE FROM DELETED D
> SELECT @.IDHOTEL = D.IDHOTEL FROM DELETED D
> INSERT INTO _BILHETE (BILHETE,IDHOTEL,DATACAPTURA) VALUES
> (@.STRBILHETE,@.IDHOTEL,getdate());
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> escreveu na mensagem
> news:efrF9oq7FHA.3416@.TK2MSFTNGP15.phx.gbl...
>|||The result was determined ZERO trought the ROWSAFFECTED property from DB
connection class, that returns the @.@.ROWCOUNT value. So i verified through a
select tha the record was no inserted.
The SP was an alternative, but even in the application the INSERT generated
the same error.
There's no INSERT TRIGGER on this table.
Thanks Andrew i really appreciated your dedication with my problem...
Raphael
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> escreveu na mensagem
news:%23u$agwr7FHA.4036@.TK2MSFTNGP11.phx.gbl...
> Well you still didn't say how you determined the result was 0. I see no
> code to indicate where you are collecting @.@.ROWCOUNT or returning any
> value. Why are you using Numeric(18,0) when the value is obviously an
> Integer? You are using 9 bytes to store what you can in 4 bytes with an
> Integer. You also do not specifyt he size in the parameter of the sp.
> Always specify the size when you declare a DataType or you can get
> unexpected results. Are you sure there are no INSERT triggers on this
> table?
> --
> Andrew J. Kelly SQL MVP
>
> "Raphael Rodrigues" <rrodrigues@.cmsolucoes.com.br> wrote in message
> news:esPC7Gr7FHA.3224@.TK2MSFTNGP09.phx.gbl...
>|||Are there any unique indexes on the table other than the PK constraint? If
you are positive there is no other triggers that can affect this then there
must be an error of some type generated when it fails to insert. In the sp
try this to see if it makes any difference.
DECLARE @.Error INT, @.Rows INT
INSERT INTO BILHETE (BILHETE,IDHOTEL,DATACAPTURA) VALUES
(@.Bilhete,@.IdHotel,getdate());
SELECT @.Error = @.@.ERROR, @.Rows = @.@.ROWCOUNT
IF @.Error <> 0
RAISERROR(@.Error, 16,1)
RETURN @.Rows
Andrew J. Kelly SQL MVP
"Raphael Rodrigues" <rrodrigues@.cmsolucoes.com.br> wrote in message
news:eEtxEAs7FHA.1420@.TK2MSFTNGP09.phx.gbl...
> The result was determined ZERO trought the ROWSAFFECTED property from DB
> connection class, that returns the @.@.ROWCOUNT value. So i verified through
> a select tha the record was no inserted.
> The SP was an alternative, but even in the application the INSERT
> generated the same error.
> There's no INSERT TRIGGER on this table.
> Thanks Andrew i really appreciated your dedication with my problem...
> Raphael
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> escreveu na mensagem
> news:%23u$agwr7FHA.4036@.TK2MSFTNGP11.phx.gbl...
>|||Good idea Andrews,
I will test this and after i post here the result.
tks,
Raphael
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> escreveu na mensagem
news:O$TB20s7FHA.3880@.TK2MSFTNGP12.phx.gbl...
> Are there any unique indexes on the table other than the PK constraint? If
> you are positive there is no other triggers that can affect this then
> there must be an error of some type generated when it fails to insert. In
> the sp try this to see if it makes any difference.
> DECLARE @.Error INT, @.Rows INT
> INSERT INTO BILHETE (BILHETE,IDHOTEL,DATACAPTURA) VALUES
> (@.Bilhete,@.IdHotel,getdate());
> SELECT @.Error = @.@.ERROR, @.Rows = @.@.ROWCOUNT
> IF @.Error <> 0
> RAISERROR(@.Error, 16,1)
> RETURN @.Rows
>
> --
> Andrew J. Kelly SQL MVP
>
> "Raphael Rodrigues" <rrodrigues@.cmsolucoes.com.br> wrote in message
> news:eEtxEAs7FHA.1420@.TK2MSFTNGP09.phx.gbl...
>