title: SQLCLR execution fails within a trigger: "transaction context in use by another session."
body: <p>I try to execute a SQLCLR stored procedure which uses a <code>HttpWebRequest</code>. Everything runs fine if I execute it alone:</p>
  
  <pre class="lang-sql prettyprint-override"><code>DECLARE @idReservation uniqueidentifier
  DECLARE @motif int
  DECLARE @idCuma uniqueidentifier
  
  set @idReservation = '52D1A203-A102-464A-8E2D-652B02A2ABDB'
  
  set @idCuma = '40EAB86E-A61B-4646-8AFB-F2C77264E337'
  
  -- À faire : définir des valeurs de paramètres ici.
  
  EXECUTE [BDchantiers].[dbo].[CLRenvoyerUnSms] 
     @idReservation 
    ,1
    ,@idCuma
  GO
  </code></pre>
  
  <p>But when I try to use it in my trigger, my trigger can't even run:</p>
  
  <pre class="lang-sql prettyprint-override"><code>USE [BDchantiers]
  GO
  /****** Trigger [dbo].[tr_HAB_Chantier_delete]    Script Date: 08/03/2017 09:39:06 ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  ALTER trigger [dbo].[tr_HAB_Chantier_delete] on [dbo].[Chantier]
  after delete
  not for replication
  as
  declare @chaID uniqueidentifier
  declare @rolID uniqueidentifier
  declare @cumaID uniqueidentifier
  declare @oldRespID uniqueidentifier
  
  -- ID du role 'opérateur de chantier'
  set @rolID = 'FA236022-B6EB-4A25-8711-59693747CDDF'
  select @cumaID = ACT_ENT_CLEF from deleted i join activite a on i.CHA_ACT_CLEF = a.ACT_CLEF
  select @chaID = CHA_ID from deleted
  EXECUTE [BDchantiers].[dbo].[CLRenvoyerUnSms] 
      '52D1A203-A102-464A-8E2D-652B02A2ABDB',
      1,
      '40EAB86E-A61B-4646-8AFB-F2C77264E337'
  select @oldRespID = CHA_UTL_CLEF from deleted
  ...
  </code></pre>
  
  <p>I get the following error:</p>
  
  <blockquote>
    <p>transaction context  in use by another session.</p>
  </blockquote>
  
  <p>This code do the http request :</p>
  
  <pre><code> public static void SendRequest(string postData)
      {
          string address = "http://www.envoyer**.com/api/message/send";
          HttpWebRequest request = (HttpWebRequest)WebRequest.Create(address);
          request.ContentType = "application/x-www-form-urlencoded";
          request.Method = "POST";
          string portiklogin = "****";
          string portikpassword = "****";
          NetworkCredential myCred = new NetworkCredential(portiklogin, portikpassword, "");
          request.Credentials = myCred;    
  
          using (var streamWriter = new StreamWriter(request.GetRequestStream()))
          {
              //string json = byteArray;
  
              streamWriter.Write(postData);
              streamWriter.Flush();
          }
  </code></pre>
  
  <p>Besind this a do a lot of database connexion (the same database who own the trigger/ DBchantiers).</p>
  
  <p>I simply creat the connexion this way :</p>
  
  <pre><code> public static void envoyerUnSms(SqlGuid idReservation, int motif, SqlGuid idCuma)
  {
      using (SqlConnection conn = new SqlConnection())
      {
  
          conn.ConnectionString = "Data Source=*****; Initial Catalog=BDchantiers;Integrated Security=False;  User Id=***; Password=****; MultipleActiveResultSets=True";
  
          conn.Open();
          int sold = CalculerSoldeSMS(idReservation, motif, idCuma, conn);
  
          GererEnvoiSMSReservation(idReservation, motif, sold, conn);
  
      }
  </code></pre>
  
  <p>Thanks for your answers!</p>