SQL Server Compatibility Level

 

Error: I gave a demo to my colleague yesterday, which is about sequence usage in enhance T-SQL: Select * From privtdemo sequence (sum ([Mark]) for [course] In ([Chinese], [mathematics], [English]) as PVT usage has the following error

MSG 102, Level 15, state 1, line 1
Incorrect syntax near '('.

At that time, I also considered whether it was compatible. However, when I used two judgment bases, I began to consider whether my syntax was incorrectly used (successfully executed, which made me very depressed, you can't lose anything !)

 

1. The database is linked to an instance of SQL Server 2005 (bqcdev01 "Dev)

2. I have seen that tokens can be recognized as keywords by the development environment.

 

Cause Analysis: but the cause cannot be found.

 

What is the reason (Google)

Compatibility issues

Then I executed exec sp_dbcmptlevel callcentereis, 90 in the database with the error.

The execution is OK.

Then I executed exec sp_dbcmptlevel callcentereis, 80

An error occurs again, which indicates that the cause of the error has been determined to be a compatibility problem.

But why?

That is, the script generated by the tool generated by the SQL Server 2000 script is used in SQL Server 2005 to generate the 2005 database callcentereis, however, the SQL Server 2000 script contains some syntaxes that are frequently used only in SQL Server 2000. As a result, SQL Server 2005 sets the default Compatibility Level of the database to 80.

 

Conclusion: if you use the new T-SQL enhanced syntax in SQL Server 2005 database development, you can consider whether it is compatible if the cause cannot be found for errors.

If a database upgraded from SQL Server 2000 to SQL Server 2005 is in development, if the new syntax of SQL Server 2005 is used (XML operations, sorting functions, etc) some inexplicable errors will be reported, such as (system. data. sqlclient. sqlexception: truncates string or binary data MSG 102, Level 15, state 1, line 1 incorrect syntax near '('. ....) And cannot be checked. In fact, these errors are compatibility problems.

 

 

PS:

 

Sp_dbcmptlevel (TRANSACT-SQL)

[This topic is pre-published document content, which may be changed in future versions. Blank topics in the form of placeholders.]

Set some database behaviors to be compatible with the specified SQL Server version.

Important:

This function will be deleted in later versions of Microsoft SQL Server. Do not use this function in new development work, and modify the application that is currently using this function as soon as possible. Instead, use alter database.

Transact-SQL syntax conventions

Syntax

sp_dbcmptlevel [ [ @dbname = ] name ] 

    [ , [ @new_cmptlevel = ] version ]

Parameters

[@ Dbname =]Name

To change the name of a compatible database. The database name must comply with the identifier rules.NameThe data type of isSysnameThe default value is null.

[@ New_cmptlevel =]Version

The version of the SQL Server to which the database is compatible.VersionThe data type of isTinyintThe default value is null. This value must be one of the following values:

80= SQL Server 2000

90= SQL Server 2005

100= SQL Server 2008

Return code value

0 (successful) or 1 (failed)