Send SMS from SQLServer stored procedure - EsendexSDK2.dll

[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

[josh_b]

I am using this sp successfuly with SQL 2008 Express Edition with Advanced Services. But when I move it into production rinning SQL 2008 Standard Edition (64-bit) I get the error:
Msg 50000, Level 16, State 1, Line 14
Class not registered
Msg 50000, Level 16, State 1, Line 22
sp_OAMethod usage: ObjPointer int IN, MethodName varchar IN [, @returnval <any> OUT [, additional IN, OUT, or BOTH params]]
Msg 50000, Level 16, State 1, Line 34
sp_OAMethod usage: ObjPointer int IN, MethodName varchar IN [, @returnval <any> OUT [, additional IN, OUT, or BOTH params]]

I have registered the dll correctly. Does anyone have any ideas. Is it as 32 bit dll getting called from a 64 bit sql server that is causing the issue.