Re: Sql Server, pessimist locking and transactions

Maybe there is some trigger executed that closes the transaction.

I remember such issue with SQL Server.

Have a look at the MSDN:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_22lq.asp

"
Placing COMMIT TRANSACTION or COMMIT WORK statements in a trigger is 
not recommended.

When triggers that include ROLLBACK TRANSACTION statements are 
executed from a batch, they cancel the entire batch. In the following 
example, if the INSERT statement fires a trigger that includes a 
ROLLBACK TRANSACTION, the DELETE statement is not executed because the 
batch is canceled:

"

Seb

Nuno Godinho wrote:
> 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.
> 
>          
>