I am trying to send SMS using a web api from sql server. I have error in sp_OAMethod. Below written is the code for the same
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
BEGIN
Raiserror('sp_OACreate Microsoft.XMLHTTP FAILED!', 16, 1)
SET @sUrl='http://alerts.ebensms.com/api/v3/?method=sms&api_key=******&to=#MobNo#&sender=XXXX&message=#Msg#'
SET @sUrl=REPLACE(@sUrl,'#MobNo#','0000000000')
SET @sUrl=REPLACE(@sUrl,'#Msg#','Thanks')
SELECT @sUrl
---- sms code start
EXEC @hr = sp_OAMethod @iReq, 'Open', NULL, 'GET', @sUrl,true
PRINT @hr
END
IF @hr <> 0
Raiserror('sp_OAMethod Open FAILED!', 16, 1)
EXEC @hr = sp_OAMethod @iReq, 'send'
SELECT @iReq
PRINT 'open failed'
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'
PRINT @hr
INSERT INTO tblUnCandidateSendSMSLog (Id,mobile,sendtext,created) values (12,'0000000000','send','AAAAAAA-B23C80FD16D4')
END
EXEC sp_OAGetErrorInfo
The error was in relation to
sp_OAMethod @iReq, 'Open', NULL, 'GET', @sUrl,true
where in it said send failed.
The issue is now resolved by
sp_OAMethod @iReq, 'Open', NULL, 'POST', @sUrl,false
and changing
sp_OACreate 'Microsoft.XMLHTTP', @iReq OUT
to
sp_OACreate 'MSXML2.XMLHTTP', @iReq OUT;