Change SQL Server Compatility Level to 110

[Brian Lewis]

This makes the function TRY_CONVERT available for use; which will help to get more robust uploads from Excel.

[Brian Lewis]

All databases on KEMIS, FEDEMIS and MIEMIS servers with Compatibility_Level 100 have been upgraded to 110.

Development machines should be changed in the same way.

‌

Run this script to generate script to upgrde any eligible database:

select name, compatibility_level  
FROM sys.databases
select replace('ALTER DATABASE %%% SET COMPATIBILITY_LEVEL = 110', '%%%', name) from sys.databases where compatibility_level < 110

Run each of the generated ALTER DATABASE statements ( names are based on current server):

ALTER DATABASE KEMIS SET COMPATIBILITY_LEVEL = 110
ALTER DATABASE miemis SET COMPATIBILITY_LEVEL = 110

‌

Then, in each Pacific EMIS database that has been upgraded run this script to complete the upgrade: THIS IS CRITICAL!

use miemis -- database to change
go
ALTER TABLE dbo.por REBUILD
GO
ALTER INDEX ALL ON dbo.por REBUILD
GO

ALTER TABLE dbo.Student_ REBUILD
GO
ALTER INDEX ALL ON dbo.Student_ REBUILD
GO
ALTER TABLE dbo.TeacherIdentity REBUILD
GO
ALTER INDEX ALL ON dbo.TeacherIdentity REBUILD
GO
-- these queries establish the affected tables are again accessible:
Select top 100 * from por
Select top 100 * from Student_
Select top 100 * from TeacherIdentity

Select count(*) por from por
Select count(*) Student from Student_
Select count(*) TeacherIdentity from TeacherIdentity

Now this statement:

select name, compatibility_level  
FROM sys.databases

should show all databases have compatibility level at least 110. Note that the Compatibility Level of non-Pacific EMIS databases will depend on the version of SQL server in which it is created.

You can see the compatibility level in SSMS: before these changes you’ll see:

After update:

‌

[Brian Lewis]

Changes applied on live servers