Send SMS & Email Using Backend

Kailash Singh
Kailash...
Participant
66 Points
28 Posts

Hi sir How can send SMS and Email Using SQL when any transaction done in a specified table ?

Views: 8281
Total Answered: 8
Total Marked As Answer: 0
Posted On: 12-Jan-2017 22:18

Share:   fb twitter linkedin
Answers
Rahul Maurya
Rahul M...
Moderator
1172 Points
211 Posts
         

Hi Kailash,

There is no in-build method to sending SMS but you can use external xmlhttp api in store procedure. For email sending there is a method 'msdb.dbo.sp_send_dbmail' that you can use to send email.

For example

DECLARE @tableHTML NVARCHAR(MAX) ; 
SET @tableHTML =
N'<H1>Work Order Report</H1>' +
N'<table border="1">' +
N'<tr><th>Work Order ID</th><th>Product ID</th>' +
N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' +
N'<th>Expected Revenue</th></tr>' +
CAST ( ( SELECT td = wo.WorkOrderID, '',
td = p.ProductID, '',
td = p.Name, '',
td = wo.OrderQty, '',
td = wo.DueDate, '',
td = (p.ListPrice - p.StandardCost) * wo.OrderQty
FROM AdventureWorks.Production.WorkOrder as wo
JOIN AdventureWorks.Production.Product AS p
ON wo.ProductID = p.ProductID
WHERE DueDate > '2004-04-30'
AND DATEDIFF(dd, '2004-04-30', DueDate) < 2
ORDER BY DueDate ASC,
(p.ListPrice - p.StandardCost) * wo.OrderQty DESC
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
 
EXEC msdb.dbo.sp_send_dbmail @recipients='yourfriend@niceone-Works.com',
@subject = 'Nice work order list',
@body = @tableHTML,
@body_format = 'HTML' ;

For more information for sending email follow:https://msdn.microsoft.com/en-in/library/ms190307.aspx

For SMS, I think you need to use third party api in procedure and execute this procedure in trigger.

Trigger:

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

Procedure:

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 <> 0
Raiserror('sp_OACreate Microsoft.XMLHTTP FAILED!', 16, 1)
set @sUrl='https://api.someapi.com/http/sendmsg?user=niceone&password=xyz&api_id=3360313&to=#MobNo#&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 <> 0
Raiserror('sp_OAMethod Open FAILED!', 16, 1)
EXEC @hr = sp_OAMethod @iReq, 'send'
select @iReq
print @hr
if @hr <> 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

 

Posted On: 13-Jan-2017 00:20
Rahul Maurya
Rahul M...
Moderator
1172 Points
211 Posts
         

Hi Kailash,

You need to replace 'responseText' in 

EXEC @hr = sp_OAGetProperty @iReq,'responseText', @sResponse OUT

with respected response property.

Posted On: 17-Apr-2017 23:56
Kailash Singh
Kailash...
Participant
66 Points
28 Posts
         

img

Posted On: 18-Apr-2017 00:32
Kailash Singh
Kailash...
Participant
66 Points
28 Posts
         
ALTER 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 <> 0
Raiserror('sp_OACreate Microsoft.XMLHTTP FAILED!', 16, 1)


set @sUrl='https://nimbusit.co.in/api/swsend.asp?username=test&password=test&sender=YASHMY&sendto=#MobNo#&message=#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 <> 0
Raiserror('sp_OAMethod Open FAILED!', 16, 1)
EXEC @hr = sp_OAMethod @iReq, 'send'
select @iReq
print @hr
if @hr <> 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,'LogID', @sResponse OUT
print @hr
insert into send_log (mobile, sendtext, response, created, createddate)
values( @MobileNo, @smstext, @sResponse, 'System', GETDATE())
end
end
Posted On: 18-Apr-2017 00:40
Kailash Singh
Kailash...
Participant
66 Points
28 Posts
         

Send_log

Posted On: 18-Apr-2017 00:43
Rahul Maurya
Rahul M...
Moderator
1172 Points
211 Posts
         

Hi,

Try different variable '@txtResponse' like following:

Declare @txtResponse nvarchar(max);
EXEC @hr = sp_OAGetProperty @iReq,'responseText', @txtResponse OUT
print @txtResponse
insert into send_log (mobile, sendtext, response, created, createddate)
values( @MobileNo, @smstext, @txtResponse, 'System', GETDATE())

OR

Declare @txtResponse nvarchar(max);
EXEC @hr = sp_OAGetProperty @iReq,'responseXML', @txtResponse OUT
print @txtResponse
insert into send_log (mobile, sendtext, response, created, createddate)
values( @MobileNo, @smstext, @txtResponse, 'System', GETDATE())
Posted On: 18-Apr-2017 02:33
Kailash Singh
Kailash...
Participant
66 Points
28 Posts
         

 

set @sUrl=REPLACE(@sUrl,'#MobNo#',@MobileNo)
set @sUrl=REPLACE(@sUrl,'#Msg#',@smstext)
--print @sUrl
-- sms code start
EXEC @hr = sp_OAMethod @iReq, 'Open', NULL, 'POST', @sUrl, true
--print @hr
if @hr <> 0
Raiserror('sp_OAMethod Open FAILED!', 16, 1)
EXEC @hr = sp_OAMethod @iReq, 'send'
--select @iReq
--print @hr
if @hr <> 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 (mobile, sendtext, response, created, createddate)
values( @MobileNo, @smstext, @sResponse, 'System', GETDATE())
end
end
Posted On: 18-Apr-2017 04:37
Rahul Maurya
Rahul M...
Moderator
1172 Points
211 Posts
         

Hi Guys,

I got success  to  test it on my local SQL Server by modifying configuration as

/*
sp_configure 'show advanced options', 1
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1
GO
RECONFIGURE;
GO
sp_configure 'show advanced options', 1
GO
RECONFIGURE;


CREATE TABLE [dbo].[send_log](
    [ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [mobile] [nvarchar](50) NULL,
    [sendtext] [nvarchar](1000) NULL,
    [response] [nvarchar](500) NULL,
    [created] [nvarchar](50) NULL,
    [createddate] [datetime] NULL
) ON [PRIMARY]
*/

And I modified the store procedure to see the how we are not getting response.

ALTER 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 <> 0
Raiserror('sp_OACreate Microsoft.XMLHTTP FAILED!', 16, 1)


set @sUrl='https://nimbusit.co.in/api/swsend.asp?username=t1yashmaypublicschool&password=90831543&sender=YASHMY&sendto=#MobNo#&message=#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 <> 0
Raiserror('sp_OAMethod Open FAILED!', 16, 1)
EXEC @hr = sp_OAMethod @iReq, 'send'
print 'send'+convert(varchar, @hr)
if @hr <> 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
DECLARE @ResponseText VARCHAR(500)
EXEC @hr = sp_OAGetProperty @iReq,'ResponseText', @ResponseText OUT
if @hr <> 0
Begin
EXEC sp_OAGetErrorInfo @iReq, @errorSource OUTPUT, @errorDescription OUTPUT
print'response Error Source:'+convert(varchar, @errorSource)
print 'response Error Desc:'+@errorDescription
end
else
Begin
EXEC @hr = sp_OAGetProperty @iReq,'ResponseText', @ResponseText OUT
print'response'+convert(varchar, @hr)
print 'response'+@ResponseText
insert into send_log (mobile, sendtext, response, created, createddate)
values( @MobileNo, @smstext, @ResponseText, 'System', GETDATE())
end
end
end

And Now I found following error message as:

response Error Source:msxml3.dll
response Error Desc:The data necessary to complete this operation is not yet available.

I don't know why this is happening although the sms message is sent successfully.  

Posted On: 19-Apr-2017 04:52
 Log In to Chat