title: Send SMS from SQL Server stored procedure and trigger failed
body: <p>I have created a stored procedure for sending a sms and call it from on update trigger </p>
  
  <p>This is my procedure:</p>
  
  <pre><code>ALTER PROCEDURE dbo.[spSendSmsSQL] 
      @Mobile varchar(20), 
      @smstext as varchar(200)
  AS
  BEGIN
      DECLARE @iReq int, @hr int 
      DECLARE @sUrl AS varchar(500) 
      DECLARE @errorSource VARCHAR(8000)
      DECLARE @errorDescription VARCHAR(8000) 
  
      EXEC @hr = sp_OACreate 'Microsoft.XMLHTTP', @iReq OUT        
  
      print @hr        
  
      if @hr &lt;&gt; 0     
          RAISERROR('sp_OACreate Microsoft.XMLHTTP FAILED!', 16, 1) 
  
      SET @sUrl = 'http://url?numbers=#MobNo#&amp;message=#Msg#';
      SET @sUrl = REPLACE(@sUrl,'#MobNo#',@Mobile) 
      SET @sUrl = REPLACE(@sUrl,'#Msg#',@smstext) 
  
      -- sms code start 
      EXEC @hr = sp_OAMethod @iReq, 'Open', NULL, 'GET', @sUrl, true 
  
      IF @hr &lt;&gt; 0 
          RAISERROR('sp_OAMethod Open FAILED!', 16, 1) 
  
      EXEC @hr = sp_OAMethod @iReq, 'send' 
  
      SELECT @iReq
  
      IF @hr &lt;&gt; 0 
      BEGIN 
          EXEC sp_OAGetErrorInfo @iReq, @errorSource OUTPUT, @errorDescription OUTPUT
  
          SELECT 
              [Error Source] = @errorSource,
              [Description] = @errorDescription
  
          RAISERROR('sp_OAMethod Send FAILED!', 16, 1) 
      END 
      ELSE 
          EXEC @hr = sp_OAGetProperty @iReq 
  END
  </code></pre>
  
  <p>It works fine while calling by </p>
  
  <pre><code>EXEC spSendSmsSQL '9999999999','SMS FROM SQLSERVER'
  </code></pre>
  
  <p>But when I'm calling it from my trigger it causes</p>
  
  <blockquote>
    <p>raise error "sp_OAMethod Open FAILED!" AND "sp_OAMethod Send FAILED!"</p>
  </blockquote>
  
  <p>My trigger is :</p>
  
  <pre><code>ALTER TRIGGER [dbo].[trgAfterUpdate] ON [dbo].[recharge_request]
  FOR UPDATE
      AS
          DECLARE @uRefId varchar(20),@operator varchar(20),@rMobile varchar(20),@memberId varchar(20),@etopId varchar(20),@oReference varchar(20);
          DECLARE @status int,@amount int, @tId int;
          DECLARE @nAmount decimal(12,2),@cPercent decimal(12,2);
          DECLARE @isSMS bit;
  
          SELECT @uRefId=i.user_reference_id FROM inserted i; 
          SELECT @operator=i.operator FROM inserted i;
          SELECT @status=i.status FROM inserted i; 
          SELECT @amount=i.amount FROM inserted i;
          SELECT @memberId=i.uid FROM inserted i;
          SELECT @etopId=i.etop_id FROM inserted i;
          SELECT @isSMS=i.is_sms FROM inserted i;
          SELECT @rMobile=i.mobile_no FROM inserted i;
          SELECT @oReference=i.operator_reference_id FROM inserted i;
  
          IF UPDATE (status)
              BEGIN
                  IF @status=7
                      UPDATE member_transaction SET is_succeed=0 WHERE reference_id=@uRefId;
                  ELSE IF @status=1
                      BEGIN
                          SELECT @cPercent=p_amount FROM extra_charges WHERE operator=@operator AND c_status=1;
  
                          IF @cPercent&gt;0
                              BEGIN
                                  SET @nAmount=(@amount*@cPercent)/100;
  
                                  SELECT @tId=id FROM member_transaction WHERE reference_id=@uRefId AND remarks=@operator AND member_id=@memberId AND t_type='D' AND debit=@nAmount
                                  IF @tId IS NOT NULL AND LEN(@tId) &gt; 0
                                      SET @tId=0;
                                  ELSE
                                      BEGIN
                                          INSERT INTO member_transaction(member_id,etop_id,t_type,debit,credit,particular,remarks,reference_id)
                                              VALUES(@memberId,@etopId,'D',@nAmount,0.00,'eDeduct : '+@operator+' '+@uRefId+', from : '+@etopId,@operator,@uRefId);
                                      END
                              END
  
                          UPDATE member_transaction SET is_succeed=1 WHERE reference_id=@uRefId;
                      END
                  ELSE
                      UPDATE member_transaction SET is_succeed=1 WHERE reference_id=@uRefId;
  
                  IF @isSMS=1
                      BEGIN
  
                          DECLARE @Mobile varchar(20);
                          DECLARE @rStatus varchar(20);
                          DECLARE @sms varchar(200);
                          DECLARE @balance decimal(12,2);
                          DECLARE @mName varchar(50);
                          IF @status=7
                              SET @rStatus='FAIL';
                          ELSE IF @status=1
                              SET @rStatus='SUCCESS';
                          IF @rStatus IS NOT NULL AND LEN(@rStatus) &gt; 0
                              BEGIN
  
                                  SELECT @Mobile=mobile,@mName=m_name FROM member_details WHERE id=@memberId;
                                  SELECT @balance=SUM(debit)-SUM(credit) FROM member_transaction WHERE member_id=@memberId AND isnull(reference_id,0) NOT IN(SELECT user_reference_id FROM recharge_request WHERE status=7);
                                  SET @sms ='RECHARGE ' + @rStatus + ' AND CONFIRM. Number: ' + @rMobile + ' Amt: ' + CONVERT(char(6), @amount) + ' TR-ID = '+ @oReference +' . Balance is ' + CONVERT(char(10), @balance);
                                  EXEC spSendSmsSQL @mobile, @sms;
  
                                  INSERT INTO member_transaction(member_id,etop_id,t_type,debit,particular,remarks)
                                      VALUES(@memberId,@etopId,'D',0.15,'SMS Charge for recharge by SMS','SMS Charges');
  
                                  INSERT INTO hidden_charges(m_id,etop_id,m_name,amount,particular)
                                      VALUES(@memberId,@etopId,@mName,0.15,'SMS Charge for recharge by SMS');
  
                              END
                      END
  
              END
  </code></pre>
  
  <p>Please help me where I'm wrong. </p>