How to call a webmethod from SQL Server 2008 R2 - endpoint?

What would be the best approach to call a web service method from SQL Server 2008 R2 without a CLR assembly ?

On second thought, believe I can use an endpoi开发者_JS百科nt..

How do I define one and access via sp

Don't. Use CLR, or perhaps use SSIS to coordinate web service and database calls.

SQL is a data manipulation language and has no real functionality for calling externally

You may be able to use sp_OA% or xp_cmdshell but you'll still need an executable for these to use. I don't know how feasible or practical or whether anyone has tried.

You can try this

Declare @Object as Int;
Declare @ResponseText as Varchar(8000);

Code Snippet
Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get',
                                                              'URL, --Your Web Service Url (invoked)
                                                              'false'
Exec sp_OAMethod @Object, 'send'
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT

Select @ResponseText

Exec sp_OADestroy @Object

Note: It might be possible this feature is either off or not available in your sql server. you have to configure in your security configuration in case it is blocked for security reason.