PowerShell at Michiana PASS User Group Meeting

Post published:January 30, 2020
Post category:SQL Server

Issues during Presentation

I had an issue during the demo of this presentation on Gathering Data for Trending using PowerShell and I indicated that I would blog the solution. I found the solution. First the problem.

Problem Code in v3.0

$dbobj = New-Object [PSObject] -Property ([ordered]@{ 
           ID = $db.ID 
           Name = $db.Name 
           Size = $db.Size 
           DataSpaceUsage = $db.DataSpaceUsage 
           IndexSpaceUsage = $db.IndexSpaceUsage 
           LastBackupDate = $db.LastBackupDate 
           RecoveryModel = $db.RecoveryModel 
           PageVerify = $db.PageVerify 
           CompatibilityLevel = $db.CompatibilityLevel 
           IsReadCommittedSnapshotOn = $db.IsReadCommittedSnapshotOn 
           IterationId = 2 
} )

In PowerShell 3.0 and above the syntax changed and in the latest versions, the code stopped working and now has to be different. Now the solution.

Code that works in v5.1

$dbobj = [PSCustomObject][ordered]@{ 
         ID = $db.ID 
         Name = $db.Name 
         Size = $db.Size  
         DataSpaceUsage = $db.DataSpaceUsage 
         IndexSpaceUsage = $db.IndexSpaceUsage 
         LastBackupDate = $db.LastBackupDate 
         RecoveryModel = $db.RecoveryModel 
         PageVerify = $db.PageVerify 
         CompatibilityLevel = $db.CompatibilityLevel 
         IsReadCommittedSnapshotOn = $db.IsReadCommittedSnapshotOn 
         IterationId = 2 
}

From here things just worked for the PowerShell window but seemed to be a problem in Azure Data Studio, but it may have been able to be resolved with restarting the shell. It still threw an error after restarting the shell, but then it executed fine.

The presentation files are here on my Presentations on Github.

Continue the quest to become a PowerShell DBA.

Tags: DBA, PowerShell, Trending

Leave a Reply Cancel reply