Issue in sending SMS via sp_OAMethod inMS-SQL 2008

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Issue in sending SMS via sp_OAMethod inMS-SQL 2008

Author

Topic

bsethi24
Starting Member

25 Posts

Posted - 2012-03-31 : 01:46:27

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

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

mfemenel
Professor Frink

1421 Posts

Posted - 2012-03-31 : 04:21:06

I'm not sure if this would connect with your login or with the credentials of the SQL server service but I would start by looking for differences in permissions/ username differences between the server where it works and the server where it does not

Mike
"oh, that monkey is going to pay"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources