We’re rewarding the question askers & reputations are being recalculated! Read more.

Questions tagged [compatibility-level]

Compatibility level is a SQL Server feature on the database level that can be used to make a database behave like it would be running on an older version of SQL Server than it actually is.

Filter by
Sorted by
Tagged with
47
votes
2answers
39k views

What is the actual behavior of compatibility level 80?

Could somebody provide me with a better insight about the compatibility mode feature? It is behaving different than I expected. As far as I understand compatibility modes, it is about the ...
32
votes
6answers
22k views

Make SqlClient default to ARITHABORT ON

First things first: I'm using MS SQL Server 2008 with a database at compatibility level 80, and connecting to it with .Net's System.Data.SqlClient.SqlConnection. For performance reasons I've created ...
16
votes
3answers
35k views

Implications of changing compatibility mode in SQL Server from 100 to 110

I have a database Mydatabase created in SQL Server 2008 R2. I have upgraded to SQL Server 2012. I was trying to execute below query to calculate percentile select Distinct [KEY],PERCENTILE_CONT(0.25)...
11
votes
2answers
19k views

Restore database from backup file of different version / edition

I read that it's possible to restore a database in SQL Server as long as you're restoring from an older version to a newer version, for backwards compatibility reasons. Does anyone know off hand if ...
8
votes
1answer
377 views

Is there a way to easily validate that a SQL Script is compatible with a specific SQL Server version?

I am currently working on a project that needs to operate in different SQL environments. Most of my developers have SQL Server 2017. The application runs in SQL Azure, and the client has recently ...
7
votes
2answers
23k views

What is the meaning of Microsoft Compatibility Level?

Setting cmptlevel to 80 on a SQL-Server 2005 inhibits the use of PIVOT and APPLY, but doesn't affect the use of Analytical Functions. Is there any rational behind this?
6
votes
3answers
248 views

How to Get Median Value From Row in SQL Server 2008 R2 Without Using Pivot and Unpivot Table

Create Table Script create table temp ( id int identity(1,1), a decimal(6,2), b decimal(6,2), c decimal(6,2), d decimal(6,2), e decimal(6,2), f decimal(6,2), g ...
6
votes
2answers
5k views

How to detect compatibility errors - SQL Server 2012

I am changing a database's compatibility mode from 90 (SQL Server 2005) to 110 (SQL 2012) and I am wondering at what point would I see errors from breaking changes if there are any. I ran Upgrade ...
6
votes
1answer
100 views

Issue with variable length string between compatibility level 80 and 100

I was getting tons of issues related with a specific scenario on my current enterprise. We have a SQL Server 2008 and we want to move to SQL Server 2016, so we are moving the compatibility from 80 to ...
6
votes
1answer
387 views

Database Compatibility Requirement for Transactional Replication

Does the subscriber "within two version requirement" for transactional replication from this MSDN article apply to the SQL Server Instance, Database Compatibility Level, or Both? For example, would ...
6
votes
1answer
2k views

pgAdmin and PostgreSQL compatibility matrix [duplicate]

Has anyone ever put together a version compatibility matrix for pgAdmin versions and PostgreSQL versions? https://www.pgadmin.org/download/windows.php https://www.postgresql.org/download/windows/
5
votes
1answer
201 views

Do SQL Server Compatibility Levels Introduce Old Security Vulnerabilities

Can anyone help settle what I thought was a simple question. A fellow DBA friend and I were talking about compatibility levels in general, and their relation to security specifically. He posited ...
4
votes
2answers
2k views

Does the compatibility level for sql server work reliably for scripts?

We have some DB scripts that need to be migrated from SQL Server 2008 R2 back to 2005, often scripts created in SQL Server 2008 / 2008 R2 won't run on a SQL Server 2005 installation (which some ...
4
votes
1answer
108 views

Howto query all files, their fill level and additional drive info with one query?

I need a query that puts together the following results: one row per database file across all databases additional columns with information about the locigal drive the file is on information about ...
4
votes
1answer
856 views

Updating a compatibility mode 80 database for SQL Server 2014

We have a SQL 2008 R2 server with a database on it that is running in compatibility mode 80. We are interested in upgrading 2008 R2 to 2014, but the upgrade adviser doesn't work because we can't force ...
4
votes
1answer
1k views

Reverting Compatibility Mode 120 to 100

We recently upgraded the compatibility level (CL) of our production databases from 100 to 120. Within a couple of days, users started complaining of increased wait times and general slowness. It was ...
4
votes
2answers
5k views

Query execute slowly when changed compatibility Level of my database [duplicate]

I use SQL Server 2014 and have a database with compatibility level 110 (SQL Server 2012). also I have a stored procedure on it that execute about 10 second. When I change compatibility level of my ...
3
votes
3answers
1k views

Generate backwards compatible CREATE DATABASE script with all objects?

I'm working on SQL Server 2016, and I want to deploy a database I have created to Sql Server 2012 SP1. I found I was unable to create a backup in v2016 and restore to v2012. So instead in the v2016 ...
3
votes
1answer
1k views

Will certain Database Compatibility cause performance degradation?

I'm running a variety of SQL Servers from 2005 to 2012 across several machines. I have many of these databases running in SQL Server 2000 compatibility mode. Will using an older compatibility in a ...
3
votes
1answer
8k views

How can I query the SQL Server Compatibility Level into a variable?

I know to query the current Compatibility Level of a database with declare @dbname sysname select @dbname = db_name(0) exec sp_dbcmptlevel @dbname But this seems to return its result via print and I ...
3
votes
1answer
145 views

Ambiguous column error on single table

I have the following code that is generated by an unsupported app SELECT ID, TYPE, ID FROM my_table t1 ORDER BY TYPE, ID I can't change this, now it used to work fine on SQL2008R2 or below on DB's ...
3
votes
3answers
112 views

Backing up a SQL 2008 DB, then restoring, gives a SQL 2005 (compatibility level 90) DB

We have four different databases on a SQL Server 2008 (not SP2 - don't ask me why) database server that have an odd problem. When listed from sys.databases, these databases report that their ...
3
votes
1answer
394 views

What is Collation Compatibility_60_406_30001 in SQL Server

We have an SQL Server 2014 (recently upgraded from 2008 R2) instance where one of the databases has some strange settings for collation: In the Properties window for the database we have the ...
3
votes
1answer
241 views

Translate query using APPLY to run under compatibility level 80

I want to use the following query: SELECT SUBSTRING(qt.TEXT, ( qs.statement_start_offset / 2 ) + 1, ( ( CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.TEXT) ...
3
votes
1answer
596 views

SQL Server 2008 and compatibility level 80

Is there any technical reason why an application certified on SQL Server 2000 should not work properly on SQL Server 2008 with compatibility level 80? In other words would there be any possible data ...
2
votes
2answers
545 views

Do SQL service packs effect SQL Compatibility mode?

We will soon be upgrading from a SQL 2008 R2 instance to a SQL 2016 instance. One of our databases has an application which our supplier says is compatible up to SQL 2012 SP1, but not SQL 2012 SP2, ...
2
votes
2answers
770 views

Error with sys.dm_db_index_physical_stats

I am executing a select statement to retrieve fragmentation percentage of indexes and facing below error. SELECT A.NAME,B.NAME,C.avg_fragmentation_in_percent FROM SYS.TABLES A INNER ...
2
votes
3answers
4k views

Changing compatibility level of database causes performance improvement of stored procedure

We have a Web Application in a Windows Server 2012 Datacenter (IIS 8), running under .NET Framework 4.0, which connects to another Windows Server 2012 Datacenter with SQL Server 2012. My Database has ...
2
votes
1answer
38 views

Can changes to cardinality optimization be made without taking users offline?

I have an old SQL Server database that has been migrated a few times since < 2005. Some queries are running very slowly. I have been advised that the following changes could help performance: ...
2
votes
1answer
77 views

SQL 2012: Executed generated DB script in SQL 2008 R2

I generated a script (using the wizard from SSMS 2012) to produce a SQL 2008 R2 compatible database script. I ran this script in a SSMS 2008 R2 instance and i got a bunch (30) of these possibly "...
2
votes
1answer
287 views

Compare the database compatibility_level to the server version?

I have a mixed environment of servers from 2000 to 2012, and I'd like to find any databases that have a compatibility level set less than the version of the server it is running on. Ideally, I'd like ...
2
votes
1answer
2k views

Mysql compatibility between client and server of different versions

How to know what version of mysql client is compatible with what version of the server? Is there backward or forward compatibility tables from the client and/or server point of view? For a given ...
2
votes
1answer
89 views

Database compatibility level [duplicate]

We are on SQL Server 2008 R2 and have DBs compatibility level set to SQL 2000 or 2005. What are differences in compatibility level across the versions ? Are there any advantages changing it to 2008 ...
2
votes
0answers
322 views

Identity in a view treated as ordinary int - migrating from SQL Server 2000 to 2012

I'm migrating a very old platform from a SQL Server 2000 backend and MS Access 2003 as its frontend to SQL Server 2012 backend and MS Access 2010 as the frontend. This obviously means a bit of ...
1
vote
3answers
524 views

SQL Server Upgrading Compatibility Level from 2005 to 2012

We have old database in SQLServer 2012 Enterprise in Compatibility mode: 90 - SQL Server 2005 . What are the consequence of changing database compatibility mode from 90 into 110-SQL Server 2012? Many ...
1
vote
1answer
443 views

SQL Server prevents me from changing compatibility level [closed]

I have a SQL Server DB that is in a SQL Server 2008 Express instance that will not let me change the compatibility level beyond SQL Server 2005 (It was originally created in 2005) either through the ...
1
vote
1answer
111 views

Could anyone please help me understand MSSQL Compatibility Level?

I have a task to upgrade a database from SQL Server 2005 to SQL Server 2012. So I took backup of the old database and simply restored it onto SQL Server 2012 (SP3). Executed query on New database: ...
1
vote
1answer
210 views

SQL Server Migrate Database - Set Compatibility Level

We are planning on migrating from SQL Server 2008R2 to SQL Server 2014 (or 2016). One 3rd party application is not supported after version 2008R2. Is it OK to migrate this application database to ...
1
vote
1answer
140 views

why will compatibility mode change amount of storage iops

We recently upgraded from SQL Server 2012 to SQL Server 2016. We noticed an unusual amount of IOps on our Dev and Integration servers, but not on production. The only difference is that production ...
1
vote
2answers
1k views

Converting old SQL 2000 Non-Ansi code to SQL 2012 compatible

We have a legacy application that is only used for a read only reference. It is using SQL Server 2000. We want to migrate its database to SQL version 2012, however we can't upgrade the software and ...
1
vote
3answers
2k views

Changing Compatibility level from 90 to 120 in sql 2014 migration

I have SQL 2008R2 server to migrate to SQL2014 Server. A side by side upgrade is what we are thinking for few databases in SQL 2008R2 which are having compatibility level of 90 or 80. Can I migrate ...
1
vote
2answers
407 views

SQL Server compatibility level

I inherited a database server with SQL Server 2012 Enterprise Edition. All the databases (except for the system databases) are at the 90 compatibility level. How do I justify updating them to 110 ...
1
vote
1answer
3k views

Tool for identifying stored procs with deprecated syntax?

I have to move a DB running in compatibility mode 80 (SQL Server 2000) on a SQL Server 2005 to a SQL Server 2012. How can I scan it for stored procedures that might break due to deprecated or ...
1
vote
2answers
2k views

Change compatibility Level on 80 to 100

We are about move all databases from a SQL server 2008r2 (Standard edition) to a 2016 standard edition server. Many databases have its compatibility level set at 80, wich is not supported on the 2016 ...
1
vote
1answer
68 views

What changes SQL Server Compatibility Level changes?

We had a database and lots of functionality that worked fine in SQL Server 2008 R2. We recently upgraded both our development and production servers to SQL Server 2014. Unbeknownst to us, our ...
1
vote
2answers
1k views

Compatibility level 100 on SQL Server 2008 R2 [duplicate]

I'm upgrading my databases to compatibility level 100 on SQL Server 2008 R2. My next step is to check if my stored procedures, functions, views, triggers are still working properly. Running each of ...
1
vote
2answers
34 views

How do you get the default compatibility level in Azure SQL Database from T-SQL?

In on-prem SQL Server you can check the server version and multiply by 10 to get the maximum compatibility level supported by the server, or check the compatibility level of the model database to get ...
1
vote
0answers
540 views

Oracle forms and report 6i compatibility issue

Please advise,is there any compatibility issues with windows server 2008 64 bit and oracle forms and reports 6i? I am being asked to buy a new server with 32bit processors by my support provider. Are ...
0
votes
1answer
85 views

SQL Server 2012 (compability level 110) and SQL Server Native Client 10

I've upgraded my SQL Server 2008R2 to SQL 2012. I will set the compability level to 110 (2012) to all databases. But I'm wondering if it will create some problems for clients that connect to this ...
0
votes
1answer
294 views

Can i alter a database to single user which is configured in log shipping

We are using a production database (SQL Server 2012) and database compatibility mode is 90 (SQL Server 2005). Can I alter the database to single user mode and change to 110? What will be the impact ...