The easiest way I've found involves two steps:

Adjust SSMS Settings

1. Go to Tools->Options
2. Query Results->SQL Server->Results to Grid
3. Check "Include column headers when copying or saving results"
4. Click OK.
5. Note that the new settings won't affect any existing Query tabs -- you'll need to open new ones and/or restart SSMS.

Now next time you run a query, do this

1. Make sure the results are displayed in a grid (CTRL+D or Query->Results To->Results to Grid)
2. Right click in the grid, and click Select All
3. Right click in the grid again & click Copy
4. Open up a new Excel spreadsheet, and paste the data in
5. Do a global search & replace, replacing "NULL" with an empty string.

Voila!

I had tried before with SSMS's export to CSV feature, and it just didn't escape data the way I needed it to.

So you can probably tell that I've been doing a lot of LINQ lately. :) One thing I've found is that it's easier for me to write complex queries in the database as stored procedures or views, and then use Linq to SQL to retrieve the results, sort, filter, do paging, etc.

The downside of that approach is the results normally come back as a <view or proc name>Result type, instead of the actual table type. That's sometimes an issue, because I use partial classes to give my table classes some extra functionality, and I might want to allow updates, etc. against various tables.

So the goal is to be able to call a view or stored procedure, but coerce those results into a good ol' MyTable object.

You may already know how you can drag a stored procedure or view from your database (in Server Explorer), and drop it on top of a Table entity in the O/R designer. That will cause the stored procedure to return results of that table's type, instead of <procname>Result.

But Rick Strahl had a great post from a while back explaining how you can use views, procs, & dynamic SQL, and cast those results to a specific table type using ExecuteQuery. A snippet from Rick's post is below:

What's also interesting is that when you provide LINQ a query like this it still works with the DataContext's change tracking. For example, the following code actually works as you'd expect:

            IEnumerable<Customer>
custList = context11.ExecuteQuery<Customer>("select
* from Customers where CustomerId={0}", "ALFKI");
Customer cust11 = custList.Single<Customer>();
Response.Write(cust11.CompanyName); cust11.CompanyName ="Alfreds
Futterkiste "+ DateTime.Now.ToString();
context11.SubmitChanges(); custList = context11.ExecuteQuery<Customer>("select
* from Customers where CustomerId={0}", "ALFKI");
cust11 = custList.Single<Customer>();
Response.Write(cust11.CompanyName);
          

The cool thing (as Rick mentions) is that you don't have to do a "select * from Customers" -- you could do a "select CompanyName, ContactName from Customers" and it will work, too. You don't need to bring back all columns in order to successfully cast the result to a Customer object.

I'll post my continuing forays into Linq as I continue. One thing that remains to be seen is whether Linq to SQL is robust enough to be helpful vs requiring me to extend it too much to do what I want/need.

Yeah so this isn't brand new. But the jQuery Cheat Sheet is for version 1.2, at least. Visual jQuery (which I still use a lot) is only for jQuery 1.1. I sure hope someone doesn't come out with a Visual jQuery clone for jQuery 1.2.X ... :)

I wanted to put up a few examples of SQL vs Linq to SQL for my future reference, since we're using it in one of our social media projects for artists. I'm pretty handy at SQL, but it doesn't translate exactly to Linq to SQL.

Example 1

Assume you have a view called vev_bws_mediaVoteDownloadHistory that votes and downloads for different media by date. So it contains columns like mediaId, artistId, activityDate, votes, and downloads.

If we wanted to query this view to get the total number of votes and downloads for a given media, you could use SQL like this

            
              SELECT     votes
= SUM(votes),
    downloads = SUM(downloads)

            
              FROM vev_bws_mediaVoteDownloadHistory WHERE mediaId
= 12345
          

            veDataContext
dc =new veDataContext();
var totals = (from
v in dc.vev_bws_mediaVoteDownloadHistories
where v.mediaId.Equals(12345) group v by v.mediaId into h select new {
votes = h.Sum(x
=> x.votes), downloads = h.Sum(x
=> x.downloads) }).FirstOrDefault();
          

            if
(totals == null)
then return; // no totals Response.Write(totals.downloads); Response.Write(totals.votes); 
            
            
          

Example 2

So the first example was jsut getting a few simple sums. Here's a bit more complex example.

Assume you have a view called vev_bws_userMediaHistory that logs votes for a given artist and his/her media. The view fields like votes, artistName, mediaName, userId (the user who cast the vote), and voteDate (a datetime column storing the date & time of a vote).

So since voteDate contains dates and times, I want to display all the votes grouped by media, artist, and date (not time). I could use SQL like this:

            
              SELECT     votesperday
= SUM(votes),
    date = CAST(FLOOR(CAST(voteDate asFLOAT)) ASDATETIME),
    mediaName,     artistName FROM vev_bws_userMediaHistory WHERE userId=1 GROUPBY     CAST(FLOOR(CAST(voteDate asFLOAT)) ASDATETIME),
    mediaName,     artistName ORDERBYCAST(FLOOR(CAST(voteDate asFLOAT)) ASDATETIME) DESC
          

            veDataContext
dc =new veDataContext();
var votes = (from
v in dc.vev_bws_userMediaHistories
where v.userId.Equals(1) group v by new {
v.voteDate.Date, v.artistName, v.mediaName } into h orderby h.Key.Date descending
select new {
date = h.Key.Date,
artistName = h.Key.artistName,
mediaName = h.Key.mediaName,
votesperday = h.Sum(x
=> x.votes) }); 
          

            select new {
    h.Key,     votesperday = h.Sum(x
=> x.votes) }
          

FYI, there's a bug in the RTM LinqDataSource where child tables aren't loaded unless you have updating or deleting enabled. Apparently if you have a LinqDataSource that doesn't have updates or deleted enabled, ObjectTracking is turned off (for performance reason), but deferred queries (e.g. queries to pull back child rows) aren't executed.

So statements like

          <%#
Eval("ChildTable.ChildTableField")
%>
        

in your ListView, etc. won't work.

Annoying, needless to say. But at least now I know.

So, there are a few options:

1. Set EnableUpdate="true", which will enable ObjectTracking and cause deferred updates to work
2. Add a SELECT statement in your LinqDataSource (e.g. SELECT="new (field1, field2, childTable)") to pull back everything you need.
3. Handle the ContextCreated event to either manually set ObjectTrackingEnabled, or manually set LoadOptions.

          
            public
            void OnContextCreated(object sender,
LinqDataSourceContextEventArgs e) { ((DataContext)e.ObjectInstance).ObjectTrackingEnabled =true;
} OR publicvoid OnContextCreated(object sender,
LinqDataSourceContextEventArgs e) { var dataLoadOptions =new DataLoadOptions();
dataLoadOptions.LoadWith<MyTable>(t => t.ChildTable); ((DataContext)e.ObjectInstance).LoadOptions = dataLoadOptions;
} 
        

Setting LoadOptions is normally the best approach for performance.

I noticed an interesting post from Allison Beckwith about Project Planning Grids. She also provided links to Todd Warfel's Task Analysis Grid and Blink Interactive's Objects & Actions Analysis. All three posts centered around different grid styles & focuses, but they all discussed ways to map out the different objects/features of a project in an easy-to-understand grid, so that you can flesh out requirements, standardize vocabulary, and understand which items need to be developed when.

Allison & Todd's grids felt higher-level, and included color & position to denote schedule & priority. Blink Interactive's grid was more detailed & was perhaps a better way to ensure you didn't miss a requirement. I could perhaps see starting with Blink's grid to round out your featureset, and then a grid like Allison or Todd's for planning & priority.

At DevelopmentNow we do a lot of project work, so one of the first things we do is map out a project featureset into "chunks" and rough feature descriptions, then assemble them into a basic dependency & delivery list. One of the big aspects in project management is getting scope right, and since there's always a tradeoff between delivery time & overall features, it's important to make sure that you haven't forgotten a critical feature, and that everyone (including and especially the client) understands what will be delivered when. So I can see how the additional dimensions in a grid, along with colors, can help add additional contextual scope information without sacrificing simplicity.

FYI, Todd actually prints out his grids on huge (like 6 feet wide) paper, puts them up on a wall, and talks through them with clients, allowing the client to write on the paper, interact with it, etc.

Some sample screenshots of grids below: