Setting the database compatibility level for a couple of SQL Server databases can be a simple task. But what if you had 30 or 40 databases? For those of you with a lot of databases, he is a simple step by step process to update the compatibility level to version 11 (SQL Server 2012).
- In SQL Server Studio Manager set the query output to text
- Create a new query window
- Excute the following script
select 'ALTER DATABASE ' + rtrim(INTERID) + ' SET COMPATIBILITY_LEVEL = 110' from DYNAMICS..SY01500
select 'ALTER DATABASE ' + 'DYNAMICS' + ' SET COMPATIBILITY_LEVEL = 110'
- Copy the results and paste into a new SQL Query Window
- Execute the new query window.
- All of the GP Databases should be updated to the new compatibility level.