using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Transactions;


public partial class StoredProcedures
{
  [Microsoft.SqlServer.Server.SqlProcedure]
  public static void DoPseudoAutonomous()
  {
    string ConnStr = 
      "server=sameserver;integrated security=sspi;database=samedb";
    using (TransactionScope ts1 = new TransactionScope())
    using (SqlConnection conn1 =
           new SqlConnection("context connection=true"))
    {
      conn1.Open();
      // do work on connection 1, then
      {
        using (TransactionScope ts2 =
                 new TransactionScope(TransactionScopeOption.RequiresNew))
        using (SqlConnection conn2 = new SqlConnection(ConnStr))
        {
          conn2.Open();
          // do work on connection 2
          ts2.Complete();
        }
        // ask to commit transaction1
        ts1.Complete();
      }
    }
  }
}
