none
Policy-Based Management: Compatability Level IN Version100, Version110

    Question

  • I am trying to create a condition to test the compatability level of a database to ensure that it is either Version100 or Version110 that will work on either a SQL2008R2SP2CU3 or SQL2012 server.

    I have tried:

    @CompatabilityLevel IN Array(Version100,Version110)

    @CompatabilityLevel IN Array('Version100','Version110')

    But I get the error "Requested value 'Array(Version100,Version110)' was not found

    or

    But I get the error "Requested value 'Array('Version100','Version110')' was not found

    Friday, December 07, 2012 10:35 PM

Answers

  • Field

    executesql(Numeric,'select Count(1) from sys.databases where name = db_name() and compatibility_level not in(110,100)')


    Operator

    =

    Value

    1

    http://blogs.msdn.com/b/sqlpbm/archive/2008/07/03/executesql.aspx


    Manish


    Friday, December 07, 2012 11:57 PM
  • Thank you.  I appreciate your help.  However I came up with a slightly different approach:

    Facet: Server; Field: @VersionString; Operator: =; Value '10.50.4266.0'

    or

    Facet: Server; Field: @VersionString; Operator: =; Value '11.0.2100'

    Saturday, December 08, 2012 12:17 AM

All replies

  • Better to execute Executesql

    select  ''Version''+convert(varchar,compatibility_level) from sys.databases where name = ''master''


    Manish

    Friday, December 07, 2012 11:12 PM
  • OK, I am not against that solution, but if I do that, being new to policy based management, how do I run that for each DB, or do I have to wrap it in sp_foreachdb?
    Friday, December 07, 2012 11:20 PM
  • Field

    executesql(Numeric,'select Count(1) from sys.databases where name = db_name() and compatibility_level not in(110,100)')


    Operator

    =

    Value

    1

    http://blogs.msdn.com/b/sqlpbm/archive/2008/07/03/executesql.aspx


    Manish


    Friday, December 07, 2012 11:57 PM
  • Thank you.  I appreciate your help.  However I came up with a slightly different approach:

    Facet: Server; Field: @VersionString; Operator: =; Value '10.50.4266.0'

    or

    Facet: Server; Field: @VersionString; Operator: =; Value '11.0.2100'

    Saturday, December 08, 2012 12:17 AM