4

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  
  • 2
    This looks like a bogus warning to me with the deprecated Upgrade Advisor. If you only need static analysis, consider using the [Data Migration Assistant](microsoft.com/en-us/download/details.aspx?id=53595 ) instead. – Dan Guzman Oct 14 '18 at 23:19
  • yes you are correct. The original code contained ORDER by 'RowNumber' and (ORDER BY a.COLUMN_NAME) AS 'RowNumber'. I ended up replacing 'RowNumber' with [RowNumber] and the analysis didn't throw any alerts. ---- Appreciate your help – Rizwan Oct 15 '18 at 13:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.