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!
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()
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
