SQL Server - Ole Automation Procedures Inside Queue Activation Procedure

1.7k views Asked by At

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?

0

There are 0 answers