Changing Compatibility Level

keithr

Is there a way use the version settings of the SDK Database object to update the compatibility level of a database before comparing?

We have several clients that use our software and migrate from various versions of SQL Server. As they migrate, their databases aren't always updated to the compatibility level of the server, so newer functionality causes conversion failures. (i.e recently one failed because the database was set to SS 2000)

I was hoping to use something like the code below to update it to the server level before comparing. I do have an script that could be applied, but wanted to see if the was an API option first. MajorVersion doesn't have an accessible setter, so the code below isn't viable.

Thanks for your help (and great product)

If oDB.SqlServerVersion <> oDB.MajorVersion Then
      oDB.MajorVersion = oDB.SqlServerVersion
End If

Allen LeVan

Hello,

Thanks for your question on the SDK and compatibility version levels. For the server version we have a set and get method but for the compatibility we only provide get and don't control this property. As the API stands I don't believe there is any way to do this, sorry for the bad news.

keithr

Thanks for the update.

I think I am able to work around the issue. I am using the query below to modify the type. Converting the ServeMajorVersion to SqlServerVersion seems to get me the compatibility level number I need without alot of parsing.

String.Format("ALTER DATABASE {0} SET COMPATIBILITY_LEVEL = {1}0;", DatabaseName, CType(oDB.ServerMajorVersion, SqlServerVersion))