adam machanic now blogs at http://sqlblog.com

Validate a URL from SQL Server

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.