Is it possible to create a Rigger that invokes a web service?

Asked

Viewed 1,212 times

6

I am working on the integration of two databases.

It is a system that needs the status of employees in HR.

For performance reasons, I can not point out all queries that depend on employees for the HR base, what I did was create a feature to import this data to my base.

Currently I have created in my system a functionality that in certain intervals of time searches the data from the HR database.

But I would like to change the implementation to something like, when the data in the database is changed, invoke a webservice on my system that will import the HR data, and update my database.

Since we can only work in the database, and not in the RH system, I thought if it would be possible to create a Trigger in the RH database that triggers my process in the other system.

It is then possible to create a Trigger in SQL Server that invokes a webservice?

sql sql-server web-service integration

  • Can’t make a Linked Server? It would be much simpler and efficient.

  • @utluiz I would like to avoid this approach. So that my system is more independent of the HR database. I also worry about not being responsible for a performance drop in the RH system throughout the day, so I preferred to import the data rather than directly consult them.

  • So, in fact, efficient would not be, because Linked Server, depending on the Hardware, ends up causing the bank, as a whole, to lose a lot of speed... I learned, through conversations with DBA’s, that if you can link a DLL in the bank, using it as if it were a function, you could seek to inform yourself about it, is a great way out for this type of situation.

  • 1

    @What do you mean "depending on the hardware"? At what point (other than a bad implementation) a direct connection from one instance of SQL Server to another may be superior to you invoking a service from a system that in turn will connect to the database and save the information?

  • Oh yes, sorry, I expressed myself wrong, actually, what I meant is the Linked Server of one server, physical, for the other is that, depending on the Hardware of the servers, can make the bank slow down, that was the purpose, not from one instance to the other.

1 answer

3


Getting only on the merits of the web service invocation issue from a Rigger, there is this article in English.

The logic is to use the Procedure sp_OAMethod which allows calling a method from an OLE object.

DECLARE @Param1 INT
DECLARE @obj INT
DECLARE @valorRetorno INT
DECLARE @url VARCHAR(200)
DECLARE @response VARCHAR(4000)

SET @Param1 = 35

SET @url = 'http://localhost/WebServices/Service1.asmx?Param1=' + CONVERT(VARCHAR, @Param1) + ''

EXEC sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT
EXEC sp_OAMethod @obj, 'Open', NULL, 'Get', @url, false
EXEC sp_OAMethod @obj, 'send'
EXEC sp_OAGetProperty @obj, 'responseText', @response OUT

SELECT @response AS Param1
EXEC sp_OADestroy @obj

The variable @response contains the callback code GET.

There are other ways, such as invoking Dlls that call the web services.
Other articles in English:

Browser other questions tagged sql sql-server web-service integration

You are not signed in. Login or sign up in order to post.