Within the context of SQL Server, retrieving the server version is pretty easy, and there is a variety of ways one can go about retrieving the information.

You might consider calling the sp_server_info sproc that I mentioned in a post yesterday with specifying the attribute id of '2':

EXEC sp_server_info 2

Unfortunately, this returns columnar data so unless you do something like I proposed in said post it's largely unhelpful.  Alternatively, and much more simply, you can query the @@version function (which I suspect ultimately defers to the same data that the sp_server_info sproc returns):

SELECT @@version

Still, this data may not be what you're looking for.  It's quite verbose and contains a lot of extraneous information.  You may be after just the version number.  Fortunately, this is quite easily accomplished by querying the serverproperty function.

SELECT serverproperty('ProductVersion')

Incidentally, there is a lot more information you can glean from the server via the serverproperty function (product level, edition, instance name, and much more).

You may have need to write some TSQL that is conditional based on the target SQL Server edition.  For instance, I'm in the process of writing software that must run on both SQL Server 2000 and SQL Server 2005, but where possible I'd like to use SQL Server 2005's capabilities and not necessarily cater to the least common denominator of SQL Server 2000.

The ProductVersion may still be too specific, as it contains minor versions, SP updates, etc.  Essentially, what I want is to ensure that I'm dealing with the correct DB engine by major version # and not take into consideration any minor updates.  The following code illustrates in a very simple manner extracting just the major version from the property.  In this case, the TSQL simply returns a string indicating what was detected, but your code may actually contain logic:

DECLARE @ver nvarchar(128)
SET @ver = CAST(serverproperty('ProductVersion') AS nvarchar)
SET @ver = SUBSTRING(@ver, 1, CHARINDEX('.', @ver) - 1)
IF ( @ver = '8' )
   SELECT 'SQL Server 2000'
ELSE IF ( @ver = '9' )
   SELECT 'SQL Server 2005'
ELSE
   SELECT 'Unsupported SQL Server Version'

It looks as though Microsoft did in fact release the Visual Studio 2005 Service Pack 1 yesterday.  It's a hefty download (approx 432 MB), but I hope it's worth it.  I imagine it would take a lot to fix many of the 'house-of-cards' issues that VS 2005 has.

Be forewarned, however.  I can't recommend running it on Vista yet.  Within the download instructions it clearly states that 1) the installation on Vista will take a long time (up to an hour just to verify digital signatures) and 2) there's an additional update to SP1 for Vista that is currently in Beta.

Enjoy :)

I've always found working with stored procedures that return tabular data somewhat cumbersome within the context of another stored procedure.  In other words, it's not the most intuitive of tasks to call a stored procedure that returns a rowset and work with its data from within a stored procedure.

There are several approaches that one might take to accomplish this.  Creating a temporary table or a CURSOR come to mind immediately as two valid approaches.  In fact, in SQL Server 2000, I found a temp table to be the most reliable way to work with data in this manner.

As a for instance, there is a stored procedure in SQL Server called sp_server_info.  This procedure, when executed without parameters, will return a three-column resultset consisting of an ID, an attribute name, and an attribute value identifying various attributes of the SQL Server itself.  You can also call the stored procedure specifying the ID and it will return the row identified by the ID.

Furthermore, SQL Server 2000 introduced the concept of TABLE-type variables.  However, they were largely useless in this context because you couldn't INSERT INTO a table variable the resultset from a stored procedure.

This changes in SQL Server 2005, however.  Let me illustrate this with a very simple, contrived example.

Suppose that I wanted to call the sp_server_info from a stored procedure, work with the data, and return it to the caller.

CREATE PROCEDURE dbo.GetSprocVersion AS
  -- Create a table variable that matches the resultset of the stored procedure
  DECLARE @tmp TABLE (
    attribute_id int, attribute_name varchar(60), attribute_value varchar(255)
  )
  -- Populate the table variable by invoking the stored procedure
  INSERT INTO @tmp EXEC sp_server_info
  -- Return the appropriate result
  SELECT attribute_value FROM @tmp WHERE attribute_id = 500
GO

What I like about this example, if you look closely, is that you can call INSERT INTO..EXEC on a TABLE variable :) - very cool!  Now, I can make this code slightly more exciting by doing the following:

CREATE PROCEDURE dbo.GetSprocVersion AS
  -- Create a table variable that matches the resultset of the stored procedure
  DECLARE @tmp TABLE (
    attribute_id int, attribute_name varchar(60), attribute_value varchar(255)
  )
  -- Populate the table variable by invoking the stored procedure
  INSERT INTO @tmp EXEC sp_server_info 500
  -- Return the appropriate result
  SELECT attribute_value FROM @tmp
GO

In this particular example I invoke the stored procedure passing a parameter.

Ok, this isn't rocket science, but as you can see, it makes it very easy to work with set data using a TABLE variable.  I envision this may be more useful were you to call a sproc that returned a larger set (say a set of customers) and then process that result to return either a finer resultset, massage the data, or otherwise.

