I've been battling a peculiar issue for a couple of days regarding Data Transformation Services (DTS) with SQL Server 2000 and SQL Server 2005.

I wrote a small C# application that utilizes DTS to pull a database from a server down to a client machine.  All in all, the application runs beautifully and the information synchronizes seamlessly.  Occasionally, however an odd error was reported: Unable to connect to source server for Transfer.  This was odd because all of my connections were succeeding.  It was also only on this computer that the error was occurring - I could not duplicate the error on other computers so I began delving into the differences between the machines.

As it turns out, the error has to do with mismatched database versions which I've whittled down to the following matrix:

As you can tell, the issue arises when the client database engine is not at least as high as the server version.  I'd long suspected that a version mismatch was the root of the problem but hadn't ascertained it until today.  After troubleshooting this for some time to make sure that there wasn't something else I was missing, I ran a trace on the SQL Server to detect whether something was happening there. (I had actually run the same trace before but failed to see the key piece of information).

The DTS transfer objects apparently have baked into them a version check.  When run from an MSDE client it performs the following query:

if (object_id('master.dbo.sp_MSSQLDMO80_version') is not null) exec master.dbo.sp_MSSQLDMO80_version else if
(object_id('master.dbo.sp_MSSQLDMO70_version') is not null) exec master.dbo.sp_MSSQLDMO70_version else select 0

SQL Server will always return a valid result set for the current version (e.g. 2005 returns valid results if the sp_MSSQLDMO90_version sproc is called and 2000 returns correctly if the sp_MSSQLDMO80_version sproc is called), but will throw and exception on any other version's sproc.  The issue is that the MSDE will check for sp_MSSQLDMO80_version on a SQL2005 box which returns an error which ultimately cascades down and cancels the DTS package.

From a SQL Express client, the query is this:

if (object_id('master.dbo.sp_MSSQLDMO90_version') is not null) exec master.dbo.sp_MSSQLDMO90_version else if
(object_id('master.dbo.sp_MSSQLDMO80_version') is not null) exec master.dbo.sp_MSSQLDMO80_version else if
(object_id('master.dbo.sp_MSSQLDMO70_version') is not null) exec master.dbo.sp_MSSQLDMO70_version else select 0

In this case, the query will continue to work against a SQL2005 server or a SQL2000 server.

In other words, SQL is throwing the 'cannot connect' error correctly.  Internally it reports "To connect to this server you must use SQL Server Management Studio or SQL Server management Objects (SMO)".

Solution:

My application must maintain full compatibility with the MSDE -> 2000 environment.  The only solution I could come to was that in order for my application to run against a SQL Server 2005 server the client computer would have to be upgraded to SQL Express.  I hate to have to do that, but I could see no other viable solution.

Are there other thoughts out there?

The Utah .NET User Group is getting the year started off with a discussion on SQL Server 2005 Development.  Yours truly has the opportunity to drill down and talk about the SQLCLR as well as many of the improvements to the T-SQL programming model in SQL2005.  It should be a good time.

If you're in the neighborhood (meaning the greater Salt Lake City area), please come on down and enjoy a great evening of code, food, friends, and fun.  We're meeting tomorrow, January 11th, 2007 @ Neumont University @ 6:00 PM.

As we've had the tradition of doing now for almost a year, several of us get together afterwards for a bite.  Please feel free to join us there too!

Date: Thursday, January 11th, 2007
Time:  6:00 PM (arrive early)
Place: Neumont University (10701 South River Front Parkway, South Jordan, UT)

It'll be loads of fun - bring your friends!

I arrived home last night from a fantastic weekend.  We had the opportunity this past weekend to have an offsite executive meeting and establish plans and goals for the coming year and to get on the same page.  It was quite productive and we accomplished many things; it was just the right mix of business and fun.

Additionally, we had the opportunity to attend the first day of CES (Consumer Electronics Show).  Boy, was that HUGE!  Despite seeing it first hand, I still have a hard time fathoming the sheer scope of the conference.  I may be mistaken, but I heard that there are over 115K attendees - good grief!  One day was plenty for me.  I can't imagine attending the entire conference, much less spending more time there; too many people, too much of the same stuff everywhere, too big.  Then again, I don't know how else it could be accomplished.

All in all, a very productive weekend and lots of fun! - But it's good to be home and back in action.

We had the opportunity last night, after arriving in Las Vegas, to take some time out and check out an exhibit that intrigued us entitled "Bodies...The Exhibition".  Wow!  The exhibit takes you through the entire body, every system, each part.  What's most fascinating is you're not looking at diagrams or models but actual people preserved by by some polymers.

