File this one in your folder of things you should probably never use -- but maybe, some day, in an emergency, you'll need this.
I see posts requesting this functionality all the time. "How do I
validate a URL in SQL Server?" Not just the string, but the URL itself
-- how can we find out if it's valid?
Thanks to the Object Automation extended stored procedures Microsoft includes with SQL Server, it's quite easy...
CREATE FUNCTION dbo.ValidateURL(@URL VARCHAR(300))
RETURNS BIT
AS
BEGIN
DECLARE @Object INT
DECLARE @Return TINYINT
DECLARE @Valid BIT SET @Valid = 0 --default to false
--create the XMLHTTP object
EXEC @Return = sp_oacreate 'MSXML2.ServerXMLHTTP.3.0', @Object OUTPUT
IF @Return = 0
BEGIN
DECLARE @Method VARCHAR(350)
--define setTimeouts method
--Resolve, Connect, Send, Receive
SET @Method = 'setTimeouts(45000, 45000, 45000, 45000)'
--set the timeouts
EXEC @Return = sp_oamethod @Object, @Method
IF @Return = 0
BEGIN
--define open method
SET @Method = 'open("GET", "' + @URL + '", false)'
--Open the connection
EXEC @Return = sp_oamethod @Object, @Method
END
IF @Return = 0
BEGIN
--SEND the request
EXEC @Return = sp_oamethod @Object, 'send()'
END
IF @Return = 0
BEGIN
DECLARE @Output INT
EXEC @Return = sp_oamethod @Object, 'status', @Output OUTPUT
IF @Output = 200
BEGIN
SET @Valid = 1
END
END
END
--destroy the object
EXEC sp_oadestroy @Object
RETURN (@Valid)
END
... And that is it ...
SELECT dbo.ValidateURL('http://www.microsoft.com/sql')
---
1
SELECT dbo.ValidateURL('http://www.XMLisNOTaMAGICbullet.com/')
---
0
Note, you don't want to run this thing against a big table. It runs
synchronously and waits for the remote site to respond. That can
definitely hold locks open a lot longer than you might want.
UPDATE: Thanks to Marcus Tucker
for pointing out that Microsoft.XMLHTTP (the XMLHTTP client object) was
not the right choice here. I've updated the UDF to use
MSXML2.ServerXMLHTTP.3.0, the XMLHTTP server object, instead.
ANOTHER UPDATE: Added a call to the setTimeouts method, as I
discovered that this wasn't behaving the same on differente servers --
apparently there is some default timeout set somewhere; I have no idea
where, though. Anyway, the four timeout types have all been set to 45
seconds (45000 ms). Tweak them if you need to.