...
"The time has come," the coder said,
"To talk of many things:
Of loops - and blocks - and heaps and stacks -
Of variables - and strings -
And why you see AND, OR, 'n NOT
And whether the NIC blocks pings.
...

          - Adapted from Lewis Carroll's Jabberwocky

Tomorrow (December 14th, 2006) we'll have the December installment of the Utah .NET User Group.  We're very excited to have Robert Green (more recent/updated website/blog?) visiting us from out of town.  He'll be talking about "Developing Office 2007 Solutions with Visual Studio Tools for Office 2nd Edition" (VSTO SE).

Come one and all - it should be a great event!

Time: 6:00 PM
Date: December 14th, 2006
Place: Neumont University (10701 South River Front Parkway, South Jordan, Utah)

See you there!

This past week was very exciting.  Not only did I have the opportunity to work with a potential customer on a pilot, proof-of-concept integration to our existing product configurator, but I met some great friends in the process.

As I mentioned in my post a few days ago, I was down in Longmont, Colorado (a delightful place on many levels) working with a prospective customer and demonstrating some very cool software.  I also had the distinct honor to meet some great developers: Hailu, Praveen, Jvalin, and Atul (all of various Indian heritages).  I hope they had as good a time working with me as I did with them.

Perhaps the highlight of my entire time there was spent with my best friend of over 20 years, Erik Peticolas, and his wife Nancy who live in Denver.  I haven't had the chance to see them since my days at Microsoft when I would travel to Denver on a monthly basis for our monthly District Meeting and would purposfully make it a point to see them and have dinner at the very least.  We had the chance to go our four times this past week which was quite a treat.

Now I'm home and very tired, but very satisfied at a great week.

December has already been a very eventful and exciting month.  It's also been a semi-difficult time to blog because there are so many things that I'm doing that are NDA and I find it difficult to derive topics from thin air.  I'd rather post about things that are relevant and applicable in what I'm doing.

I have the opportunity this week to spend time in beautiful Longmont, Colorado.  And when I say beautiful, I mean it.  It's not a luscious, colorful place (at least not in December), but it's definitely pleasant and rural.  It's not too far from the city (45 minutes north of Denver) and about the same to the airport.  I understand that the population here is quite a bit more than it lets on, but I'm constantly skirting around it on SR-119 that I don't get to see too much.  I really like it here.  I like that the houses are not 20' apart.  I like open space, trees, and mountains.  It's definitely nice.

I have the opportunity to work with some pretty sharp developers here on a custom integration prototype.  Things are going really well.  In a matter of two days we've gotten a lot up and running.  It's going to be fun to demonstrate its functionality at the end of the week.

Along with Scott Golightly and Pat Wright, I had the opportunity today to particpate in a SQL Server 2005 all-day training at our local Microsoft offices.  The day was broken into three parts: Pat covered SQL Server Management, Scott discussed Business Intelligence, and I tackled Development.

While I can't speak for the others (as I was not able to attend their presentations unfortunately), my presentation went very well - I even threw in some off-the-cuff demonstrations (as I am want to do) of returning tabular information from a .NET stored procedure :)  We covered a variety of topics, most notably the SQLCLR, TSQL enhancements, and ADO.NET 2.0 along with a smattering of data encryption.  All in all I'm fairly happy, though I can always look back and nitpick some things that I should have done better.

I had a great time and I believe those in attendance did as well.  I look forward to the next one.

I was going though some old code that I wrote a few years ago and stumbled upon a little nugget that might prove to be useful.  Back in my VB 2.0-6.0 days there was an event on a Form called QueryUnload.  This handy little event proved to be very useful because not only could it be cancelled (effectively stopping the form from unloaded), but it also provided some information as to how the form was closing (e.g. whether the user clicked the 'X', code closed it, or the OS was shutting down).

Unfortunately nothing exists in the .NET Framework out of the box that provides this functionality.  I had this need a long time back and drummed up some code that may prove to be useful out there, so here it is.

Essentially, I have a base form class (appropriately named FormBase) which exposes an UnloadMode property that you can query in the _OnClosing event (note that the OnClosing method is sealed so you need to add the handler rather that override the OnClosing method directly).  The UnloadMode property indicates how the form is closing down.

Here's the code:

using System;
using System.ComponentModel;
using System.Windows.Forms;

namespace Devstone.Demo.Forms {

#if ( DEBUG )
   public class FormBase : Form {
#else
   public abstract class FormBase : Form {
#endif

      public enum FormUnloadMode : int {
         None,
         FormControlMenu,
         Code,
         OperatingSystemShutDown
      }

      private bool _closeButton = false;
      protected FormUnloadMode ulMode = FormUnloadMode.None;

      public FormUnloadMode UnloadMode {
         get { return ulMode; }
      }

      protected sealed override void OnClosing(CancelEventArgs e) {
         // set the flag back to false so as to not prevent the WM_CLOSE message
         // from changing the UnloadMode
         _closeButton = false;

         // ensure that the event will be properly raised in the derived classes
         base.OnClosing(e);

         // reset the UnloadMode flag
         ulMode = FormUnloadMode.None;
      }

      protected override void WndProc(ref Message m) {
         const int WM_CLOSE = 0x0010;
         const int WM_SYSCOMMAND = 0x0112;
         const int WM_ENDSESSION = 0x0016;
         const int SC_CLOSE = 0xF060;

         switch ( m.Msg ) {
            case WM_CLOSE:
               if ( !_closeButton ) ulMode = FormUnloadMode.Code;
               break;

            case WM_SYSCOMMAND:
               if ( m.WParam.ToInt32() == SC_CLOSE ) {
                  _closeButton = true;
                  ulMode = FormUnloadMode.FormControlMenu;
               }
               break;

            case WM_ENDSESSION:
               ulMode = FormUnloadMode.OperatingSystemShutDown;
               break;
         }

         base.WndProc(ref m);
      }

   }

}

I hope this proves helpful in some small way.  In terms of future enhancements, I may go ahead and implement a QueryUnload method on this to hearken back to the older programming model (which is perhaps a bit more clear in some instances).  If I do, I'll be sure to update the blog accordingly.

I had an interesting set of experiences today while setting up some Virtual PC images.  I had an older, unused desktop machine sitting around and decided to set it up on Windows XP with the sole purpose of playing games.  I had a lot of fun setting it up and getting it ready.  Several games, however, won't run from within Windows (even in Windows 95-compatibility mode) as they are DOS-based games.  I therefore set out to create a VPC to run MS-DOS 6.22 and Windows 3.1.  This turned out to be a bit more challenging than I had originally expected.

The crux of the issue was that I didn't have 1) a working 3.5" floppy drive (depite having tried to install 2 different drives) or 2) my original MS-DOS installation disks.  To acquire the installation disk images I went to the Microsoft MSDN downloads site and found the 6.22 installation disks.  The images are in an .IMG format and cannot be mounted as disk images within Virtual PC.  VPC expects the disk images to be a valid/complete 720KB, 1.2MB, or 1.44MB image - none of these were.  In order to mount them in the VPC they need to be valid .VFD (Virtual Floppy Disk) files.  This is quite easy to do with a Virtual Floppy Driver.  The one I used can be downloaded here.

The MSDN download of the disks includes a batch file which (using dskimage.exe to copy the .IMG files to a disk) expects a floppy disk as the destination.  I had, therefore, to create virtual disks seeing that I didn't have any functioning hardware.  This was easily accomplished as follows:

1. VFD.exe INSTALL
2. VFD.exe START
3. VFD.exe LINK a /L
4. VFD.exe OPEN c:\Dest01.img (creating the new image when prompted)
5. FORMAT A:
6. Copy the disk image to the virtual A: via DSKIMAGE.exe
7. VFD.exe SAVE c:\Disk01.vfd
8. VFD.exe CLOSE
9. Repeat steps 4-8 for each of the three MS-DOS disks.

Once created, I also created a blank 3.5" floppy disk image to have for archival purposes (which you can download here).

Now that I had booted up my VPC using the new .VFD files and installed MS-DOS 6.22, I saw that it was consuming 100% of my processor (well, 50% of the Core 2 Duo).  I had to install the Virtual PC Additions for DOS.  I simply had to set the machine up to recognize my CD player (via MSCDEX.exe) and I was off to the races.  The VPC is only 6.1MB - compared to > 5 GB for a base Vista VPC.

I'll look into the licensing and post my VPC of MSDOS 6.22 if possible, though I'm not optimistic.

I had a lot of fun setting this up and reliving my DOS days from years ago.  Dealing with all the little nuances like autoexec.bat, config.sys, et al was quite nostalgic and enjoyable...but I'm glad I don't have to go through it on a daily basis any more. :)

Now that it's all set up and running, though, I've had a lot of fun installing and playing my old DOS games.  Oh, and it was a blast to reinstall Windows 3.1 (which only took a minute or two) and play with it.

It's important to be considerate to users of your applications.  I've discussed in the past a very simple technique to detect if the application si running in a Terminal Services (aka Remote Desktop) session.  Users appreciate it when certain aspects of the application gracefully degrade in such an environment.  For instance, a splash screen could be replaced with a less-graphically-intensive one, sound effects might be reduced or eliminated, color schemes can be changed, animations eliminated, etc.

Though I've not investigated it previously, I've been interested knowing if an application is running within a virtualized environment (e.g. Virtual PC, et al).  Today, I stumbled upon a nice link that illustrates a technique that has merit within the Microsoft virtual environments.  It involves checking the manufacturer of the motherboard on the VPC.  In a VPC it is identified as Microsoft.  Unless MS decides to get into the motherboard industry this technique might last.  I'm going to be testing this technique in the coming days to see how reliable it is and whether it also works in a Virtual Server environment.  I don't believe that it will work in a VMWare virtual machine, but there are also techniques for testing therein as well.