<pre lang="c#">USE [test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spSendSmsSQL]
--@Mobile varchar(20),
--@smstext as varchar(200)
AS
BEGIN
declare @Mobile as varchar(200)
declare @smstext as varchar(200)
DECLARE @iReq int, @hr int
DECLARE @sUrl AS varchar(500)
DECLARE @errorSource VARCHAR(8000)
DECLARE @errorDescription VARCHAR(8000)
EXEC @hr = sp_OACreate 'Microsoft.XMLHTTP', @iReq OUT
print @hr
if @hr <> 0
RAISERROR('sp_OACreate Microsoft.XMLHTTP FAILED!', 16, 1)
set @Mobile=(select MobileNo from tbl_MobileNo)
set @smstext='This is line 1.' + CHAR(10) + 'This is line 2.'
SET @sUrl='http://xxxxxxx/smpp/sendsms?username=xxx&password=xxx&to=#MobNo#&from=Al Ahli D C&text=#Msg#'
SET @sUrl = REPLACE(@sUrl,'#MobNo#',@Mobile)
SET @sUrl = REPLACE(@sUrl,'#Msg#',@smstext)
-- sms code start
EXEC @hr = sp_OAMethod @iReq, 'Open', NULL, 'GET', @sUrl, true
IF @hr <> 0
RAISERROR('sp_OAMethod Open FAILED!', 16, 1)
EXEC @hr = sp_OAMethod @iReq, 'send'
SELECT @iReq
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
EXEC @hr = sp_OAGetProperty @iReq
END
What I have tried:
I am trying to send SMS from MSSQL server using SMS Gateway. But SMS is going in paragraph Like "This is line 1.' + CHAR(10) + 'This is line 2. " i want break line or new line for next word how i can achieve from MSSQL Server.
i want SMS like that
This is line 1.
This is line 2.