Esendex Business SMS Forum

[Nicholas]

One of our customers inquired if it is possible to an send and SMS from within a SQLServer stored procedure. After a small amout of head scratching we came up with this.

/*

Example code to enable SQL Server to send a text message using EsendexSdk2.dll via OLE Automation Stored Procedures

Pre-requisites:

1. EsendexSdk2.dll must be registered using regsrvr32

2. The SQL Server on which the dll is hosted must have access to the Internet and particular the following URL's
https://www.esendex.com/secure/messenger/soap/SendService.asmx?wsdl
http://schemas.xmlsoap.org/soap/envelope
http://www.w3.org/2001/XMLSchema-instance
http://www.w3.org/2001/XMLSchema

3. OLE Automation procedures must be enabled in SQL Server using sp_configure or SQL Server Surface Area Configuration utility

*/

DECLARE @strNumber nvarchar(20),
@strMessage nvarchar(160),
@intCOM int,
@errorSource varchar(8000),
@errorDescription varchar(8000),
@intReturn int,
@strReturn nvarchar(2000)

EXEC @intCOM = sp_OACreate 'Esendex.SendService.2', @intReturn OUTPUT
IF (@intCOM <> 0)
BEGIN
-- Trap errors if any
EXEC sp_OAGetErrorInfo @intReturn, @errorSource OUTPUT, @errorDescription OUTPUT
RAISERROR (@errorDescription, 16, 1)
END

EXEC @intCOM = sp_OAMethod @intReturn, 'Initialise', NULL, 'username@esendex.com', 'password', 'accountreference'
IF (@intCOM <> 0)
BEGIN
-- Trap errors if any
EXEC sp_OAGetErrorInfo @intReturn, @errorSource OUTPUT, @errorDescription OUTPUT
RAISERROR (@errorDescription, 16, 1)
END

SET @strNumber = '441234567891'
SET @strMessage = 'This is a test message from SQL Server'

EXEC @intCOM = sp_OAMethod @intReturn, 'SendMessage', @strReturn OUTPUT, @strNumber, @strMessage, 1

IF (@intCOM <> 0)
BEGIN
-- Trap errors if any
EXEC sp_OAGetErrorInfo @intReturn, @errorSource OUTPUT, @errorDescription OUTPUT
RAISERROR (@errorDescription, 16, 1)
END

EXEC sp_OADestroy @intReturn

Recommended links:

This article provided an excellent starting point for this work: http://www32.brinkster.com/srisamp/sqlArticles/article_31.htm

Details of the EsendexSDK2.dll are here: http://www.esendex.co.uk/Developers/SDKs/ActiveX-SMS-SDK

[VibrantEnergyMatters]

This script has been very helpful, thank you.

Is it possible to retrieve message using the same process and store the results directly to an SQL table?

Many thanks, Martin