Each room in the exhibit is themed to an aspect of the human body (i.e. skeletal structure, circulatory system, respiratory system, reproductive system, digestive system, etc).  There are 'view boxes' with each piece of the body perfectly preserved (some even demonstrated characteristics of disease such as Cirrhosis of the Liver) and one or two almost complete bodies in dramatic poses that further illustrate that particular aspect of the body.

One room that greatly intrigued me was dedicated to the circulatory system.  Each view box in the room had the blood vessels of each of the major organs of the human body (and one complete body as well!) - but just the blood vessels.  Turns out that to accomplish this the organ's vessels were filled with the polymer solution, then the polymer was allowed to harden, and then all organic material was dissolved away leaving only the structure of the veins.  In a word: awesome!  Especially the complete body.  Each exhibit represented only about 60% of the veins in the body because the capillaries are too small for the polymer to enter.

If you get a chance, I would highly recommend taking the time to see it and experience it.  You'll not regret it.

On a side note, I'm rather proud of myself for my restraint.  That is, not once did I break out and say that most memorable line from The Sixth Sense. :)

One of the most satisfying and gratifying aspects of writing software is to hear how much it helps customers succeed.  One such review was posted yesterday by John O'Connor.  He has some great things to say and gives nice succinct overview of what our product configurator is.

Reading his review makes me want to continue to strive to make our product even better and to further improve our great support team.  This is what I get up for in the morning! :)

This past week I've been completely engrossed in debugging software and assisting to resolve customer issues.  It's been a very good and productive week, but not too technical (hence the dearth of programming-targeted posts), at least not technical enough to warrant a post.

This weekend, however, I will be in Las Vegas, NV for, among other things, the 2007 International CES Conference.  The conference starts on the 8th and runs through the 11th, though I'll only be there on Monday and Tuesday, flying home that evening.

Prior to that I'll be working with two of my compatriots at Experlogix in an offsite meeting that I'm very excited about.  I hope to accomplish a lot this weekend.

All things are looking up.

It's pretty exciting to get the year started off this way.  I received notification today via email that I was accepted for the second year as a Microsoft MVP.  :)  I hope that more can be accomplished this coming year and the .NET community strenghtened.  Thanks to all those that contribute and make our community rock!

I've been very quiet on the blogging front this month, especially over the past couple of weeks.  I took some time off to finish off some long overdue projects around the house (namely the basement).  With the exception of Christmas Day, I've been downstairs almost every waking hour trying to wrap it all up.  Tonight I got all of the caulking and spackling done on all of the baseboards and trim.  Essentially, all that remains is painting (baseboards, trim, and walls), but that's a pretty big undertaking that will occupy every hour of my weekend.  We're installing the carpet on Monday so I have my work cut out for me.  I'll post some pictures when it's all done.

On the positive side, I'll be able to get my office downstairs (finally!) in about a week (after I get all of the outlets and switches wired up) and free up a much needed bedroom upstairs.

Just wanted to drop in and let everyone know I've not gone away, that I'll be back and blogging some pretty good stuff in the coming days (following the vacation), and to wish everyone a Happy Belated Christmas and a Happy New Year :)

Being the major Potter-phile that I am, I can't resist but to put this out there: the next/last book's name has been officially announced.  Harry Potter and the _____ _____.  The name is actually embedded in a hidden easter egg on J. K. Rowling's website.  I must admit that I was sorely disappointed upon hearing the news of the new title and opening the article that the putz author included the name right in the first line of the article only to explain a few paragraphs down that, "oh, by the way, you can find out about the name on J. K. Rowling's site via a fun hangman game."

So as to not spoil the news (unless you already know), and to make it more entertaining, here are the instructions to the easter egg, as provided by the MSN article in question (spelling errors and all :), with a little more organization and clarity):

1. Open her home page.
2. Click on the eraser and you will be taken to a room - you'll see a window, a door, and a mirror.
3. In the mirror, you'll see a hallway.  Click on the farthest doorknob and look for the Christmas tree.
4. They [sic] click on the center of the door (ed: the center of the top panel) next to the mirrow and a reef [sic!] appears.
5. Then click on teh top of the mirror and you'll see a garland.
6. Look for a cobweb next to the door.  Click on it, and it will disappear.
7. Now, look at the chimes in the window.  Click on the second chime to the right.
8. Click it again, holding down the mouse button (rephrased).  The chime will turn into a key, which opens the door (drag it onto the lock on the door).
9. Click on the wrapped gift behind the door.
10. Then click on it again and figure out the title yourself by playing a game of hangman.

Gee, figure it out after I already told you the answer! grrrr.

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 :)