Solving Erratic Behavior with HttpWebrequest in SQL 2008 CLR UDF

Question

We're currently attempting to implement a sql server 2008 udf to do expansion of shortened urls. We have it working quite well against most of the major url shortening services. However, at seemingly random times it will "hang" and refuse to work against a certain domain (for example bit.ly) while subsequent calls to other services (for example tinyurl.com) will continue to succeed.

We initially thought this was due to some sort of blocking by the url shortening provider, but stopping and restarting the dbserver service cause subsequent requests to succeed. Could it be that SQL server is somehow pooling outgoing http connections in some way?

Here's the code...

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

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString UrlExpander(string url)
    {
            // Set up the Webrequest
            HttpWebRequest wr = (HttpWebRequest)HttpWebRequest.Create(url);
            try
            {

            // Set autoredirect off so the redirected URL will not be loaded
            wr.AllowAutoRedirect = false;

            // Get the response
            HttpWebResponse wresp = (HttpWebResponse)wr.GetResponse();

            return new SqlString(wresp.Headers["Location"].ToString());
        }
        catch (Exception ex)
        {
            wr.Abort();
            throw ex;

        }


    }
};

Answer 1

You're missing wresp.Close().

Answer 2

Given the feedback from Jesse and our desire to have a function that returns either a properly expanded url or NULL, we have come up with the following which seems to process 1000s of minified URL with no further issues:

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString UrlExpander(string url)
{
    // Set up the Webrequest
    try
    {
        HttpWebRequest wr = (HttpWebRequest)HttpWebRequest.Create(url);
        try
        {
            // Set autoredirect off so the redirected URL will not be loaded
            wr.AllowAutoRedirect = false;

            // Get the response
            HttpWebResponse wresp = (HttpWebResponse)wr.GetResponse();
            wresp.Close();

            if (wresp != null)
                return new SqlString(wresp.Headers["Location"].ToString());
        }
        finally
        {
            if (wr != null)
                wr.Abort();
        }
    }
    catch
    {
    }

    return null;

}