Saturday, April 22, 2006

TSQL - Database Compatibility Mode

TSQL to set database compatibility mode.
The statement cannot be run when other users are connected, hence the 1st & 3rd lines -
ALTER DATABASE [databasename] SET SINGLE_USER;
EXEC sp_dbcmptlevel [databasename], 90;
ALTER DATABASE [databasename] SET MULTI_USER;

This statement cannot be run from dynamic sql though -

Msg 15432, Level 16, State 1, Procedure sp_dbcmptlevel, Line 28
Stored procedure 'sys.sp_dbcmptlevel' can only be executed at the ad hoc level.

No comments: