Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Compatiblity Mode

Posted on 2016-08-16
4
Medium Priority
?
92 Views
Last Modified: 2016-09-09
I was trying to write a Pivot query. I got an error message that said something like "I may  need to go up to a higher "Compatibility Level."

In the Database Properties, I saw where I could change the Compatibility Level to SQL Server 2005(90). Right now it's on SQL Server 2000(80).

I'm on a SQL 2008 box.

Would it cause a problem if I raise the level?
0
Comment
Question by:isames
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 25

Expert Comment

by:SStory
ID: 41758200
"Before you change the compatibility level of a database, you should understand the impact of the change on your applications. For more information, see ALTER DATABASE Compatibility Level (Transact-SQL)."
source: https://msdn.microsoft.com/en-us/library/bb933794.aspx

"Use compatibility level as an interim migration aid to work around version differences in the behaviors that are controlled by the relevant compatibility-level setting. "
https://msdn.microsoft.com/en-us/library/bb510680.aspx

Since, per Microsoft it is a temporary stop gap type measure until you can migrate to newer compatibility. I'd not do it while the db has anything going on, backup the db before starting, test it somewhere and be ready to modify any applications that may break. That's just from what I've read...not personal experience with it.
0
 
LVL 52

Accepted Solution

by:
Vitor Montalvão earned 2000 total points
ID: 41758932
Pivot command didn't exist in SQL Server 2000. You'll need to change the compatibility level of that database to 2005 but I recommend you to change it to 2008 since is the version that you're running.

Note
When PIVOT and UNPIVOT are used against databases that are upgraded to SQL Server 2005 or later, the compatibility level of the database must be set to 90 or higher.
0
 
LVL 10

Expert Comment

by:Jason clark
ID: 41769999
When database compatibility is set to previous version and they are attempted with procedure of newer version they will throw the Compatibility Level error. you need to change the Compatibility Level. Read this article to understand the affects of Compatibility Level on database
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 41784146
In addition to Andrew Jackson's good article that Jason Clark points you to above, please note that there were quite a few "breaking changes" when moving from SQL Server 2000 to 2005. There's a pretty comprehensive listing here. You should also see this page. There should also be an equivalent one for 2005 -> 2008
One example that comes to mind in particular is that it can be common to use a small query to provide a list for a combo-box: from 2005 it is not possible to use an order by clause within a sub-query, which broke a lot of code where I worked at the time.

Hope this helps

Mike
1

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question