Geeks With Blogs

News My Blog has been MOVED to https://mfreidge.wordpress.com

Michael Freidgeim's OLD Blog My Blog has been MOVED to https://mfreidge.wordpress.com

In our environment most of developers use SQL Server 2008,but in production we have SQL Server 2005, and some databases still have compatibility level 80(SQL server 2000)
It sometimes causes problems
E.g. the SQL wtitten on SQL Server 2008

INSERT INTO[dbo].[SiteIds]  ([SiteID], [SiteCode],[ParentID])

    VALUES  (11,'ChildOfXX',(select [SiteID] from [SiteIds] where SiteCode='XX'))

GO

on SQL Server 2005 caused
Msg 1046, Level 15, State 1, Line 5
Subqueries are not allowed in this context. Only scalar expressions are allowed.
Fortunately it’s easy to rewrite in SQL 2005 acceptable form
 

DECLARE @SiteId int

    set @siteID=(select [SiteID] from [SiteIds] where SiteCode='XX')

INSERT INTO[dbo].[SiteIds]  ([SiteID], [SiteCode],[ParentID])  VALUES  (11,'ChildOfXX',@siteID)


Another statement that generated in SQL Server 2008 should be removed in SQL Server 2005
ALTER TABLE dbo.Event SET (LOCK_ESCALATION = TABLE)

Also we had a stored procedure, that worked in  compatibility level 80(SQL server 2000), but caused an error after changing to compatibility level 90

Msg 145, Level 15, State 1, Line 4
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
The explanation of the error can be found in SELECT DISTINCT and ORDER BY post.  In my case DISTINCT was completely redundant and I’ve removed it without any side effects.

Posted on Friday, July 22, 2011 8:00 PM SQL Server | Back to top


Comments on this post: Some compatibility differences between versions of SQL Server

# re: Some compatibility differences between versions of SQL Server
Requesting Gravatar...
Is there a difference between the way SS 2005 compatibility 80 and SS 2008 compatability level 80 treats SQL?
Left by Patric Emmons on Aug 22, 2011 12:50 PM

# re: Some compatibility differences between versions of SQL Server
Requesting Gravatar...
Our developers and testing SQL servers are not set to compatibility 80, because we are are planning to convert all DBs to level 90. I am not aware about differences иуецуут SS 2005 compatibility 80 and SS 2008 compatability level 80ю
Left by Michael Freidgeim on Aug 24, 2011 8:28 AM

Your comment:
 (will show your gravatar)


Copyright © Michael Freidgeim | Powered by: GeeksWithBlogs.net