CODEWITHSUNDEEP
sql-serverpowershellazure-sql-databasessas
Cataster
Cataster

Reputation: 3481

How to get the compatibility level of a database from analysis server?

I searched around and found there is a way to get compatibility level of a database in powershell through something like:

    Import-Module SqlServer

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection 
$SqlConnection.ConnectionString = "Server=$Server;Initial Catalog=$DB;Integrated Security=SSPI"
$conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection($sqlConnection)
$srv = new-object Microsoft.SqlServer.Management.Smo.Server($conn) 

$db = New-Object Microsoft.SqlServer.Management.Smo.Database  
$db = $srv.Databases.Item("$DB")  

$comp_lvl = New-Object Microsoft.SqlServer.Management.Smo.CompatibilityLevel  
$comp_lvl = $db.CompatiblityLevel  
Write-Host "Compatibility level =" $db.CompatibilityLevel

However, I am getting errors trying to get compatibility level of a database that is on an analysis server

Exception getting "Item": "Failed to connect to server ..."

I realized it is probably working for a regular database engine, but something else may be used for an analysis server. I looked around on MS Docs and didnt really find anything helpful.

SO is this even possible?

UPDATE:

I was able to find something on this page: https://technet.microsoft.com/en-us/hh213141(v=sql.100)

Import-Module SqlServer

$as = New-Object Microsoft.AnalysisServices.Server  
$as.connect("$Server")  

$as.databases

Write-Host "Compatibility level ="$as.DefaultCompatibilityLevel

but this returns ALL databases back...

I want to specify just one database to get the compatibility level of...

I tried this, 

$as.databases["$Database"]

but it seems not to return the proper level, because the DB i am passing has a lvl of 1103, not 1200...

Upvotes: 0

Views: 746

Answers (1)

Cataster
Cataster

Reputation: 3481

I figured it out!

Import-Module SqlServer

$as = New-Object Microsoft.AnalysisServices.Server  
$as.connect("$Server")  

$c = $as.Databases | Where-Object { $_.ID -eq $Database }

Write-Host "Compatibility level =" $c.CompatibilityLevel

Upvotes: 1

Related Questions