Microsoft KB Archive/316872

= Error message occurs when you use OLE DB Provider for SQL Server in ADO.NET: &quot;Cannot start more transactions on this session&quot; =

Article ID: 316872

Article Last Modified on 3/9/2006

-

APPLIES TO


 * Microsoft ADO.NET 2.0
 * Microsoft ADO.NET 1.0
 * Microsoft ADO.NET 1.1
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q316872



This article refers to the following Microsoft .NET Framework Class Library namespaces:
 * System.Data
 * System.Data.OleDb
 * System.Data.SqlClient



SYMPTOMS
When you use Microsoft OLE DB Provider for SQL Server (OLEDBSQL) in ADO.NET, if you try to start more than one transaction in a session, you receive the following error message:

System.Data.OleDb.OleDbException: Cannot start more transactions on this session



CAUSE
By design, OLE DB Provider for SQL Server does not allow nested transactions.



Steps to Reproduce the Behavior
 Start Microsoft Visual Studio .NET. Create a Windows Application project in Visual Basic .NET. Form1 is created by default. Double-click Form1 to open the Code window.  Replace the code in Form1 with the following code: Imports System.Data Imports System.Data.OleDb Imports System.Data.SqlClient

Public Class Form1 Inherits System.Windows.Forms.Form

Dim cn As New OleDb.OleDbConnection Dim cmd As New OleDb.OleDbCommand Dim mycmd As New OleDb.OleDbCommand


 * 1) Region &quot; Windows Form Designer generated code &quot;

Public Sub New MyBase.New

'This call is required by the Windows Form Designer. InitializeComponent

'Add any initialization after the InitializeComponent call

End Sub

'Form overrides dispose to clean up the component list. Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean) If disposing Then If Not (components Is Nothing) Then components.Dispose End If       End If        MyBase.Dispose(disposing) End Sub

'Required by the Windows Form Designer Private components As System.ComponentModel.Container

'NOTE: The following procedure is required by the Windows Form Designer 'It can be modified using the Windows Form Designer. 'Do not modify it using the code editor.  Private Sub InitializeComponent '       'Form1 '       Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13) Me.ClientSize = New System.Drawing.Size(292, 273) Me.Name = &quot;Form1&quot; Me.Text = &quot;Form1&quot;

End Sub


 * 1) End Region

Private Sub Form1_Load(ByVal sender As System.Object, _   ByVal e As System.EventArgs) Handles MyBase.Load cn.ConnectionString = &quot;Provider=SQLOLEDB;Data Source=YourServer;&quot; & _ &quot;Initial Catalog=pubs;User ID=YourUserID;Password=YourPassword&quot; cn.Open cmd.Connection = cn       mycmd.Connection = cn        cmd.CommandText = &quot;Insert into stores (stor_id) values('9876')&quot; Dim myTrans As OleDb.OleDbTransaction myTrans = cn.BeginTransaction(IsolationLevel.ReadCommitted)

cmd.Transaction = myTrans

Try cmd.ExecuteNonQuery

myTrans.Begin myTrans.Commit Catch ex As Exception MessageBox.Show(ex.ToString) End Try

End Sub End Class  Modify the connection string as appropriate for your environment.</li> Run the application. Notice that you receive the error that is listed in the &quot;Symptoms&quot; section.</li></ol>

<div class="references_section">