Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2002
    Posts
    7

    Unanswered: *** 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
    --------------------------------------------------------------------------------
    Attached Files Attached Files

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    Can you post your stored procedure ? You may want to repost the profiler in zip format rather than rar.

  3. #3
    Join Date
    Sep 2002
    Posts
    7
    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.
    Attached Files Attached Files

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    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.

  5. #5
    Join Date
    Feb 2002
    Posts
    2,232
    Also, which version of sql server are you using - including service pack ?

  6. #6
    Join Date
    Sep 2002
    Posts
    7
    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)
    )

  7. #7
    Join Date
    Sep 2002
    Posts
    7
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •