TSQL change in SQL 2008 can break legacy code

As of the 2008 version, SQL Server no longer supports the old antiquatedd syntax for outer joins. It now requires the use of the newer JOIN keyword syntax. While it is a good idea to get rid of this flawed syntax, there is a considerable amount of legacy code that uses it. Before upgrading to SQL 2008 you should search your TSQL code for examples of the old outer join operators ( *=, =*) to see if this is going to affect your application.

Here is an example of the old syntax using the AdventureWorks sample database:

SELECT SalesTerritory.Name, SalesPerson.SalesPersonID
FROM Sales.SalesTerritory, SALES.SalesPerson
WHERE Sales.SalesPerson.TerritoryID *= Sales.SalesTerritory.TerritoryID

This syntax will generate an error in SQL Server 2008. To get around this you need to change the code to the accepted ANSI syntax or else set the database into compatibility level 80. Here is an example of the supported syntax

SELECT SalesTerritory.Name, SalesPerson.SalesPersonID
FROM Sales.SalesTerritory
LEFT JOIN Sales.SalesPerson on Sales.SalesPerson.TerritoryID = Sales.SalesTerritory.TerritoryID

So far Microsoft has only de-supported the outer join form of this syntax. The inner join form will still work. However, it should not be used in new code. I suspect it will be de-supported in a future release as it should be. Specifying join conditions in the WHERE clause makes it far too easy to leave a join condition out of your WHERE clause, especially in queries that join many tables.

SQL 2008 cluster installer bug

I just got back from an on-site assignment in Denver. W were migrating to SQL 2008 on a new set of Windows 2008 clustered servers and ran into an installer bug. We completed the install of the first node but the install failed for the second node because of an invalid product SKU. The SKU was valid. It was slipstreamed in by Microsoft when the product was purchased and downloaded. It appeared correctly in the SKU input box just before the error. We tried several times with the same result and eventually called Microsoft.

Microsoft is well aware of the bug and has a hotfix which, unfortunately, didn't work for us either. Microsoft and others have also posted workarounds which all involve running setup from the command prompt with a varying number of parameters, depending on who you are talking to. Microsoft's example has an enormous number of parameters which you can find by following links from these pages: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=363036

http://social.msdn.microsoft.com/forums/en-US/sqlsetupandupgrade/thread/13a256b0-b95b-46a7-92d5-17a0b763f8dd/

What finally worked for us was very simple: Execute this in the command prompt while logged in as an admin:

setup.exe /ACTION=AddNode /INSTANCENAME="MSSQLSERVER"

By the way, the install process for clustered instances has changed significantly. Now the cluster wizard has been broken into two wizards, one creates a single-node cluster (whatever that means). Then you must go back to the setup start screen and run the Add Node wizard for each node you want to add. You must run it from the node you want to add, not the primary node. SQL Server binaries are not moved to the second node until you do that.