title: Invoke WCF service from SQL without CLR
body: <p>Is it possible to invoke a WCF service from SQL without using a CLR? (like with asmx).</p>
  
  <p>The following code works perfectly with an asmx service, but not with WCF</p>
  
  <pre><code>    --INTERNAL PARAMETERS
      DECLARE @Object AS INT
      DECLARE @ResponseText AS VARCHAR(8000)
      DECLARE @SiteURL AS VARCHAR(100) = fGetSiteUrl()
  
      --BUILD SERVICE URL
      DECLARE @ServiceURL VARCHAR(MAX) = @SiteURL + '/WebServices/PortalService.asmx/CreateUser'
                                          + '?userName=' + @UserName
                                          + '&amp;password=' + @ClearTextPassword
                                          + '&amp;question=' + @PasswordQuestion
                                          + '&amp;answer=' + @PasswordAnswer
                                          + '&amp;email=' + @Email
  
      --BUILD AND EXECUTE SERVICE CALL
      EXEC sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
      EXEC sp_OAMethod @Object, 'open', NULL, 'get',@ServiceURL, 'false'
      EXEC sp_OAMethod @Object, 'send'
      EXEC sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
      EXEC sp_OADestroy @Object
  
      --STRIP OUT USELESS FLUFF
      SET @ResponseText = SUBSTRING(@ResponseText, CHARINDEX('&gt;', @ResponseText) + 1, (LEN(@ResponseText) - CHARINDEX('&gt;', @ResponseText)))
      SET @ResponseText = SUBSTRING(@ResponseText, CHARINDEX('&gt;', @ResponseText) + 1, (LEN(@ResponseText) - CHARINDEX('&gt;', @ResponseText)))
      SET @ResponseText = CONCAT('&lt;Result&gt;', @ResponseText)
  
      DECLARE @MyXML XML  = @ResponseText
  
      --GET THE USER USERID OF THE NEW USER
      SET @UserId =   (SELECT     a.b.value('UserId[1]','nvarchar(1280)') AS UserId
                      FROM        @MyXML.nodes('Result') a(b))
  </code></pre>