title: Send SMS using triggers and stored procedures from SQL Server 2008
body: <p>I written a trigger on <code>smslog</code> table</p>
  
  <pre><code>ALTER TRIGGER [dbo].[mytrigger]    
     ON  [dbo].[smslog]   
     AFTER INSERT    
  AS   
     Declare @MobileNo int   
     Declare @smstext as varchar(300)   
  Begin    
     set @MobileNo = ( select mobile from inserted)  
     set @smstext = (select smstext from inserted)  
     set @sResponse = 'test'
     Exec pr_SendSmsSQL @MobileNo, @smstext, @sResponse  
  END  
  </code></pre>
  
  <p>Stored procedure is</p>
  
  <pre><code>create procedure [dbo].[pr_SendSmsSQL] 
      @MobileNo varchar(12), 
      @smstext as varchar(300), 
      @sResponse varchar(1000) OUT 
  as 
  BEGIN 
     Declare @iReq int,@hr int 
     Declare @sUrl as varchar(500) 
     DECLARE @errorSource VARCHAR(8000)
     DECLARE @errorDescription VARCHAR(8000) 
  
     -- Create Object for XMLHTTP 
     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://api.clickatell.com/http/sendmsg?user=devendar&amp;password=csx19csx&amp;api_id=3360313&amp;to=#MobNo#&amp;text=#Msg#' 
  
     set @sUrl=REPLACE(@sUrl,'#MobNo#',@MobileNo) 
     set @sUrl=REPLACE(@sUrl,'#Msg#',@smstext) 
  
     print @sUrl 
  
     -- sms code start 
     EXEC @hr = sp_OAMethod @iReq, 'Open', NULL, 'GET', @sUrl, true 
     print @hr 
  
     if @hr &lt;&gt; 0 
        Raiserror('sp_OAMethod Open FAILED!', 16, 1) 
  
     EXEC @hr = sp_OAMethod @iReq, 'send' 
     select @iReq
     print @hr 
  
     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 
  Begin
      EXEC @hr = sp_OAGetProperty @iReq,'responseText', @sResponse OUT 
      print @hr
  
      insert into send_log (Id, mobile, sendtext, response, created, createddate) 
      values(0, @MobileNo, @smstext, @sResponse, 'System', GETDATE())
  end
  end
  </code></pre>
  
  <p>I use clickatell gateway here please help me on this what is the issue </p>
  
  <p>I am not able to send SMS when I insert a row into the <code>smslog</code> table </p>
  
  <p>I used after insert in trigger and passed parameters to stored procedure to send SMS.</p>
  
  <p>Please help me on this</p>
  
  <p>Thanks in Advance
  devendar</p>