Teams Collaboration

Setting SQL Server Database Compatibility level for all databases

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).

  1. In SQL Server Studio Manager set the query output to text
  2. Create a new query window
  3. Excute the following script
    select ‘ALTER DATABASE ‘ + rtrim(INTERID) + ‘ SET COMPATIBILITY_LEVEL = 110’ from DYNAMICS..SY01500
    union
    select ‘ALTER DATABASE ‘ + ‘DYNAMICS’ + ‘ SET COMPATIBILITY_LEVEL = 110’
  4. Copy the results and paste into a new SQL Query Window
  5. Execute the new query window.
  6. All of the GP Databases should be updated to the new compatibility level.

If you are updaing to SQL Server 2008 change 110 to 100.

Share this post

Related Posts

KTL Solutions Makes Inc. 5000 List for 2022

Each year, Inc. Magazine comes out with its list of the fastest-growing private companies in the nation. The 2022 list includes 131 Maryland businesses, with 11 of them making the prestigious Top 500. Those 131 MD-based businesses represent $2.7 billion in total revenue and more than 10,500 jobs added.

Read More »