Check URL Status from SQL Server

Posted in: Technical Track

One of our clients has a public web page and they needed to ensure that it is always up and accessible. Pythian already has a monitoring stack that includes website and pages monitoring.

I wondered if this could be done from SQL Server using built-in Windows modules to access external web resources. There are various the methods we can use.

Object Automation extended stored procedures

Adam Machanic has a nice blog describing how to use Object Automation extended stored procedures. That code, however, was designed for SQL Server 2000; to use it in SQL Server 2005 and after, you will need to turn on OLE Automation using sp_configure.

Adam also recommended using CLR for SQL 2005 and later as it is safer, more stable, and more capable, which is completely true.


Another old-fashioned method is using a VBSCRIPT file, then calling it using XP_CMDSHELL and Windows Script Host (WSH).

The VBScript should work like this:

This is the VBS script:

' Parameter holding URL

url= WScript.Arguments.Item(0)

'url= ""

' Using MSXML 6.0 ; shipping with Win Vista , Win7, win 2008 and is installed with Win server 2003 Sp2
' Download it for older versions

Set HttpRequest = CreateObject("Msxml2.XMLHTTP.6.0")

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set oFSO = WScript.CreateObject("Scripting.FileSystemObject")
Set oShell = WScript.CreateObject("Wscript.Shell")

' Create text file beside the script file. Should have proper permissions.
' Don't use CurDir or Oshell.currentdirectory  global variables, they will refer to %windows%\system32

Set obj_File = objFSO.createTextFile(oFSO.GetParentFolderName(Wscript.ScriptFullName) ; "\check_url.txt", true )

' Make the connection "HEAD", Url , False

On Error Resume Next


If Err.Number <> 0 Then
' Error happend , write to file
obj_File.Write "Error : "
obj_File.Write Err.Number
obj_File.Write ": "
obj_File.Write Err.Description
'WScript.echo "Error : "
'WScript.echo Err.Number
'WScript.echo ": "
'WScript.echo Err.Description

' No Errors , write URL status to file
obj_File.Write "HTTP status: "
obj_File.Write HttpRequest.status
obj_File.Write " "
obj_File.Write HttpRequest.statusText
'WScript.echo "HTTP status: "
'WScript.echo HttpRequest.status
'WScript.echo " "
'WScript.echo HttpRequest.statusText
End If

Calling the code should be easy using Xp_cmdshell (which must be enabled) and CSscript


SET @url ‘’;

EXEC (‘Exec master..Xp_cmdshell  ”Cscript //B c:\app\check_url.vbs “‘+@url+‘””’) ;


EXEC master..xp_cmdshell ‘type c:\app\check_url.txt’


HTTP status: 200 OK


CLR: The more elegant, safer, newer, and more stable way.

By creating a CLR stored procedure that takes URL as a parameter, we are using the System.Net namespace as the basis here; it provides a simple programming interface for many of the protocols used on networks. I used the WebRequest and WebResponse classes to interact with the URL and return its status using HttpStatusCode. For FTP, I used FTPWebRequest and FtpWebResponse.

The URL can be HTTP, HTTPS, or FTP, and must begin with “%Protocol%://”. I wrote the CLR in C# using Microsoft Visual Studio 2008. The code is quite simple, so it can be easily written in Notepad, saved as .CS file, and compiled using C# compiler CSC.EXE.

Here is the code:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Web;
using System.Net;

public partial class check_url_status
    public static void check_url(string url)
        if (url.Trim().Substring(0, 6).ToLower() != "ftp://" &amp;amp;amp;amp;amp;amp; url.Trim().Substring(0, 7).ToLower() != "http://" &amp;amp;amp;amp;amp;amp; url.Trim().Substring(0, 8).ToLower() != "https://")
            SqlContext.Pipe.Send("URL must be a valid HTTP:// , HTTPS:// Or FTP:// URL ");

        if (url.Trim().Substring(0, 7).ToLower() == "http://" || url.Trim().Substring(0, 8).ToLower() == "https://")
                HttpWebRequest req = (HttpWebRequest)WebRequest.Create(url.Trim());

                using (HttpWebResponse rsp = (HttpWebResponse)req.GetResponse())

            catch (WebException e)
                SqlContext.Pipe.Send(e.ToString().Substring(0, e.ToString().IndexOf(" at ")));

        else if (url.Trim().Substring(0, 6).ToLower() == "ftp://")
                FtpWebRequest req = (FtpWebRequest)WebRequest.Create(url.Trim());

                using (FtpWebResponse rsp = (FtpWebResponse)req.GetResponse())

            catch (WebException e)
                SqlContext.Pipe.Send(e.ToString().Substring(0, e.ToString().IndexOf(" at ")));


There are few notes regarding the CLR.

The CLR permission level is defined as “External” as it accesses external resources (Web resources). This is set from project properties > Database tab.

The assembly should be signed for the purpose of later creating the assembly in SQL Server.

To create an EXTERNAL_ACCESS or UNSAFE assembly in SQL Server, or to load an assembly, one of two conditions must be met as mentioned in referenced link.

You can sign the assembly from project properties > signing tab

Sign Assembly

After creating the DLL comes creating SQL stored procedure based on the assembly:

  1. Enable CLR
    EXEC master..sp_configure 'clr enabled'1



  2. Adhering to the requirement of creating an EXTERNAL_ACCESS assembly, we will create an ASYMMETRIC KEY and a corresponding login with EXTERNAL ACCESS ASSEMBLY permission.USE master


    CREATE ASYMMETRIC KEY check_url FROM EXECUTABLE FILE = 'c:\app\check_url.dll'




  3. Create the assembly with External_access:CREATE assembly Check_url FROM 'c:\app\check_url.dll'



  4. Create the stored procedure:CREATE PROCEDURE Check_url (@url NVARCHAR(MAX))

    AS external name Check_url.check_url_status.check_url ;


    Note that the URL parameter is STRING in C# code and should be either Nvarchar or nchar in SQL Server. This is a table of mapping CLR parameter data.

  5. Test the code:EXEC Check_url @url 'HTTP://WWW.Pythian.COM'




    • For FTP, there must be a destination file and not only a folder or root site (example : “”). This is how FTPWebRequest works, but I’m not sure why.
    • Sometimes links are redirected. The maximum number of redirects that the request follows can be controlled by MaximumAutomaticRedirections property.

Interested in working with Mohammed? Schedule a tech call.

4 Comments. Leave new

Hi Mohammed,

Nice post. One modification to make the CLR version a bit more elegant would be to do the URL “well-formedness” validation via a regular expression, rather than using String.Trim(). The one at this site looks promising:


Thanks Adam.

That’s indeed looks a neat Regx ; my c# skills isn’t a top-notch yet :)



One update

To return the URL status as tabular data , w can use this code :

using(SqlConnection connection = new SqlConnection(“context connection=true”))
SqlCommand command = new SqlCommand(“select ‘” + rsp.StatusCode.ToString().Trim()+ “‘”, connection);



This should help in trapping the status , and examining it. A temp table / table variable can be used for that


SQL server : Convert BBcode to HTML | The Pythian Blog
October 19, 2011 7:01 am

[…] have a blog where I had to sign the assembly because it needed access to external resources (Web resources) , we don't need that here. More info […]


Leave a Reply

Your email address will not be published. Required fields are marked *