Podcast #128: We chat with Kent C Dodds about why he loves React and discuss what life was like in the dark days before Git. Listen now.

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
9
votes
1answer
410 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 ...
1
vote
2answers
37 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 ...
3
votes
1answer
155 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 ...
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 ...
0
votes
1answer
119 views

Changing compatibility level from 2008 to 2016

I have a database with hundreds of stored procedure, views, triggers, function, ... The database Compatibility Level is 2008 (100) and we would like to move it to 2016 (130). The SQL Server version ...
1
vote
1answer
112 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: ...
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 ...
6
votes
1answer
399 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 ...
0
votes
1answer
28 views

SQL 2005 SP2 Database on SQL 2012 Instance

I've an SQL Server 2005 SP2 instance that contain old databases that are not compatible with other SP of SQL Server 2005. Those databases are on physical server that don't have any space to backup ...
-1
votes
1answer
44 views

Restoring AdventureWorks2014 Database to SQL Server 2017

I restored AdventureWorks2014 database from SQL Server 2014 to SQL Server 2017. (I restored it twice, I changed the name on the 2nd restore, of course) It works fine. Now I tried to change the ...
1
vote
3answers
526 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 ...
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 ...
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: ...
1
vote
1answer
219 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 ...
5
votes
1answer
202 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 ...
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
1k views

“Cannot open database version 706. Upgrade the database to the latest version” - when trying to use availability database for read-only access

I have Availability Group with below config: replica-01: SQL Server 2012 (primary) replica-02: SQL Server 2012 (secondary synchronous) replica-03: SQL Server 2016 (secondary asynchronous) replica-01 ...
1
vote
1answer
141 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 ...
0
votes
1answer
399 views

Should I upgrade compatibility level of MS Report Server database

Recently upgraded from SQL Server 2008 to SQL Server 2012. The MS Report Server databases did not automatically upgrade compatibility level. Should I force the upgrade?
2
votes
2answers
547 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, ...
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 ...
6
votes
1answer
3k 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/
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 ...
0
votes
1answer
296 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 ...
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 ...
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 "...
1
vote
1answer
447 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 ...
2
votes
2answers
781 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 ...
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
1answer
110 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 ...
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 ...
0
votes
1answer
1k views

phpmyadmin to SQL Server

I have scripts generated from a MySQL database using phpmyadmin to create database and insert data. The scripts were exported using compatible with MS SQL. Needless to say they are not compatible at ...
3
votes
1answer
398 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 ...
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
413 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 ...
3
votes
1answer
242 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) ...
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 ...
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 ...
0
votes
1answer
603 views

Do I need to rebuild indexes when upgrading a DB from 2008 to 2012?

I can't find anything on BOL that indicates I need to rebuild indexes or statistics after the migration and compatibility level upgrade of a database. But I have read a few articles/answers around the ...
-1
votes
2answers
2k views

database Compatibility 90 to 100 changing causes any problem

We are planning to change Compatibility level of databases from 90 to 100. Would this cause any impact on application side? If yes then please guide me, what steps or measures we need to look upon ...
0
votes
1answer
325 views

Compatibility level when upgrading a SQL Server 2005 database to SQL Server 2012

I would like to move my databases which are currently running on SQL Server 2005 to SQL Server 2012, using the Copy Database Wizard in SQL Server Management Studio. Should I first set the database ...
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 ...
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 ...
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 ...
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
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 ...
2
votes
1answer
289 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 ...
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)...
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
votes
1answer
2k views

Is my SQL Server 2000 connection string compatible with SQL Server 2012?

Currently I have an application in Visual Basic 6. This application connects to a SQL Server 2000 server using a Data Link file (.UDL) through the OLE DB provider for SQL. The server will be ...