x

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.

more ▼

asked Apr 18 at 07:28 AM in Default

avatar image

tareqhabib
1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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......

more ▼

answered May 04 at 04:33 AM

avatar image

tareqhabib
1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2

asked: Apr 18 at 07:28 AM

Seen: 65 times

Last Updated: May 04 at 04:33 AM

Copyright 2016 Redgate Software. Privacy Policy