ROW_NUMBER()

Scott Klueppel — Sun, 07 Sep 2008 03:33:25 GMT

I really need to read up on new features when a major release comes out. Just a few weeks ago I learned of a great "new" SQL 2005 function... ROW_NUMBER(). Just in time since SQL 2008 is already out.

For me, this function means a lot less temp tables. I would typically create a temp table with an ID INT IDENTITY(1,1) column to create an DisplayOrder, BatchID, etc. used to group or join on later. Books Online describes the function as "Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition." The syntax is simple, and looks like:

ROW_NUMBER() OVER (ORDER BY ID DESC)

For this example, the data I want to bring back with a DisplayOrder column looks like:

Without ROW_NUMBER(), using a table variable with an identity column:

DECLARE @Subs TABLE (DisplayOrder INT IDENTITY(1,1), [Address] VARCHAR(100), Operation VARCHAR(50), [Contract] VARCHAR(50))

INSERT INTO @Subs ([Address], Operation, [Contract])
SELECT [Address]
, Operation
, [Contract]
FROM PersistentSubscribers
WHERE Operation = 'OnEvent2'
ORDER BY ID DESC

SELECT * FROM @Subs

With ROW_NUMBER(), look how beautiful:

SELECT DisplayOrder = ROW_NUMBER() OVER (ORDER BY ID DESC)
    , [Address]
    , Operation
    , [Contract]
FROM PersistentSubscribers
WHERE Operation = 'OnEvent2'

The results from both methods looks like:

Error Log RSS Feed

Scott Klueppel — Sun, 31 Aug 2008 08:37:38 GMT

Why clutter your inbox with error messages? Why make special code provisions for users to receive error messages via email? Why not log your error messages and have users subscribe to receive them in their favorite RSS aggregator?

If you are logging your exceptions already, you may find it easier to provide a syndication service. The process is ridiculously simple, and starts by creating a new project using the "Syndication Service Library" template. This template creates everything for you. All you need to do now is fill the SyndicationFeed with SyndicationItem objects.

Add a new class file called Feeds.cs:

    1 using System;

    2 using System.Linq;

    3 using System.ServiceModel;

    4 using System.ServiceModel.Syndication;

    5 using System.ServiceModel.Web;

    6 

    7 namespace SyndicationService

    8 {

    9    
[ServiceContract]

   10    
[ServiceKnownType(typeof(Atom10FeedFormatter))]

   11    
[ServiceKnownType(typeof(Rss20FeedFormatter))]

   12     public interface IFeeds

   13    
{

   14        
[OperationContract]

   15        
[WebGet(UriTemplate = "{type}?env={env}&app={app}", BodyStyle = WebMessageBodyStyle.Bare)]

   16         SyndicationFeedFormatter CreateFeed(string type, string env, string app);

   17    
}

   18 

   19     public class Feeds : IFeeds

   20    
{

   21         public SyndicationFeedFormatter CreateFeed(string type, string env, string app)

   22        
{

   23             SyndicationFeed feed = CreateSyndicationFeed(type, env, app);

   24 

   25             //
Return ATOM or RSS based on query string

   26             //
rss -> http://localhost:8000/Feeds/Errors?env=Production&app=MyAppName

   27             //
atom -> http://localhost:8000/Feeds/Errors?env=Production&app=MyAppName&format=atom

   28             string query = WebOperationContext.Current.IncomingRequest.UriTemplateMatch.QueryParameters["format"];

   29             SyndicationFeedFormatter formatter = null;

   30             if (query == "atom")

   31            
{

   32                 formatter = new Atom10FeedFormatter(feed);

   33            
}

   34             else

   35            
{

   36                 formatter = new Rss20FeedFormatter(feed);

   37            
}

   38 

   39             return formatter;

   40        
}

   41 

   42         private static SyndicationFeed CreateSyndicationFeed(string type, string env, string app)

   43        
{

   44             SyndicationFeed feed;

   45             switch (type.ToLower())

   46            
{

   47                 case "errors":

   48                     feed = CreateErrorsFeed(type, env, app);

   49                     break;

   50                 default:

   51                     feed = new SyndicationFeed(

   52                         String.Format("Feed
is unavailable - Type: {0} / Environment: {1} / Application: {2}",

   53                         type, env, app), null, null);

   54                     break;

   55            
}

   56             return feed;

   57        
}

   58 

   59         private static SyndicationFeed CreateErrorsFeed(string type, string env, string app)

   60        
{

   61             ApplicationLogDataContext db = new ApplicationLogDataContext();

   62 

   63             SyndicationFeed feed = new SyndicationFeed

   64            
{

   65                 Title = new TextSyndicationContent(String.Format("{0}
{1} {2}", env, app, type)),

   66                 Description = new TextSyndicationContent(

   67                     String.Format("Application
error syndication for the {0} applicaiton ({1}).", app, env)),

   68                 Items = from e in db.Exceptions

   69                         where e.Environment == env && e.Application == app

   70                         select new SyndicationItem

   71                        
{

   72                             Title = new TextSyndicationContent(e.Message),

   73                             Content = new TextSyndicationContent(e.StackTrace)

   74                        
}

   75            
};

   76             return feed;

   77        
}

   78    
}

   79 }

Modify the App.config file:

    1 <?xml version="1.0" encoding="utf-8"
?>

    2 <configuration>

    3    
<configSections>

    4    
</configSections>

    5    
<connectionStrings>

    6        
<add name="SyndicationService.Properties.Settings.ApplicationLogConnectionString"

    7             connectionString="Data
Source=Scorpion;Initial Catalog=ApplicationLog;Integrated Security=True"

    8             providerName="System.Data.SqlClient"
/>

    9    
</connectionStrings>

   10    
<system.serviceModel>

   11        
<services>

   12            
<service name="SyndicationService.Feeds">

   13                
<host>

   14                    
<baseAddresses>

   15                        
<add baseAddress="http://localhost:8000/"
/>

   16                    
</baseAddresses>

   17                
</host>

   18                
<endpoint contract="SyndicationService.IFeeds"

   19                           address="Feeds"

   20                           binding="webHttpBinding"

   21                           behaviorConfiguration="WebHttpBinding_Common"/>

   22            
</service>

   23        
</services>

   24        
<behaviors>

   25            
<endpointBehaviors>

   26                
<behavior name="WebHttpBinding_Common">

   27                    
<webHttp/>

   28                
</behavior>

   29            
</endpointBehaviors>

   30        
</behaviors>

   31    
</system.serviceModel>

   32 </configuration>

You will need to adjust your project's Debug options to have command arguments that look similar to the following to F5-debug your service.

"/client:iexplore.exe" "/clientArgs:http://localhost:8000/Feeds/Errors?env=Production&app=GeoTracker"

Press F5 to test it out.

Here is the IE7 RSS viewer:

Here is your RSS aggregator viewing the same feed:

You will, of course, want to add some additional information to the content of your SyndidationItem, a bogus phrase works for this example.

Also, it is unusual that you would care to keep your exception details around for a long period of time. Since this is a syndicated feed of application errors, you should make special arrangements to archive or delete your exception log on a regular basis. This will not only keep your insert and select times low, but will also alleviate the burden placed on a new subscriber when all of the exceptions from the database appear at once. An alternative would also be to modify the LINQ in the code above to only bring back exceptions from the last 7-60 days depending on your counts. I already archive my exceptions to a master exception repository for all environments by way of an ETL job. This way I can report on my errors without disturbing the live environments too.

Scott Klueppel's Blog

ROW_NUMBER()

Error Log RSS Feed