Click here to monitor SSC

Testing links

Last post 08-09-2006, 5:08 AM by Phil Factor. 3 replies.
Sort Posts: Previous Next
  •  08-08-2006, 5:44 AM Post number 1564

    Testing links

    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.
  •  08-08-2006, 9:04 AM Post number 1567 in reply to post number 1564

    Re: Testing links

    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");
                }
  •  08-08-2006, 1:17 PM Post number 1573 in reply to post number 1564

    Re: Testing links

    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)


  •  08-09-2006, 5:08 AM Post number 1581 in reply to post number 1564

    Re: Testing links

    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 <> OR @ObjectToken GOTO ErrorHandler

    SELECT @OurError 'while getting the URL '''+@URL+''''
    EXECUTE @Returncode sp_oaMethod @ObjectToken'Get',NULL,@url
    IF @@ERROR |@ReturnCode <> OR @ReturnValue GOTO ErrorHandler
      
    SELECT @OurError 'while getting the response code'
    EXECUTE @Returncode sp_oaMethod  @ObjectToken 'ResponseCode' ,@ReturnValue out
    IF @@ERROR |@ReturnCode <> GOTO ErrorHandler

    EXECUTE sp_oaDestroy  @ObjectToken

    IF  (@ReturnValue 200RETURN (0ELSE 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 )
      
    RETURN (-1)
View as RSS news feed in XML