*** Can anyone explain me this ***

Hi:
I'm having a very strange and problematic behavior of SQL Server 2000 in my
program.

Program execution:
--------------
1.BEGIN TRANSACTION
2.EXECute for the first time a SP called sp_GAupdARMAZ_MOV, which inserts into a table ARMAZ_MOV a new record.
3.Til this point everything is ok!
4.EXECute for the second time sp_GAupdARMAZ_MOV, which I don't why rollbacks my transaction, aborting obviously the previously inserted record. But still continues execution without any error and inserts another record in my table ARMAZ_MOV.
5.COMMIT TRANSACTION

I make a "select * from armaz_mov" and there's only one record !!!!!!! What the hell? ... Can anyone explain me this???

I attached the SQL profiler trace of this problem, and an image of the exact line where my transaction is rolled back.

I analysed very well my SP and the only reason I encountered to explain this is because of an string concatenation with an integer in a SP line of code that's not even executed !!! Maybe a compilation error is causing this...
If I include a "convert(varchar,id)" in my string concatenation everything
works fine !!!!!!!!

If anyone could help me with this, maybe already had a similar problem, or maybe this is a known issue of SQL Server...

I would appreciate a lot if someone could analise for some minutes the
attached SQL Profiler trace and give me some feedback, opinion ...

Thanks a lot.
Monica
------------------------Can you post your stored procedure ? You may want to repost the profiler in zip format rather than rar.|||Originally posted by rnealejr
Can you post your stored procedure ? You may want to repost the profiler in zip format rather than rar.

Ok I reposted the Profiler trace in zip format and included my SP.|||What is the schema for the table ARMAZ_MOV ? Also, where are you executing the transaction (in query analyzer) ? Have you tried it in query analyzer to see if any errors are reported ? What are the exact statements that you are using to execute this transaction (including the parameters) ? You have to cast your int value to varchar anyway - but I am curious as well.|||Also, which version of sql server are you using - including service pack ?|||Originally posted by rnealejr
Also, which version of sql server are you using - including service pack ?

I execute my program in Delphi 6, W2K, with SQL Server 2000 and SP2 installed.
I'm connecting to my database and executing the SP using ADO.

The schema for my ARMAZ_MOV table is:
--
create table DBO.ARMAZ_MOV
(
ID_ARMAZ_MOV integer not null,
ID_REG_MOV integer not null,
REF_TIPO_MOV char(3) not null,
COD_FABRICA char(2) not null,
REF_TP_ARMAZ char(2) not null,
COD_ARTIGO varchar(10) not null,
DATA_MOV datetime not null,
QTD_MOV decimal(18,3) not null,
QTD_EXIST decimal(18,3) null ,
UNIDADE_MOV varchar(10) not null,
OBS_MOV varchar(200) null ,
INS_BY varchar(10) not null,
INS_DATE datetime not null,
UPD_BY varchar(10) not null,
UPD_DATE datetime not null,
STATUS_EXPORT varchar(15) null
constraint CKC_STATUS_EXPORT_ARMAZ_MO check (STATUS_EXPORT in ('EXPORTADO','NAO_EXPORTADO')),
ERRO_EXPORT varchar(200) null ,
TIPO_REG varchar(15) not null
constraint CKC_TIPO_REG_ARMAZ_MO check (TIPO_REG in ('NORMAL','CORRECCAO')),
ID_ORIGINAL integer null ,
constraint PK_ARMAZ_MOV primary key (ID_ARMAZ_MOV)
)|||I executed the 2 iterations of my program in qry analyser and everything worked fine.

So my conclusion is that problem is not from the DBMS but from the client side (ADO/Delphi).

I have to see if this is a known problem of ADO or Delphi, or not.

Rui Ferreira