question

tareqhabib avatar image
tareqhabib asked

SMS IS NOT SENDING USING API

STEP-1 ----------------- THIS IS THE STORED PROCEDURE THAT I USE IN SQL SERVER JOB AND EXECUTE THIS IN EVERY 3 MINUTES.THIS BRINGS DATA FROM ORACLE AND INSERT IT IN A SQL SERVER TABLE. USE [master] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[XX_DO_SR_FROM_ORACLE] AS begin SET XACT_ABORT ON begin transaction INSERT INTO dbo.smsAPI_DO_SR(isdn,msg) SELECT * FROM OPENQUERY(appldb,'SELECT CONCAT(88,MSISDN),MESSAGE FROM APPS.XX_DO_CUST_SMS2 MINUS SELECT CONCAT(88,MSISDN),MESSAGE FROM APPS.XX_DO_CUST_SMS2 AS OF TIMESTAMP SYSTIMESTAMP-(3/1440)'); commit transaction; end STEP-2 --------------- WHEN AFTER DATA INSERTED IN smsAPI_DO_SR TABLE THEN ALL ROWS INSERTED ROW BY ROW TO ANOTHER TABLE NAMED smsAPI_DO_SR_DATA. USE [master] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: -- Description: -- ============================================= ALTER TRIGGER [dbo].[smsDOsr] ON [dbo].[smsAPI_DO_SR] after insert AS declare @i int=1; declare @cnt int; declare @url varchar(500); declare @phone varchar(15); declare @message varchar(200); BEGIN select @cnt = COUNT(*) from smsAPI_DO_SR; while @i<=@cnt begin SET XACT_ABORT ON begin transaction select @phone=isdn, @message=msg from smsAPI_DO_SR where sl=(select MIN(sl) from smsAPI_DO_SR); commit transaction; begin transaction delete from smsAPI_DO_SR where sl=(select MIN(sl) from smsAPI_DO_SR); commit transaction; begin transaction insert into smsAPI_DO_SR_DATA (isdn,msg) values(@phone,@message); commit transaction; SET XACT_ABORT OFF set @i=@i+1; end END STEP-3 ------------- NOW DATA SENDING FROM THIS TABLE TO MOBILE NUMBERS VAI API. USE [master] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: -- Description: -- ============================================= ALTER TRIGGER [dbo].[smsDOsmsSR] ON [dbo].[smsAPI_DO_SR_DATA] for insert AS declare @url varchar(500); declare @phone varchar(15); declare @message varchar(200); IF EXISTS(SELECT * FROM Inserted) BEGIN set @phone = (select isdn from inserted) set @message = (select msg from inserted) set @url= ' http://vas.banglalinkgsm.com/sendSMS/sendSMS?msisdn=' + RTRIM(LTRIM(@phone)) + '&message=' + RTRIM(LTRIM(@message)) + '&userID=npoly&passwd=4e526dbf47599da15cc5129bc2962c0b&sender=NPOLY'; exec HTTP_Request @url; END STEP-4 ---------------- HERE IS THE CODE FOR HTTP REQUEST PROCEDURE USE [master] GO /****** Object: StoredProcedure [dbo].[HTTP_Request] Script Date: 04/18/2017 09:59:18 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[HTTP_Request]( @sUrl varchar(500)) As Declare @obj int ,@hr int ,@msg varchar(255) exec @hr = sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT if @hr <> 0 begin Raiserror('sp_OACreate MSXML2.ServerXMLHttp.3.0 failed', 16,1) return end exec @hr = sp_OAMethod @obj, 'open', NULL, 'POST', @sUrl, false if @hr <>0 begin set @msg = 'sp_OAMethod Open failed' goto eh end exec @hr = sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded' if @hr <>0 begin set @msg = 'sp_OAMethod setRequestHeader failed' goto eh end exec @hr = sp_OAMethod @obj, send, NULL, '' if @hr <>0 begin set @msg = 'sp_OAMethod Send failed' goto eh end exec @hr = sp_OADestroy @obj return eh: exec @hr = sp_OADestroy @obj Raiserror(@msg, 16, 1) return NOTE: -------------- ALL TABLE IS FULFILLED BY DATA BUT WHEN I ASSIGN IT IN JOB SCHEDULE THEN SMS IS NOT SENT.
sms
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
tareqhabib avatar image
tareqhabib answered
The answer is found . Text message contained hash character(#) for which message was not sent.I exclude hash character from the API message body and it is now working. any way thanks a lot......
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.