How to Set Compatibility Level for SQL Server 2008 or 2005 Database Kako nastaviti Kompatibilnost ravni za SQL Server 2008 ali 2005 Database

When selecting data or performing query on Microosft SQL Server 2008 or SQL Server 2005 database, the following error message or failure may be returned: Pri izbiri podatkov ali opravljanje poizvedb o Microosft SQL Server 2008 ali SQL Server 2005 podatkovne baze, naslednje sporočilo o napaki ali odpoved se lahko vrne:

[Microsoft][ODBC SQL Server Driver][SQL Server][Msg 4147, Level 15, State 1, Line 4] The query uses non-ANSI outer join operators (”*=” or “=*”). [Microsoft] [ODBC SQL Server Driver] [SQL Server] [Sporočilo 4147, Level 15, članica 1, vrstica 4] query uporab niso ANSI pridružijo zunanji subjekti ("*=" ali "=*"). To run this query without modification, please set the compatibility level for current database to 80, using the SET COMPATIBILITY_LEVEL option of ALTER DATABASE. Če želite izvesti to vprašanje brez sprememb, prosimo, nastavite stopnjo združljivosti za sedanje baze podatkov do 80, z uporabo SET COMPATIBILITY_LEVEL možnost spreminjati baze podatkov. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). To je zelo priporočljivo znova poizvedbo z uporabo ANSI pridružijo zunanji subjekti (LEVI ZUNANJI JOIN, RIGHT JOIN ZUNANJI). In the future versions V prihodnje različice
of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes. SQL Server, ne ANSI pridružiti operaterji ne bodo podprti tudi v združljivost nazaj načine.

or, ali,

Msg 4147, Level 15, State 1, Line 3 Msg 4147, Level 15, članica 1, vrstica 3
The query uses non-ANSI outer join operators (”*=” or “=*”). Poizvedba uporablja brez ANSI pridružijo zunanji subjekti ("*=" ali "=*"). To run this query without modification, please set the compatibility level for current database to 80 or lower, using stored procedure sp_dbcmptlevel. Če želite izvesti to vprašanje brez sprememb, prosimo, nastavite stopnjo združljivosti za sedanje baze podatkov s 80 ali nižje, z uporabo shranjeni postopek sp_dbcmptlevel. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). To je zelo priporočljivo znova poizvedbo z uporabo ANSI pridružijo zunanji subjekti (LEVI ZUNANJI JOIN, RIGHT JOIN ZUNANJI). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes. V prihodnje različice SQL Server, ne ANSI pridružiti operaterji ne bodo podprti tudi v združljivost nazaj načine.

The error happens because Microsoft has dropped support for and eliminated old-style *= and =* outer join operators. Ta napaka se zgodi, ker Microsoft je padla za podporo in odpraviti starih in *= =* pridružijo zunanji izvajalci.

In order to fix the SQL Server error above, DB administrator has to set the database compatibility level to 80, which equivalent with SQL Server 2000 as a temporary workaround. Da bi določili SQL Server error zgoraj, DB administrator je določiti podatkovno zbirko združljivost ravni do 80, ki je enakovreden z SQL Server 2000 kot začasna rešitev. Of course, the best solution is to modify the SQL statements or queries code to remove the old-style join operators with current standard SQL join syntax. Seveda, je najboljša rešitev, da spreminjanje SQL izjave poizvedbe ali koda za odstranitev starih pridružiti operaterji s sedanjo standardno SQL syntax pridružiti.

For example, SQL query below is not valid: Na primer, SQL query spodaj ni veljaven:

SELECT o.name FROM sys.objects o, sys.views v WHERE o.object_id *= v.object_id;

And should be replaced with the following code: In naj se nadomesti z naslednjo kodo:

SELECT o.name FROM sys.objects o LEFT JOIN sys.views v ON o.object_id = v.object_id;

Or the following abbreviated inner join statement: Ali po skrajšani Notranje pridružiti izjavo:

SELECT o.name FROM sys.objects o, sys.views v WHERE o.object_id = v.object_id;

For administrator who can't change the code, the easiest workaround is to revert the compatibility level of the database in order to provide the backward compatibility to old-style joins. Za skrbnika, ki se ne morejo spremeniti kodo, najlažje rešitev je, da se vrne združljivosti ravni baze podatkov, da se zagotovi združljivost nazaj na starih pridruži. sp_dbcmptlevel stored procedures (deprecated) or Transact-SQL can be used to set certain (partially, not all) database behaviors to be compatible with the earlier version of SQL Server. sp_dbcmptlevel shranjeni postopki (deprecated) ali Transact-SQL se lahko uporablja za določitev nekaterih (delno, ne vse), podatkovne baze obnašanja mora biti združljiva s starejšo različico SQL Server. This To MSDN article MSDN članek provides overview on what behaviors are changed on each compatibility level. ponuja pregled o tem, kaj sprememba vedenja so na vsaki ravni združljivosti.

Version of SQL Server that can be reverted to can be one of the following: Različica za SQL Server, ki se lahko obrnil, da se lahko eno od naslednjih navedb:

60 = SQL Server 6.0 60 = SQL Server 6,0
65 = SQL Server 6.5 65 = SQL Server 6,5
70 = SQL Server 7.0 70 = SQL Server 7,0
80 = SQL Server 2000 80 = SQL Server 2000
90 = SQL Server 2005 90 = SQL Server 2005
100 = SQL Server 2008 100 = SQL Server 2008

