RE: Sql Server, pessimist locking and transactions

Good Morning Martha.
    
    There's one thing calling my atention in this output: naterr = -999 that identifies the native error ocurred on the ms-sqlserver. As far I can see after a quick search on the Net this is an unexpected error on the SQL Server.
    Could you try to change the commit instruction to "commit transaction" to see what happens ?
    
    I know is not much of a help, but it's the best I can came out with!
    HIH
    
    Nuno
 
 
 

-----Mensagem original-----
De: Martha Rangel [mailto:[email protected]]
Enviada: terça-feira, 16 de Agosto de 2005 14:54
Para: [email protected]
Assunto: RE: [fourjs-users] Sql Server, pessimist locking and transactions
Importância: Alta



Good Morning Nuno,

 

I ran my code setting FGLSETDEBUG = 5 and I redirected the stdout to a file.  I looked through the entire stdout file and I could not see any error at all. All of the sqlcodes were 0 or 100.  The error I received at the end of the program is: 

 

SqlHandleMsv.c       00913 3 Nat stmt1 = 

SqlHandleMsv.c       00917 3 Nat stmt2 = 

SqlSessionMsv.c      00107 2 Could not get diagnostic informations.

SqlSessionMsv.c      00403 2 Couldn't execdirect : commit transaction                 

SqlSessionMsv.c      00434 2 Commit transaction failed (naterr=-999).

SQL: COMMIT WORK

 | 4gl source      : lowlevel.4gl line=1275

 | sqlcode         : -6372

 | sql driver      : ident='static'

 | sql connection  : ident='uc' (fglname='pubs')

 

SqlSessionMsv.c      00107 2 Could not get diagnostic informations.

SqlSessionMsv.c      00403 2 Couldn't execdirect : rollback transaction               

SqlSessionMsv.c      00449 2 Rollback transaction failed (naterr=-999).

SQL: CLOSE DATABASE

 | 4gl source      : exitprog.4gl line=42

 | sqlcode         : 0  

 

But the thing that I do not understand, our customer is running like 150 programs in that SQL Server Box and all of them start transactions and commit/rollback transactions OK.  This is the only program failing and I really do not know what the reason is or what I can do to resolve this issue.  This program fails all the time. 

 

Any ideas would be appreciated.

 


  _____  


Martha Rangel

Software Developer

 



 

Phone: (613) 226-5511 ext. 2215
Fax: (613) 226-3377
Email: [email protected]
1 Antares Drive, Suite 400
Ottawa, Ontario, Canada
K2E 8C4

 

This message and any attachments are confidential to the ordinary user of the e-mail address to which it was addressed and may also be privileged. If you are not the addressee you may not copy, forward, disclose or use any part of the message or its attachments and if you have received this message in error, please notify the sender immediately by return e-mail and delete it from your system. The sender does not accept liability for any errors or omissions in the context of this message that arise as a result of Internet transmission. Any opinions contained in this message are those of the author and are not given or endorsed by the Harris company or office through which this message is sent unless otherwise clearly indicated in this message and the authority of the author to so bind the Harris entity referred to is duly verified. 

 


  _____  


From: [email protected] [mailto:[email protected]] On Behalf Of Nuno Godinho
Sent: Tuesday, August 16, 2005 3:52 AM
To: [email protected]
Subject: RE: [fourjs-users] Sql Server, pessimist locking and transactions

 

Hi. 

    I had the same problem months ago. If I recall it correctly it happens because whenever an exception ocurs when executing a SQL statement the SQL Server executes a rollback with no acknoledgement of the 4Js.

 

    I managed do solve it creating a function to begin and to end transactions. Every time it starts a transaction it increnments a counter. Every time it closes a transaction, either by rollback and commit it decrements the counter.

    Before a begin/commit/rollback work I check the number of pending transactions using "SELECT @@TRANCOUT". Whenever I find a diference between the number of transaction nad the my counter I generate an exception and terminate the program with an error message.

    

HIH

Nuno

 

 

-----Mensagem original-----
De: Martha Rangel [mailto:[email protected]]
Enviada: quinta-feira, 11 de Agosto de 2005 14:10
Para: [email protected]
Assunto: [fourjs-users] Sql Server, pessimist locking and transactions
Importância: Alta

Hi everyone!

 

I am having a problem in one customer who is running 4GL against a SQL Server database.  The error is raised upon the commit work, the commit work causes an error - 6372, with sqlca.sqlerrd[2] = 0!.  

 

On Informix systems, the program runs without errors.  I reviewed the code and everything is correct.  

 

For what I have read, it seems is a missing alignment between @TRANCOUNT and the expected trancount from the ODBC driver.  

 

Any ideas would be appreciated.  

 

 


  _____  

 


Martha Rangel

Software Developer

 



 

Phone: (613) 226-5511 ext. 2215
Fax: (613) 226-3377
Email: [email protected]
1 Antares Drive, Suite 400
Ottawa, Ontario, Canada
K2E 8C4

 

This message and any attachments are confidential to the ordinary user of the e-mail address to which it was addressed and may also be privileged. If you are not the addressee you may not copy, forward, disclose or use any part of the message or its attachments and if you have received this message in error, please notify the sender immediately by return e-mail and delete it from your system. The sender does not accept liability for any errors or omissions in the context of this message that arise as a result of Internet transmission. Any opinions contained in this message are those of the author and are not given or endorsed by the Harris company or office through which this message is sent unless otherwise clearly indicated in this message and the authority of the author to so bind the Harris entity referred to is duly verified.