How can I call SOAP API using web link in Stored Procedure of MS SQL server. kindly define in detail.
Thanks in Advance.
Without more detail on what you're trying to accomplish, we can't "define in detail" how to accomplish this.
In the olden days of SQL Server, you'd use OLE Automation via the sp_OA* stored procedures:
While still supported available, they are mostly obsolete and very clunky to use. Since SQL Server 2005, CLR language support was added to SQL Server, so you could use your favorite .Net language to compile a CLR function or stored procedure to call your web service.
Note that there are security and usability concerns with using CLR in SQL Server. For instance, you would not want to use a CLR function/procedure inside a SQL Server transaction, if the CLR calls an external service that may not respond. This could cause concurrency issues and is not recommended, either through CLR or OLE methods.
A better suggestion is to create an external program that calls your web service but can run as a command-line executable, or can be called via PowerShell, and then use a SQL Agent job to run that service. Your service would query SQL Server for data necessary to call the service, but would not maintain a transaction or create a process inside of the main SQL Server process, like a stored procedure would.
1 Like
The short answer you do not have an option without development:/
I have never loved using clr's as they always seem to cause issues since it is outside of the built in functions of sql. I normally would setup a layer that communicates with the web service you want to call. A clean way to do this is using something like AWS Lambda or another middleware that can handle the code to do web posts.