How to get a resultant URL using tsql

• dmusson17

  Ten Centuries

  Points: 1235

  More actions

  September 26, 2016 at 1:13 pm

  #317734

  I was able to use Adam Machanic's logic to Validate a URL from SQL Server, now I am trying to retrieve a resulting or forwarded URL where applicable using sp_OAGetProperty responseText, but am having trouble figuring out how to do it or if it's even do-able using tsql. Any advice/direction is appreciated.

  Thanks,

  D

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  September 26, 2016 at 2:39 pm

  #1903218

  Quick suggestion, use WinHttp.WinHttpRequest instead.

  😎

  Here is an example previously posted on this thread

  CREATE function [dbo].[GetHttp]

  (

  @url varchar(8000)

  )

  returns varchar(8000)

  WITH EXECUTE AS OWNER

  as

  BEGIN

  DECLARE @win int

  DECLARE @hr int

  DECLARE @text varchar(8000)

  EXEC @hr=sp_OACreate 'WinHttp.WinHttpRequest.5.1',@win OUT

  IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

  EXEC @hr=sp_OAMethod @win, 'Open',NULL,'GET',@url,'false'

  IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

  EXEC @hr=sp_OAMethod @win,'Send'

  IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

  EXEC @hr=sp_OAGetProperty @win,'ResponseText',@text OUTPUT

  IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

  EXEC @hr=sp_OADestroy @win

  IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

  RETURN @text

  END