I want to call a Webservice when a row inserted into a table.
So I enable Ole Automation Procedures
, call Webservice like below and everything goes fine.
--enabling 'Ole Automation Procedures'
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
--calling Webservice
create PROCEDURE callwebservice
@url varchar(128)=null
AS
Declare @Object as Int;
Declare @ResponseText as varchar(8000);
Exec sp_OACreate 'MSXML2.ServerXMLHttp.3.0', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get', @url ,'false'
Exec sp_OAMethod @Object, 'send'
Exec sp_OAGetProperty @Object, 'ResponseText', @ResponseText OUTPUT
Exec sp_OADestroy @Object
return
But, Since Webservice call is extremely lengthy, I need to do it with Service Broker. so I create a new stored-procedure and pass it as an activation parameter of my Queue
like this:
--declare procedure
create procedure handleNewMessageInQueue
as
begin
DECLARE @TargetDlgHandle UNIQUEIDENTIFIER
DECLARE @ReplyMessage xml
DECLARE @ReplyMessageName
BEGIN TRAN;
receive top(1)
@TargetDlgHandle =Conversation_handle
,@ReplyMessage = Message_Body
,@ReplyMessageName = Message_Type_Name
from newUserQueue;
if @ReplyMessageName = '//goodType'
begin
exec callwebservice 'http://path/to/my/webservice'
end
commit tran;
end
--declare queue
create queue myQueue
with ACTIVATION (
PROCEDURE_NAME = handleNewMessageInQueue,
MAX_QUEUE_READERS =100,
EXECUTE AS owner
);
But this time, no request sent. I checked and I realize that the sp_OACreate
method does not create the Object since the value of @Object
is null
after execution of it. Also when I call sp_OAGetErrorInfo
to get error info, everything is null
I'm using SQL Server 2016
I appreciate if you can find the problem here.
EDIT
I found that if I enable TRUSTWORTHY
on that database everything goes fine. but enabling it can open security hole.
is there any better way to do that?