Stored procedure calls and transactions

Thread is closed for posting
4 posts, 1 answers
  1. Chris Jansen
    Chris Jansen avatar
    7 posts
    Member since:
    Sep 2009

    Posted 30 Dec 2009 Link to this post

    Hi-

    We're using OpenAccess with a SQL Server 2008 back-end. When using OpenAccess to call stored procedures,  it is apparent that the stored procedure call is wrapped in a database transaction. This is problematic because we have stored procedure calls that need to rollback in case of error. Calling ROLLBACK in the stored proc is causing issues with the database connection from OpenAccess. Our assumption is that this is because the ROLLBACK affects the transaction initiated by OpenAccess. Some or all subsequent stored procedure calls fail with an exception.

    Is there a way to disable wrapping stored procedure calls in a transaction?

  2. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 04 Jan 2010 Link to this post

    Hello Chris Jansen,

    when using the GetSqlQuery method Telerik OpenAccess ORM does not start a transaction implicitly. Instead a connection is given from the connection pool and OpenAccess uses this connection to execute the stored procedure. However in some cases a connection might get "attached" to a scope where a transaction has already started. Using this connection will result in the behavior your described. To avoid that can you please try to use separate scope for executing your stored procedure calls. This should fix your problem.

    Sincerely yours,
    Petar
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Watch a video on how to optimize your support resource searches and check out more tips on the blogs.
  3. Chris Jansen
    Chris Jansen avatar
    7 posts
    Member since:
    Sep 2009

    Posted 04 Jan 2010 Link to this post

    Hello Petar-

    When we encounter a "parallel transactions" exception in our implementation, the execution stack trace from the stored procedure call indicates that a transaction is getting created. We are not doing anything to begin this transaction. Can you explain this discrepancy?

       at OpenAccessRuntime.Relational.RelationalQueryResult.createFetchResult(Connection conParam, SqlDriver sqlDriver, Boolean scrollableParam, FetchSpec fetchSpec, RelationalCompiledQuery relationalCompiledQuery, RelationalStorageManager storageManager, Object[] paramVals, Int32 fetchSize, Int32 maxRows)  ---> Telerik.OpenAccess.RT.sql.SQLException: Please check, that the genericADO2 driver is not used with SQL Server 2000 as it does not support MARS; please use instead in the backendconfiguration element: backend="mssql" driver="ntds"  
     ---> System.InvalidOperationException: SqlConnection does not support parallel transactions. 
       at System.Data.SqlClient.SqlInternalConnection.BeginSqlTransaction(IsolationLevel iso, String transactionName) 
       at System.Data.SqlClient.SqlInternalConnection.BeginTransaction(IsolationLevel iso) 
       at System.Data.SqlClient.SqlConnection.BeginDbTransaction(IsolationLevel isolationLevel) 
       at System.Data.Common.DbConnection.System.Data.IDbConnection.BeginTransaction(IsolationLevel isolationLevel) 
       at Telerik.OpenAccess.RT.Adonet2Generic.Impl.Sql2005Factory.Telerik.OpenAccess.RT.Adonet2Generic.Impl.IADOFactory.BeginTransaction(ConnectionImp c) 
       at Telerik.OpenAccess.RT.Adonet2Generic.Impl.ConnectionImp.BeginTxn() 
       at Telerik.OpenAccess.RT.Adonet2Generic.Impl.ConnectionImp.get_CurrentTransaction() 
       at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.Prepare() 
       at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.executeQuery() 
     

    Thanks
    Chris

  4. Answer
    Ady
    Admin
    Ady avatar
    589 posts

    Posted 04 Jan 2010 Link to this post

    Hello Chris Jansen,

     Sorry for the misleading information posted earlier. OpenAccess does initiate a transaction before calling the stored procedure. This is by design as the same code is used to perform CUD operations that use stored procedures, which requires a transaction. We will update the documentation accordingly.

    The only way to call a stored procedure that initiates it's own transaction is to call it directly using the ADO.NET API.

    Your Telerik points have been updated.

    Once again we are sorry for the inconvenience caused.

    Kind regards,
    Ady
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Watch a video on how to optimize your support resource searches and check out more tips on the blogs.
Back to Top