I have quite a large number of URLs held in a database table which are displayed on my website. Does anyone know of a simple way of testing them to see if they still  work?  it would save me a great deal of time. Ideally something I can do as a regular scheduled task. I hate displaying broken links on the website.

glenfitch:

I have quite a large number of URLs held in a database table which are displayed on my website. Does anyone know of a simple way of testing them to see if they still  work?  it would save me a great deal of time. Ideally something I can do as a regular scheduled task. I hate displaying broken links on the website.

If you're running SQL Server 2000, you could create a UDF that uses the object automation stored procedures (sp_OA*) to call the XMLHTTP APIs in order to validate the URL.  Here's some sample code for that:

declare @object int
declare @return int
declare @valid int
set @valid = 0

--create the XMLHTTP object
exec @return = sp_oacreate 'Microsoft.XMLHTTP', @object output
if @return = 0
begin
 --Open the connection
 exec @return = sp_oamethod @object, 'open("GET",
"http://www.microsoft.com/sql", false)'

 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

if @valid = 1
 print 'valid'
else
 print 'invalid'

IMPORTANT NOTE::: I wrote this code a long time ago, and since then found out that Microsoft.XMLHTTP is intended for clients; you should use the Server.XMLHTTP object on a server (I'm not sure of the exact differences between the two, but was told that the latter is tuned for serverside threading/memory models and that using the former on a server can be dangerous.)

If you're running SQL Server 2005, you can do the same thing using a CLR UDF.  Here's some sample code for that (you should turn the Console.WriteLine calls into returns):

            try
            {
                WebRequest wr = WebRequest.Create("http://www.sqlblog.com/");

                using (HttpWebResponse resp = (HttpWebResponse)wr.GetResponse())
                {
                    if (resp.StatusCode == HttpStatusCode.OK)
                        Console.WriteLine("valid");
                    else
                        Console.WriteLine("invalid");
                }
            }
            catch
            {
                Console.WriteLine("error");
            }

---

Looking for an advanced SQL Server 2005 book?

Expert SQL Server 2005 Development

Adam, that's beautiful. There seem to be a lot of different ways of doing this.
Here is an example that allows you to give a User ID and password for the site and the proxy, and uses the WinHTTP.WinHTTPRequest object. Of course, your server will need access to the web which is not normally advised for a production server, but that's life?

ALTER PROCEDURE dbo.spHTTPalive(
               @URL           VARCHAR(512),
               @WebLogin      VARCHAR(128)  = NULL,
               @WebPassword   VARCHAR(128)  = NULL,
               @ProxyLogin    VARCHAR(128)  = NULL,
               @ProxyPassword VARCHAR(128)  = NULL)
AS
/*******************************************************************
       Name : spHTTPAlive
     Server : SQLserver 2000
       Uses : WinHttp.WinHttpRequest.5.1
Description : does a simple GET to test the URL. Returns 0 if good
 Parameters :
       @URL : the url to use ( like http://www.simple-talk.com )
  @WebLogin : (optional) The Username for the webserver
@WebPassword: (optional) The password for the webserver
@ProxyLogin : (optional) The Username for the proxyserver
@ProxyPassword : (optional) The password for the proxyserver 
       e.g. : Declare @Ret int
              Exec @Ret=spHTTPAlive 'http://www.simple-talk.com'
              select @ret 
       Date : 2006-07-08
     Author : Phil Factor
    History : helped a lot by an example by Bert De Haes
*******************************************************************/

  SET nocount  ON
  DECLARE  @ObjectToken INT, 
-- the objecttoken for WinHttp.WinHttpRequest.5.1
           @ReturnCode  INT, -- the return code from sp_OA procedures
           @Source      VARCHAR(255), -- the source method of an error
           @Description VARCHAR(512), -- the desciption of an error
           @MethodName  VARCHAR(512), -- The Method paramter passed 
           @Status      INT 

  SET @ReturnCode = 0
                    -- create the 'WinHttp.WinHttpRequest.5.1' object
  SET @MethodName = 'WinHttp.WinHttpRequest.5.1'
  EXEC @ReturnCode = master.dbo.sp_oacreate  @MethodName ,
                                             @ObjectToken OUTPUT
  IF @ReturnCode <> 0
    GOTO error
    -- open the url on the server
  SET @MethodName = 'Open("GET" , "' + @URL + '")'
  EXEC @ReturnCode = master.dbo.sp_oamethod  @ObjectToken ,
                                             @MethodName
  IF @ReturnCode <> 0
    GOTO error
  IF @WebLogin IS NOT NULL 
     AND @WebPassword IS NOT NULL 
    BEGIN
    -- Set the Credentials for the Webserver
      SET @MethodName = 'SetCredentials("' + @WebLogin + '","' + @WebPassword + '",0)'
      EXEC @ReturnCode = master.dbo.sp_oamethod  @ObjectToken ,
                                                 @MethodName
      IF @ReturnCode <> 0
        GOTO error
    END
  IF @ProxyLogin IS NOT NULL 
     AND @ProxyPassword IS NOT NULL 
    BEGIN
    -- Set the Credentials for the Proxy
      SET @MethodName = 'SetCredentials("' + @ProxyLogin + '","' + @ProxyPassword + '",1)'
      EXEC @ReturnCode = master.dbo.sp_oamethod  @ObjectToken ,
                                                 @MethodName
      IF @ReturnCode <> 0
        GOTO error
    END
  SET @MethodName = 'Send'
  EXEC @ReturnCode = sp_oamethod  @ObjectToken ,
                                  @MethodName  
                                 -- Get the HTTP_Status
  IF @ReturnCode <> 0
    GOTO error
  SET @MethodName = 'Status'
  EXEC @ReturnCode = sp_oamethod  @ObjectToken ,
                                  @MethodName ,
                                  @status OUT
  IF @ReturnCode <> 0
    GOTO error
    -- if we get here the normal way, don't do error
  GOTO cleanup
  ERROR:
  IF @ObjectToken IS NOT NULL 
    BEGIN
      EXEC sp_oageterrorinfo  @ObjectToken ,
                              @Source OUT ,
                              @Description OUT
    END
  ELSE
    BEGIN
      SET @Source = '?'
      SET @Description = '?'
    END
  SELECT @Source + ', ' + @Description + ',  while doing the command ' + @MethodName
                                      -- Destroy created object(s)
  CLEANUP:
  IF @ObjectToken IS NOT NULL 
    BEGIN
      EXEC @ReturnCode = master.dbo.sp_oadestroy  @ObjectToken
      SET @ObjectToken = NULL
    END
  IF (@Status = 200)
    RETURN (0)
  ELSE
    RETURN (1)

Just for the sheer hell of it, here is an approach using XStandard's HTTP component. This has a few features that make it rather useful. XStandard also have a zip component which is dead handy for zipping up long reports. If you use VB or C#, you can see from this example which luckily does what Glen wants how to translate a VB use of an OLE object into a SQL Server version.

CREATE PROCEDURE spIsURLLive

@URL VARCHAR(8000)

/*******************************************************************

Name        : spIsURLLive

Server      : SQLserver 2000 

Uses       : XStandard.HTTP

Description : does a simple GET to test the URL. Returns 0 if good

Parameters  : @URL : the url to use ( like http://www.simple-talk.com )

e.g.       : Declare @Ret int

                     Exec @Ret=spIsURLLive 'http://www.simple-talk.com'

                     select @ret

Date        : 2006-07-09

Author      : Phil Factor

History     : 

*******************************************************************/



AS

  DECLARE

    @ourError   VARCHAR(255),

    @Returncode INT,

    @ObjectToken INT,

    @ReturnValue INT,

    @Source VARCHAR(100),

    @Description VARCHAR(255)



  SELECT @OurError = 'whilst opening the HTTP object'

  EXECUTE @Returncode = sp_oaCreate  'XStandard.HTTP' ,

                                     @ObjectToken OUTPUT

  IF @@ERROR |@ReturnCode <> 0

      OR @ObjectToken < 0

    GOTO ErrorHandler

  SELECT @OurError = 'while adding a request header'

  EXECUTE @Returncode = sp_oaMethod  @ObjectToken ,

       'AddRequestHeader' ,

               NULL,

       'User-Agent', 'Mozilla/4.0 (compatible; MyApp 1.0; Windows NT 5.1)'

IF @@ERROR |@ReturnCode <> 0 OR @ObjectToken < 0 GOTO ErrorHandler



SELECT @OurError = 'while getting the URL '''+@URL+''''

EXECUTE @Returncode = sp_oaMethod @ObjectToken, 'Get',NULL,@url

IF @@ERROR |@ReturnCode <> 0 OR @ReturnValue < 0 GOTO ErrorHandler

  

SELECT @OurError = 'while getting the response code'

EXECUTE @Returncode = sp_oaMethod  @ObjectToken , 'ResponseCode' ,@ReturnValue out

IF @@ERROR |@ReturnCode <> 0 GOTO ErrorHandler



EXECUTE sp_oaDestroy  @ObjectToken



IF  (@ReturnValue = 200) RETURN (0) ELSE RETURN (1)





ERRORHANDLER:

       IF @ObjectToken IS NOT NULL

               EXEC sp_OAGetErrorInfo @ObjectToken, @Source OUT, @Description OUT

       ELSE

               SELECT @Source = '?', @Description = '?'



  SELECT @OurError = 'We had the problem "'+@Description
     +'" with '+@Source+' '+@Ourerror

  RAISERROR ( @OurError , 16 , 1 )

  RETURN (-1)