Note: Compatibility level 60, 65, and 70 no longer available in SQL Server 2008. Opomba: Kompatibilnost ravni 60, 65 in 70 ni več na voljo v SQL Server 2008. And future version of SQL Server will support only two (2) prior version of backward compatibility. In prihodnje različice SQL Server bo podpiral samo dva (2) pred različico povratno združljivost. A database containing an indexed view cannot be changed to a compatibility level lower than 80. Podatkovna baza vsebuje indeksiran mnenju ni mogoče spremeniti na združljivost ravni nižja od 80. Do also take note that when a database is set to backward-compatibility mode, some of the new functionalities may be lost, such as SQL CLR support and SSMS diagrams for the database. Ali upošteva tudi, da kadar bazo podatkov je, da nazaj združljivostnem načinu, v nekaterih novih funkcij se lahko izgubijo, kot so na primer SQL CLR podporo in SSMS diagramov za baze podatkov. Beside, the compatibility mode affects behaviors only for the specified database, not for the entire server. Poleg tega, združljivost način vedenja vpliva le za določene baze podatkov, ne pa za celoten strežnik.

Steps to Change and Set Compatibility Level of A Database in SQL Server Korakom, da spremenite in nastavite združljivostni ravni baze podatkov na SQL Server

The following SQL commands can be issued in SQL Server Management Studio Query window. Naslednje SQL ukaze lahko izda v SQL Server Management Studio Query window. Remember to execute Go after each command. Ne pozabite, da izvrši Pojdi po vsakem ukaz.

1. Optional: Set the database to single user access mode: Neobvezno: Določite zbirko podatkov za dostop enemu uporabniku Način:

   ALTER DATABASE database_name SET SINGLE_USER;

2. Set the database compatibility level to one of the earlier version stated above: Nastavite bazo podatkov združljivost ravni na eno od prejšnje različice navedeno:

   Transact-SQL Method Transact-SQL Metoda

   ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = { 80 | 90 | 100 }

   For example, Na primer,

   ALTER DATABASE my_db SET COMPATIBILITY_LEVEL = 80 Alter zbirki my_db SET COMPATIBILITY_LEVEL = 80

   sp_dbcmptlevel Stored Procedure Method sp_dbcmptlevel Stored Procedure Metoda

   EXEC sp_dbcmptlevel database_name, compatibility_level value;

   For example, Na primer,

   EXEC sp_dbcmptlevel my_db, 90; Exec sp_dbcmptlevel my_db, 90;

3. Optional: Put back the database in multiuser access mode: Neobvezno: Pot nazaj v bazo podatkov v Višekorisničko Način dostopa:

   ALTER DATABASE database_name SET MULTI_USER;

IMPORTANT : The page is machine translated and provided "as is" without warranty. POMEMBNO: Ta stran je stroj prevod in če "kot je" brez garancije. Machine translation may be difficult to understand. Strojno prevajanje je lahko težko razumeti. Please refer to Prosimo, da original English article original English članek whenever possible. kadar je to mogoče.

Related Articles Sorodni članki

• Uninstall and Remove Multiple Database Instances of Microsoft SQL Server 2005 Odstranite in Odstrani Multiple Zbirka Primeri Microsoft SQL Server 2005
• Microsoft DreamSpack Offers Free Download and License of Visual Studio 2008, Server 2003, SQL Server 2005 and More Microsoft DreamSpack Offers Free Download in licenca za Visual Studio 2008, Server 2003, SQL Server 2005 in več
• Optimize SQL Server 2000, 2005 or 2008 in Large RAM System by Locking Pages in Memory and AWE Optimiraj SQL Server 2000, 2005 ali 2008 v Veliki RAM sistema Zaskočenje Strani v pomnilnik in AWE
• Download Update for Windows 7 and Server 2008 R2 Beta (KB967062) to Fix Common Application Compatibility Issue Prenesite posodobitve za Windows 7 in Server 2008 R2 Beta (KB967062) določiti skupne Application Compatibility Issue
• Error Has Occurred While Establishing A Connection To SQL Server 2005 Which Does Not Allow Local and Remote Connections Čeprav je prišlo do napake Vzpostavitev povezave Za SQL Server 2005, ki ne omogoča lokalno in daljinsko Connections
• Microsoft Virtual Server 2005 R2 Download Available for Free Microsoft Virtual Server 2005 R2 Download na voljo za brezplačno
• Check System Compatibility Via Microsoft New Application Compatibility Toolkit Version 5.5 Preverite System Compatibility Via Microsoft Application Compatibility Toolkit Nova različica 5.5
• Microsoft Virtual Server 2005 R2 SP1 (Enterprise Edition) Direct Download Links Microsoft Virtual Server 2005 R2 SP1 (Enterprise Edition) Direct Download Povezave
• Internet Explorer (IE) Hang When Opening Virtual Server 2005 Web Administration Site Internet Explorer (IE) Počakaj pri odpiranju Virtual Server 2005 Web Site Administration
• Change Oracle Database User Password Spremeni Oracle Database Uporabnik Geslo

This entry was posted on Sunday, May 17th, 2009 at 2:43 am and is filed under Ta vnos je bil objavljen na nedelja, 17. maj 2009 ob 2:43 in je vložena v skladu s Databases Podatkovne baze . . You can follow any responses to this entry through the Lahko sledenje vseh odzivov na to vstop skozi RSS 2.0 RSS 2.0 feed. You can krme. Lahko leave a response pustite odgovor , or , Ali trackback Sledilnik from your own site. iz vaše strani.