Way to access a REST Service via a SQL Server stor

Question

We are looking for a way to call a REST service from a SQL Server 2008 & newer stored procedure. We do not need to get any information from this service. We just need to force the call to run in the middle of a stored procedure.

I have found some 3rd party options or CLR; but I refuse to think there isn't an easier way since we just need to fire a call and don't need anything in return.

Hopefully someone can shed some light. Thanks!

Answer 1

You could make a SQL Agent job to run a PowerShell script, then run the job via msdb.dbo.sp_start_job. That way you wouldn't need any external scripts or utilites, nor any SQL-CLR.

Your job step would be something like:

$request = [System.Net.WebRequest]::Create('http://stackoverflow.com/')
$result = $request.GetResponse()

Answer 2

I can think at least of 2 ways of doing that:

1) Probably prefered way:

Make a C# dll and call its function from the store procedure example: How to call C# function in stored procedure

2) Call the rest service with the help of CURL utility.

DECLARE @PassedVariable VARCHAR(100)
DECLARE @CMDSQL VARCHAR(1000)
SET @PassedVariable = 'SqlAuthority.com'
SET @CMDSQL = 'c:findword.bat' + @PassedVariable
EXEC master..xp_CMDShell @CMDSQL

have the call to the ws inside the bat file