It is not uncommon to have databases with a Compatibility Level that does not match the version of SQL Server they are running on. When you upgrade a SQL Server installation the databases retain a Compatibility Level that matches the version you upgraded from. The same applies to restoring or attaching databases from an earlier version.

I wrote the script below to set the Compatibility Level of a database to match the version of SQL Server. It is designed to work only on SQL Server 2005 and SQL Server 2008 instances. It uses the sys.databases view that does not exist in SQL Server 2000. I decided to use this view because I knew the script would not be executed on a SQL Server 2000 instance.

DECLARE @database nvarchar(128)
SET @database = 'Foo'

DECLARE @databaseLevel tinyint
SELECT @databaseLevel = compatibility_level FROM sys.databases WHERE name = @database
IF @databaseLevel IS NULL
    BEGIN
        PRINT N'The database ''' + @database + ''' does not exist.'
        RETURN
    END

PRINT N'Database Compatibility Level: ' + CONVERT(nvarchar, @databaseLevel)

DECLARE @productVersion nvarchar(128)
SELECT @productVersion = CONVERT(nvarchar(128), SERVERPROPERTY('ProductVersion'))
PRINT N'Server Product Version: ' + @productVersion

DECLARE @majorVersion tinyint
SELECT @majorVersion = CONVERT(tinyint, SUBSTRING(@productVersion, 0, CHARINDEX('.' , @productVersion)))
PRINT N'Server Major Version: ' + CONVERT(nvarchar, @majorVersion)

DECLARE @serverLevel tinyint
SET @serverLevel = @majorVersion * 10
PRINT N'Server Compatibility Level: ' + CONVERT(nvarchar, @serverLevel)

IF @databaseLevel = @serverLevel
    BEGIN
        PRINT N'The Compatibility Level for ''' + @database + ''' already matches the SQL Server version.'     
        RETURN
    END

DECLARE @query nvarchar(max)
SET @query = N'ALTER DATABASE [' + @database + '] SET SINGLE_USER'
EXEC sp_executesql @query

EXEC sp_dbcmptlevel @database, @serverLevel
PRINT N'The Compatibility Level for ''' + @database + ''' has been updated.'

SET @query = N'ALTER DATABASE [' + @database + '] SET MULTI_USER'
EXEC sp_executesql @query

I can think of a few different cases that will result in the recursion of a CTE (Common Table Expression) in SQL Server coming to an end:

• You run out records to recursively select and your result is returned without error.
• You reach the default maximum recursion depth of 100 and an error occurs.
• You reach the maximum recursion depth you specified using the MAXRECURSION query hint (a value between 1 and 32767) and an error occurs.
• You set the maximum recursion depth to have no limit using the MAXRECURSION query hint (a value of 0) and crash your server due to an infinite loop.
• You reach a maximum recursion depth that you manually control in your query.

Obviously, some of these cases have a more desirable outcome than others. While the MAXRECURSION query hint does provide a mechanism to ensure you do not end up in an infinite loop, reaching the limit it imposes causes an error to occur, and that is something to be avoided. It is also possible to set the MAXRECURSION query hint to 0 which will result in no limit being applied, so without a mechanism of your own in place to limit the recursion depth, you have definitely made it easier to get yourself into trouble.

I prefer to manually restrict the recursion level and set the MAXRECURSION query hint to be the same if my restriction is greater than 100. Remember, 100 is the default limit when the MAXRECURSION query hint is not present, and you must exceed and not just reach the limit for the error to occur.

The query below is manually limited to 50 levels of recursion. There is no need to specify the MAXRECURSION query hint because the default of 100 will never be reached.

WITH LimitedLoop AS
(
    SELECT 0 AS RecursionLevel
        
    UNION ALL
    
    SELECT (LimitedLoop.RecursionLevel + 1) AS RecursionLevel
    FROM LimitedLoop
    WHERE (LimitedLoop.RecursionLevel + 1) <= 50
)
SELECT * FROM LimitedLoop

This query is manually limited to 200 levels of recursion. The MAXRECURSION query hint is specified because its default value of 100 is less than the manually imposed limit.

WITH LimitedLoop AS
(
    SELECT 0 AS RecursionLevel
        
    UNION ALL
    
    SELECT (LimitedLoop.RecursionLevel + 1) AS RecursionLevel
    FROM LimitedLoop
    WHERE (LimitedLoop.RecursionLevel + 1) <= 200
)
SELECT * FROM LimitedLoop
OPTION (MAXRECURSION 200)

It is also worth noting that the first query will return 51 rows and the second 201 rows. This is because the first record returned containing the 0 in the RecursionLevel column is part of the base result set and not the recursive invocation.

I have used VistaDB Express for many hobby projects in the past and found it to be simple and reliable. This blog is running on VistaDB Express and it has never given me any trouble. Unfortunately, with the release of VistaDB 4 there will no longer be a free edition available for any purpose. While this is very disappointing I can understand why they have taken this stance.

Sorry, we had to discontinue the free edition of VistaDB due to the support demands, and the number of companies who were using it for commercial purposes.  We have to put food on the table and feed our families just like anyone else, this was the free as in beer problem (it cost us a lot to build and support, but people used it to make money and not pay us).

When it comes to small hobbyist projects this is not the end of the world, as there are other free products available that will meet such needs. I made the jump over to SQLite, another very popular and free embedded database engine. The main reason I chose to use VistaDB Express over SQLite in the past, was the fact that it had a wider range of supported data types and good management tools provided out of the box. However, there is a free management tool for SQLite called SQLite Database Browser that does a good job, and when used with a decent ORM the reduced number of supported data types in SQLite is soon forgotten.

I have been using LightSpeed Express from Mindscape as the ORM atop of both database engines, and I must say that I absolutely love it. This free edition is limited to creating a model with at most eight classes, which I have found to be enough when coding for fun. If you don’t need many entities in your model it is well worth using given the features it provides. To see how the LightSpeed feature set stacks up against other ORM technologies including LINQ to SQL and LINQ to Entities have a read of the comparison document on the Mindscape website. LINQ to LightSpeed is the standout feature that makes this framework a winner for me. It enables you to use LINQ against any of the databases that LightSpeed supports: SQL Server, Oracle, PostgreSQL, MySQL, SQLite, VistaDB, Firebird and Amazon SimpleDB.

LightSpeed has an editor for Visual Studio that allows you to build your model in a manner similar to that provided by LINQ to SQL and LINQ to Entities. You can even use the design time tools to create a new database from an existing one. This is exactly what I did when moving an existing project from VistaDB Express to SQLite.

The LightSpeed designer extends that portability to design time. By changing the connection string and data provider on a model, and choosing Update Database, you can rapidly create much of your schema on the new database, rather than having to manually translate the SQL CREATE scripts from one dialect to another.

Once you have a good ORM in place you tend not to worry so much about the underlying database engine, well at least when your requirements are simple and you have no desire to spend money. This was certainly the case for me with VistaDB Express and SQLite. It would be interesting to know how many other VistaDB Express users have switched to a completely different database engine for their home projects despite the low price of the entry level VistaDB 4 Lite version. Regardless, I can assure you that LightSpeed Express will make any database engine you choose easier to work with, and like any good ORM will allow you to more easily change it at a later point in time.

Service Pack 1 for SQL Server 2008 has been released and is available for download. The service pack offers very little in the way of features, but is instead comprised of a rollup of cumulative updates and bug fixes. There is a note on the download page that seems like it was put there to address the disappointment that may be felt by those looking for new features:

We remain committed to our plans to keep service packs contained, focusing on essential updates only, primarily a Roll-up of Cumulative Update 1 to 3, Quick Fix Engineering (QFE) updates, as well as fixes to issues reported through the SQL Server community.

Despite the lake of sexy new features there are enough bug fixes to make installing the service pack worthwhile. The release of the first service pack for a Microsoft product is often the point at which many quality sceptical users take up the technology, so maybe this release will bring an increase in the adoption of SQL Server 2008.

You may have noticed that after renaming a computer running SQL Server that the value returned for @@SERVERNAME has not been updated. It returns the computer name as it was during installation of SQL Server. The SERVERPROPERTY function does take into consideration changes made to the computer name when the ServerName property is requested.

To fix the server name you need to run the sp_dropserver and sp_addserver stored procedures. Instead of typing in the computer and instance names you can use the current @@SERVERNAME value for the old name, and SERVERPROPERTY('ServerName') for the new. The TSQL below fixes the name of your local SQL Server and works for default and named instances. You will need to restart the service for the change to take affect.

EXEC sp_dropserver @@SERVERNAME
GO

DECLARE @server nvarchar(128)
SELECT @server = CAST(SERVERPROPERTY('ServerName') AS nvarchar(128))
EXEC sp_addserver @server, 'local'
GO

Here are some links if your keen further information.

• @@SERVERNAME (Transact-SQL)
• SERVERPROPERTY (Transact-SQL)
• sp_addserver (Transact-SQL)
• sp_dropserver (Transact-SQL)
• "Renaming A Server" Topic in SQL Server Books Online is Incomplete

The last link refers to a KB article for SQL Server 2000 but still contains relevant information.