This question is locked. New answers and comments are not allowed.
Chris Jansen
Top achievements
Rank 1
Rank 1
Chris Jansen
asked on 30 Dec 2009, 07:05 AM
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?
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?
3 Answers, 1 is accepted
0
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.
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.
0
Chris Jansen
Top achievements
Rank 1
Rank 1
answered on 04 Jan 2010, 04:58 PM
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?
Thanks
Chris
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
0
Accepted
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,
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.
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.