Sql-server – Moving to version 2016 from 2008 and Breaking Change Alert from Upgrade Advisor

sql serversql-server-2008sql-server-2016upgrade

DBMS: SQL Server

Upgrading from version 2008 to 2016.

I have this part of the code that is being flagged by the Upgrade adviser as Breaking Change.

Error: uses a constant expression which are not allowed in the ORDER BY clause in database compatibility level 90 or later

What am I missing here. To my understanding, I should not use constants here but like variables. Any help would be appreciated.

    DECLARE ForeignColumn1 CURSOR LOCAL STATIC FOR
    SELECT 
      a.COLUMN_NAME
    , ROW_NUMBER() OVER (ORDER BY a.COLUMN_NAME) AS RowNumber
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE a
    WHERE 1=1
    AND a.CONSTRAINT_NAME = @Constraint
    AND a.TABLE_NAME IS NOT NULL
    AND a.COLUMN_NAME NOT LIKE
    CASE 
        WHEN @SkipSite > 0 THEN 
             @CurrSite
        ELSE
             ''
        END
    ORDER BY RowNumber

Best Answer

Going from 2008 to 2016 is jumping too far.

I suggest you to go move to 2012 first and then to 2016.

First of all download Microsoft Migration Assistant and try to spot incompatibilities.
Then download Microsoft Assessment and Planning Toolkit and scan for all sort of incompatibilities following a guide.
Spot the incompatible queries using the Query Tuning Assistant

Now that you are on 2012 repeat the steps 1, 2 and 3 and move to 2016.

Related Solutions

Sql-server – SQL Server 2008 upgrade advisor tool

The upgrade advisor will work, but you need to make sure to analyze a trace file of activity. Just pointing it to the database and running it will only analyze the TSQL code stored in the database and not any TSQL code within your application.

In addition to the upgrade advisor, you can run a Profiler trace to capture the events: "Depreciation Final Support" and "Depreciation Announcement". Both will identify code that will need to be evaluated because of syntax or reference to objects that don't exist in new version.

Check out Books Online for a decription of each of those events: Depreciation Event Category.

Mysql – Steps to upgrade MySQL from version 5.0 to version 5.5

Step 01 : Run this script to Dump Everything from MySQL 5.0

cd /root
MYSQL_CONN="-uroot -p..."
SQLSTMT="SELECT schema_name FROM information_schema.schemata WHERE"
SQLSTMT="${SQLSTMT} WHERE schema_name NOT IN"
SQLSTMT="${SQLSTMT} ('information_schema','mysql','performance_schema')"
MYSQL_OPTIONS="--skip-column-names -A"
mysql ${MYSQL_CONN} ${MYSQL_OPTIONS} -e"${SQLSTMT}" > /tmp/dblist.txt
DB_OPTIONS="--databases"
for DB in `cat /tmp/dblist.txt` ; do DB_OPTIONS="${DB_OPTIONS} ${DB}"
MYSQLDUMP_OPTIONS="--routines --triggers ${DB_OPTIONS}"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} > MySQLData.sql
SQLSTMT="SELECT CONCAT('SHOW GRANTS FOR ''',user,'''"
SQLSTMT="${SQLSTMT}@''',host,''';') FROM mysql.user WHERE user<>''"
mysql ${MYSQL_CONN} ${MYSQL_OPTIONS} -e"${SQLSTMT}" > GetGrants.sql
mysql ${MYSQL_CONN} ${MYSQL_OPTIONS} < /root/GetGrants.sql | sed 's/$/;/g' > Grants.sql
rm -f GetGrants.sql

Step 02 : Backup config file

cp /etc/my.cnf /etc/my.cnf.50

Step 03 : Backup /var/lib/mysql

mv /var/lib/mysql /var/lib/mysql50

Step 04 : Uninstall MySQL 5.0

Step 05 : Install MySQL 5.5

Step 06 : service mysql start

Step 07 : Login to mysql

mysql -uroot

Step 08 : At the mysql prompt enter the following:

mysql> source /root/Grants.sql
mysql> source /root/MySQLData.sql

That's it.

Give it a Try !!!

I have suggested this before : MySQL upgrade 5.0.88 to latest

Best Answer

Related Solutions

Sql-server – SQL Server 2008 upgrade advisor tool

Mysql – Steps to upgrade MySQL from version 5.0 to version 5.5

Related Question