Issue: 5

Issue: 5, Non-integer constants are not allowed in the ORDER BY clause in 90 or later compatibility modes

Upgrade Advisor detected the use of noninteger constants in the ORDER BY clause of one or more statements. Noninteger constants are allowed (and ignored) in the ORDER BY clause when the database compatibility mode is set to 80 and earlier. Noninteger constants in the ORDER BY clause will cause the statement to fail when the database compatibility mode is set to 90 or later.

Corrective Action

When you upgrade to SQL Server 2008, user databases maintain their compatibility mode. Before you change the database compatibility mode to 90 or later, modify statements that use noninteger constants in the ORDER BY clause to use a column name or column alias, or a nonnegative integer representing the position of the name or alias in the select list.

The following example uses a noninteger constant in the ORDER BY clause of a query and an equivalent query that uses a valid ORDER BY expression.

Issue

USE pubs

SELECT au_id, au_name as ‘a’

FROM authors

ORDER BY ‘a’

Workaround

USE pubs

SELECT au_id, au_name as ‘a’

FROM authors

ORDER BY a

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s