1

I have to two tables employee and department.

Employee

Id, Name, JoiningDate, SpecialRegistrationDone, DepartmentId

Department

DepartmentId, Name

There is a special department with DepartmentId = 2 and Name = "AI".

There is a Java service pushing the data to Employee and Department tables.

Now, I have to write a polling service (polling frequency once in 15 minutes) which satisfies the condition

select e.* 
from Employee e
inner join Department d on e.DepartmentId = d.DepartmentId
                        and e.DepartmentId = 2
                        and e.JoiningDate > cast(GetDate() as date)
                        and e.specialRegistrationDone = 0

If there are any records fetched then call a Web service say http://specialdepartmentregistration/register and pass the employee details there.

One way to write is to write a Java service which polls the database and execute this query, get the results and calls the web service for resultset.

I am looking at a away to create a service on the SQL Server side only which executes the query and call the web service. How can I write that ? Is it even possible in SQL Server 2014?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
userx
  • 3,713
  • 5
  • 32
  • 38
  • 1
    Just an idea, but wouldn't it be much easier to implement a cron job calling a server-side script that takes care of querying the db and, if needed, call the web service? –  Apr 19 '19 at 06:10
  • 1
    You can't write anything _inside_ T-SQL to push data out to a web service. It has to be something external. Although you could use system com stored procedures, this is unreliable and unsupported – Nick.Mc Apr 19 '19 at 06:10
  • @ChrisBE - Yes that's one option, but I was exploring any option on the sql server side. I was just wondering if I can use triggers but still be able to call a web service. – userx Apr 19 '19 at 06:34
  • 1
    Why do this at all? SQL Server Agent already runs jobs periodically. The *database* can't push data to other services unless you use Service Broker either. A job's script can do so easily though – Panagiotis Kanavos Apr 19 '19 at 06:35
  • 2
    To put it another way, the database **shouldn't** call long running, blocking, external services like a web service. A scheduled job on the other hand can do so quite easily – Panagiotis Kanavos Apr 19 '19 at 06:36
  • 2
    How will the *database* throttle requests to that service to avoid flooding it? How will it handle error responses? How will it retry failed requests? All those things are trivial for a small console application or script. Doing so in SQL isn't possible – Panagiotis Kanavos Apr 19 '19 at 06:39
  • 2
    BTW I use SSIS, custom-made command line tools and scheduled jobs to poll million-row tables every 15 minutes and call airline web services for new data, store that into the database and combine all of those things together. Calling a web service HttpClient, implementing retries with Polly, creating a processing pipeline wit throttling using TPL Dataflow is easy. Making even a single HTTP call in T-SQL, even using SQLCLR would be too tricky, never mind retries, concurrent requests and throttling – Panagiotis Kanavos Apr 19 '19 at 06:43
  • @PanagiotisKanavos - Thanks for your comments – userx Apr 19 '19 at 06:55

1 Answers1

2

Here is a way to do what you are trying to achieve:

Step-1: How to run a stored procedure in sql server every hour?

Step-2: Write the business logic in the stored procedure to check the data

Step-3: Call/Post to the web service with CLR integration as discussed here

Or use the SQL Server Service Broker

It is not recommended to implement the way you are planning. Database should only focus on storing the data.

Instead you should have either a CRON service hitting your application on a regular interval. And then from the application query the database and post to the web service as required.

Or, have a scheduled service running within your application and your application can check the DB as above. Quartz is a library you can use for the purpose.

Rahatur
  • 3,147
  • 3
  • 33
  • 49