Are you working on lots of databases with different compatibility level and different SQL Server versions? I have this problem almost every day. I have to make my code stable and robust enough to work on different SQL Server versions and on the code must use functionality on databases with different compatibility levels.
I have to check for the database compatibility level before configuring the different performance counter and custom features for my database activity monitor, described in my previous post. I was pretty baffled when I discovered this logical error last week.
As a result, I created a helper function used in my day-to-day work to avoid mixing different compatibility levels.
CREATE FUNCTION [dbo].[GetDatabaseVersion] ( @dbname NVARCHAR(100) ) RETURNS NVARCHAR(50) AS BEGIN DECLARE @ver NVARCHAR(5) ; DECLARE @returnvalue NVARCHAR(100) ; SELECT @ver = compatibility_level FROM sys.databases WHERE name = @dbname ; SET @returnvalue = CASE WHEN @ver = '80' THEN 'SQL Server 2000' WHEN @ver = '90' THEN 'SQL Server 2005' WHEN @ver = '100' THEN 'SQL Server 2008 (R2)' WHEN @ver = '110' THEN 'SQL Server 2012' WHEN @ver = '120' THEN 'SQL Server 2014' END; RETURN @returnvalue END