Check URL Status from SQL Server

Nov 25, 2009 / By Mohammed Mawla

Tags: ,

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.

VBSCRIPT

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= "http://www.pythian.com/mawla"

' Using MSXML 6.0 ; shipping with Win Vista , Win7, win 2008 and is installed with Win server 2003 Sp2
' Download it for older versions
' http://www.microsoft.com/downloads/details.aspx?FamilyID=993C0BCF-3BCF-4009-BE21-27E85E1857B1;displaylang=en

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

HttpRequest.open "HEAD", Url , False

On Error Resume Next

HttpRequest.send

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

Else
' 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

DECLARE @url NVARCHAR(2000)

SET @url ‘http://www.pythian.com/’;

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

GO

EXEC master..xp_cmdshell ‘type c:appcheck_url.txt’

GO

HTTP status: 200 OK

CLR

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
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    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 ");
            return;
        }

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

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

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

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

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

                    SqlContext.Pipe.Send(rsp.StatusCode.ToString());
                }
            }
            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

    RECONFIGURE

    GO

  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

    GO

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

    GO

    CREATE LOGIN check_url FROM ASYMMETRIC KEY check_url GRANT EXTERNAL ACCESS ASSEMBLY TO check_url

    GO

  3. Create the assembly with External_access:CREATE assembly Check_url FROM 'c:appcheck_url.dll'

    WITH PERMISSION_SET=EXTERNAL_ACCESS

    GO

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

    AS external name Check_url.check_url_status.check_url ;

    GO

    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'

    GO

    OK

    NOTE:

    • For FTP, there must be a destination file and not only a folder or root site (example : “ftp://ftp.gnu.org/README”). 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.

4 Responses to “Check URL Status from SQL Server”

  • 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:

    http://snippets.dzone.com/posts/show/452

  • Mohammed Mawla says:

    Thanks Adam.

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

    Regards

  • Mohammed Mawla says:

    One update
    =============

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

    using(SqlConnection connection = new SqlConnection(“context connection=true”))
    {
    connection.Open();
    SqlCommand command = new SqlCommand(“select ‘” + rsp.StatusCode.ToString().Trim()+ “‘”, connection);
    SqlContext.Pipe.ExecuteAndSend(command);
    }

    //SqlContext.Pipe.Send(rsp.StatusCode.ToString());

    ——————————————

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

    HTH

  • [...] 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

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>