Issue in sending SMS via sp_OAMethod in MS-SQL 2008

  • Dear All,

    Hi I am using below given procedure to send SMS from Ms-SQL. it's working fine till yesterday night. But, today I am getting following error: -

    -2146697212

    Msg 50000, Level 16, State 1, Procedure pr_SendSmsSQL, Line 61

    sp_OAMethod Send FAILED!

    I added "sp_OAGetErrorInfo" to know the exact error then it shows me below given message: -

    ----------

    Error Source Description

    msxml3.dll Unable to connect to the target server.

    ----------

    Please guide how to solve this issue. The same code is running properly on another SQL Server.

    Store Procedure code : -

    ------------------------------

    USE [TEST]

    GO

    /****** Object: StoredProcedure [dbo].[pr_SendSmsSQL] Script Date: 03/30/2012 13:10:52 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    /*

    Purpose : To send SMS from SQL.

    declare @sResponse as varchar(1000)

    exec pr_SendSmsSQL '1234567891','This Is a Test SMS From SQL Server - Regards, BSethi24',''

    print @sResponse

    */

    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='http://122.22.12.12/smsportal/sendsms.aspx?usr=test&pass=test&senderid=BSethi24&mobileno=#MobNo#&messagetext=#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 s_SmsLog (Id,Mobile_No,SMSText,SMSResponse,Created_By,Created_Date,Modified_By,Modified_Date)

    values(0,@MobileNo,@smstext,@sResponse,'System',GETDATE(),null,null)

    End

    ------------------------------

  • Unable to connect to the target server.

    That message sounds to me like the SMS portal is down. I would start with whoever maintains http://122.22.12.12/smsportal/sendsms.aspx.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply