Send SMS & Email Using Backend

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

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:

Procedure:

Hi Kailash,

You need to replace 'responseText' in 

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

with respected response property.

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

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())

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

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.