使用SQL Server 2008中的触发器和存储过程发送SMS
问题描述:
我在 smslog
表上写了一个触发器
I written a trigger on smslog
table
ALTER TRIGGER [dbo].[mytrigger]
ON [dbo].[smslog]
AFTER INSERT
AS
Declare @MobileNo int
Declare @smstext as varchar(300)
Begin
set @MobileNo = ( select mobile from inserted)
set @smstext = (select smstext from inserted)
set @sResponse = 'test'
Exec pr_SendSmsSQL @MobileNo, @smstext, @sResponse
END
存储过程为
create procedure [dbo].[pr_SendSmsSQL]
@MobileNo varchar(12),
@smstext as varchar(300),
@sResponse varchar(1000) OUT
as
BEGIN
Declare @iReq int,@hr int
Declare @sUrl as varchar(500)
DECLARE @errorSource VARCHAR(8000)
DECLARE @errorDescription VARCHAR(8000)
-- Create Object for XMLHTTP
EXEC @hr = sp_OACreate 'Microsoft.XMLHTTP', @iReq OUT
print @hr
if @hr <> 0
Raiserror('sp_OACreate Microsoft.XMLHTTP FAILED!', 16, 1)
set @sUrl='http://api.clickatell.com/http/sendmsg?user=devendar&password=csx19csx&api_id=3360313&to=#MobNo#&text=#Msg#'
set @sUrl=REPLACE(@sUrl,'#MobNo#',@MobileNo)
set @sUrl=REPLACE(@sUrl,'#Msg#',@smstext)
print @sUrl
-- sms code start
EXEC @hr = sp_OAMethod @iReq, 'Open', NULL, 'GET', @sUrl, true
print @hr
if @hr <> 0
Raiserror('sp_OAMethod Open FAILED!', 16, 1)
EXEC @hr = sp_OAMethod @iReq, 'send'
select @iReq
print @hr
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
Begin
EXEC @hr = sp_OAGetProperty @iReq,'responseText', @sResponse OUT
print @hr
insert into send_log (Id, mobile, sendtext, response, created, createddate)
values(0, @MobileNo, @smstext, @sResponse, 'System', GETDATE())
end
end
我在这里使用clickatell网关请帮助我解决这个问题
I use clickatell gateway here please help me on this what is the issue
当我在 smslog $ c $中插入一行时,我无法发送短信c> table
我在插入触发器并将参数传递给存储过程以发送SMS后使用。
I used after insert in trigger and passed parameters to stored procedure to send SMS.
请在此帮助我
在此先感谢
devendar
Thanks in Advance devendar
答
ALTER TRIGGER [dbo].[mytrigger]
ON [dbo].[smslog]
AFTER INSERT
AS
Declare @MobileNo int
Declare @smstext as varchar(300)
Begin
set @MobileNo = ( select mobile from inserted)
set @smstext = (select smstext from inserted)
set @sResponse = 'test'
Exec pr_SendSmsSQL @MobileNo, @smstext, @sResponse
END
过程
create procedure [dbo].[pr_SendSmsSQL]
@MobileNo varchar(12),
@smstext as varchar(300),
@sResponse varchar(1000) OUT
as
BEGIN
Declare @iReq int,@hr int
Declare @sUrl as varchar(500)
DECLARE @errorSource VARCHAR(8000)
DECLARE @errorDescription VARCHAR(8000)
-- Create Object for XMLHTTP
EXEC @hr = sp_OACreate 'Microsoft.XMLHTTP', @iReq OUT
print @hr
if @hr <> 0
Raiserror('sp_OACreate Microsoft.XMLHTTP FAILED!', 16, 1)
**set @sUrl='http://api.clickatell.com/http/sendmsg?user=devendar&password=csx19csx&api_id=3360313&to=#MobNo#&text=#Msg#'**
**
APi无法正常工作-其余代码非常出色,可以很好地工作
**
** THe APi isnot working properly - remaining code is excellent it working in a great way **
set @sUrl=REPLACE(@sUrl,'#MobNo#',@MobileNo)
set @sUrl=REPLACE(@sUrl,'#Msg#',@smstext)
print @sUrl
-- sms code start
EXEC @hr = sp_OAMethod @iReq, 'Open', NULL, 'GET', @sUrl, true
print @hr
if @hr <> 0
Raiserror('sp_OAMethod Open FAILED!', 16, 1)
EXEC @hr = sp_OAMethod @iReq, 'send'
select @iReq
print @hr
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
Begin
EXEC @hr = sp_OAGetProperty @iReq,'responseText', @sResponse OUT
print @hr
insert into send_log (Id, mobile, sendtext, response, created, createddate)
values(0, @MobileNo, @smstext, @sResponse, 'System', GETDATE())
end
end