PeterKellner.net

So, this is not a big tip, but worth at least 10 minutes to figure it out on your own.  Here is to saving you 10 minutes:

SELECT LEFT (userlastname, 1) as alpha,
       count(id)
FROM attendees
WHERE id IN (
              SELECT attendeesid
              FROM AttendeesCodeCampYear
              WHERE codecampyearid = 6
      )
GROUP BY  LEFT (userlastname, 1)O
ORDER BY LEFT (userlastname, 1)

It’s pretty self explanatory.  Our case is we have two tables that we track code camp attendees.  One is the master list, and the other is a detail by year.  (6 is this year)

Seems like this comes up a lot so I thought I’d blog about a clean way I found to do this from a StackOverflow article.  Basically, the problem is if you have a bunch of Sql records that have date and times in them (not 00:00:00 for time), using Microsoft’s SqlServer.  You may want to query all records that are on a certain date.  You can certainly for a query that looks like:

SELECT * FROM VolunteerJobs 
   WHERE JobStartTime >='08-01-2008' AND 
         JobStartTime<='08-14-2008 23:59:59.996'

However, this feels a little awkward.  A better way that I plan on using is:

SELECT *
FROM VolunteerJob
WHERE DateAdd
   (day, datediff(day, 0, JobStartTime), 0) =
       '2010-10-09'

Somehow, it just feels better.  HTH’s.

I have heard all the hype about how great Sql Server CE 4.0 and that it is now standard with Visual Studio 2010 SP1.

I’ve got a small project (1 table) that I’d like to include in an asp.net website project so I decided to give it a try.  For the most part, things have gone smoothly, however I did have a couple hiccups I’d like to mention.  One cost me $9 because I was doing this implementation on an airplane and immediately, I had a failed connection with a confusing error that I needed Bing to lookup  (more details below).

I often forget that the simplest way (IMHO) to connect to a Microsoft SqlServer 2008 database is to use the web.config connection for making a Trusted connection.  Basically, it keeps you from having to put a username and password in the web.config and also from having to keep track of different username and passwords on different systems (like where you deploy to for example).  The simplest connection string I can think of looks like this:

<add name="MyConnName" connectionString="Server=.;Database=mydbcatalogname;Trusted_Connection=True;" 
    providerName="System.Data.SqlClient" />

Notice that I’m using the “.” as the server name.  This allows me to reference the local system where my sqlserver lives. Not necessarily a best practice, but often is the case.

Upside

One big benefit is if you fall victim to a disk scraping attack, or someone gets a hold of your source (maybe from version control?), you don’t give up your passwords.

Downside

If you SqlSever is not on the system system as your web project or application, then this become more tricky because of the cross system authentication issues.  If you have active directory installed on both systems, then this works also.

In this post, we will show and explain a small TSQL Sql Server 2008 procedure that deletes all rows in a table that are older than some specified date.  That is, say the table has 10,000,000 rows in it the accumulated over the past 2 years. 

Say you want to delete all but the last 30 days of activity.  If you just simply say DELETE FROM table WHERE id>10000, you will cause this to happen in one transaction and likely, you will get an error.  That’s the best case.  The worst case is your system tries to do this, eventually consumes all the resources in your computer and crashes your server.

In this post, we’ll go briefly the process of how you would update all rows in a SQL Server 2008 table such that a particular date column will be moved back 1 hour in time.  This is actually pretty simple, but being that I typically do my work in the ORM layer (that is LINQ2SQL or Entity Framework), I just don’t do much of this.  The process I’m going to follow is first to use Microsoft SQL Server Management Studio to make sure I know what I’m doing with the Sql Server 2008 functions, then add it to an UPDATE statement.

For the past couple years, I’ve used a product called FinalBuilder from VSoft Technologies to automate my build processes.  Since I’ve been building web sites (about the past 5 years or so), one of the biggest hassles is maintaining them.  That is, updating the databases, deploying the web site, automating backups, etc.  I’ve used several products like FinalBuilder (including Cruise Control), and to be honest, none of them come close to the quality of FinalBuilder.

Basically, the way the product works is that you create a “project” file using the FinalBuilder IDE.  That project file has “Actions” in it which do things like “ftp” files to servers, rename files, iterate over sql scripts, parse and update files, checkout from source control, as well as hundreds of other convenient functions.  You then execute that “script” file and all the magic happens.

Many of you know of ORCSWeb either by reputation, or by way of Scott Forsyth, one of my ASP.NET MVP brothers.  In case you don’t, they are a managed hosting solutions company specializing in Microsoft technologies.  I’ve used their basic services for quite a while and have always been very happy.  It has always seemed that anytime I’ve called them (and it always seems like the middle of the night) one of their tech support staff is always available to help me, and go the extra mile if necessary.

The company I’m now working at is small and we don’t have a lot of resources to maintain hardware and do operating system type support.  We do have a high load requirement so we need a very robust supported solution.  Before this, I’d always been in the under $50 per month type plan with ORCSWeb, but I decided I needed more servers and a higher level of support.  I really did not know what level of support to expect when signing up for the managed servers but decided to go for it anyway.

All  can say is WOW!!!  I am over the top impressed.

We’ve been looking to find the best Cloud based host to put our soon to be virally growing web site up on.  Our requirements are it must run IIS7 and use Sql Server 2008 as it’s database. We have lots of wants (like Service Broker, Replication, etc.) but primarily we want the web tier to scale easily.  After doing research and testing, we got frustrated with our options at Amazon’s EC2.  We emailed their support (with our paid support contract) and were basically told we should go do our own Windows research to find out what works best.

At any rate, I emailed Steve Evan’s who is a frequent speaker at conferences and also an expert consultant on all things Windows and IT related and asked for some advice.  He told me that he had been asked that question often recently by others so he would do some research and get back to me.  He did one step better.  He wrote a blog post describing the ups and downs of the different cloud providers including Amazon’s EC2.  I respect Steve’s opinion a lot and rather than try and paraphrase it, I’ll just link to his post.

Here it is:  http://serktools.com/2009/04/20/load-balancing-iis-web-farm-on-amazon-ec2/

Good luck and feel free to post comments here if you’ve got opinions on Cloud Hosting of IIS and Sql Server 2008.