web service call from SQL SERVER 2019

Question

Hi,

Currently we generate month statements (bank monthly statements) from asp.net application.
Background:

1. Data is in sql server tables
2. we use SSRS reports with parameters to run report and generate pdf files
3. Have batch file that calls SSRS report passing parameter values

What we did is
asp.net application calls stored procedure.
In stored procedure we call batch file by using xp_Cmdshell and passing parameter values.
We do this for each client\account in a loop.

Security team want to disable using xp_cmdshell due to security issues. So we are planning to re-archtect this.
We were thinking of moving all the logic into web service (like running SSRS reports and generating pdf statements) etc and call that webservice from SQL Server stored procefure.

What are the best options for this kind of requirement.
Can we make a call to webservice from stored procedure (not using CLR) in 2019 and get a response when work is done. It may take couple of hours to generate all statements.

Thank You

Answer 1

One alternative is to have a client program in the language of your choice that reads the data and the deals with the SSRS thing.

But if there is a lot of data to collect and you want to do that in a stored procedure to be close to the data for better efficiency, CLR is indeed the way to call a web service. In the system I work with, we use CLR for this purpose, and hardly nothing else.

Beware that there is some security red tape with the CLR as well. With CLR strict security in force (the default), all assemblies are considered unsafe. But this is not a big deal once you have it working.

It is also possible to call web services through COM and sp_OAxxxx, but please don't do that.

Answer 2

Hi @Spunny ，

All ways involve a certain amount of risk, safety is always relative, you can choose the way you like, just a few recommendations for common mrthods. like CLR

You also can look C#, create a web service client using a c# assembly and create a web reference to the web service you wish to call
https://kb.hyve.com/howtocallawebservicefromsqlserver/

SSIS is also an option, you can look this doc:

https://learn.microsoft.com/en-us/sql/integration-services/control-flow/web-service-task?view=sql-server-ver16

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

So if I understand your latest replies, the web server talks to SQL Server and nothing else, and therefor SQL Server needs to somehow talk to SSRS and all that.

Ideally, maybe you should re-architect your solution and introduce a middle tier between the web server and SQL Server. This middle tier can then start this statement-generating operation by spawning a subprocess, and with no need to go through SQL Server.

It goes without saying that this is quite a big operation, quite bigger than only rewriting the process to generate the monthly statements.

There is one more alternative, though. You could use Service Broker with external activation. That is, when the user clicks the button, you post a message on a Service Broker queue. Then you have program (probably running as a service) which polls this Service Broker queue and when there is a message, it starts working.

You could also have an Agent job for the task, and then the user clicks the button that starts the job.