Microsoft.SqlServer.Management.Smo.Server Version and Status...etc

Category: sql server smodmo

Question

John Couch on Wed, 24 Oct 2012 21:37:33


I am trying to output the values from each SQL Server into an Excel Spreadsheet using powershell. below is the initial portion of the script, and the instance name shows, but the remaining values return nothing. Status says Offline, even though the instance is online. Not sure what I am doing wrong. Anyone have an idea? Just looking to try and get the status and version of the SQL Server Instance.

            $Connection = new-object ("Microsoft.SqlServer.Management.Common.ServerConnection")
            $Connection.ServerInstance = $Instance
            $Connection.LoginSecure = $true
            $Connection.Connect()
            
            if ($Connection.IsOpen -eq $false)
            {
                throw 'Unable to connect to SQL Server Instance (' + $Instance + ')'
            }
            
            $smoInstance = new-object ("Microsoft.SqlServer.Management.Smo.Server") $Connection
            $smoInstance.ConnectionContext.StatementTimeout = 65534              

            $WorkSheet.Cells.Item($Row, 1) = "Instance:"
            $WorkSheet.Cells.Item($Row, 1).HorizontalAlignment = $xlConstants::xlRight
            $WorkSheet.Cells.Item($Row, 1).Font.Bold = $true
                        
            $WorkSheet.Cells.Item($Row, 2) = $smoInstance.Name
            $WorkSheet.Cells.Item($Row, 2).HorizontalAlignment = $xlConstants::xlCenter
            $WorkSheet.Cells.Item($Row, 2).Font.Bold = $true
            
            $WorkSheet.Cells.Item($Row, 3) = ""
            
            $WorkSheet.Cells.Item($Row, 4) = "Status:"
            $WorkSheet.Cells.Item($Row, 4).HorizontalAlignment = $xlConstants::xlRight
            $WorkSheet.Cells.Item($Row, 4).Font.Bold = $true
                        
            $WorkSheet.Cells.Item($Row, 5) = $smoInstance.Status
            $WorkSheet.Cells.Item($Row, 5).HorizontalAlignment = $xlConstants::xlCenter
            $WorkSheet.Cells.Item($Row, 5).Font.Bold = $true
            
            $WorkSheet.Cells.Item($Row, 6) = ""
            
            $WorkSheet.Cells.Item($Row, 7) = "Version: "
            $WorkSheet.Cells.Item($Row, 7).HorizontalAlignment = $xlConstants::xlRight
            $WorkSheet.Cells.Item($Row, 7).Font.Bold = $true
                        
            $WorkSheet.Cells.Item($Row, 8) = $sqlserver.Version
            $WorkSheet.Cells.Item($Row, 8).HorizontalAlignment = $xlConstants::xlCenter
            $WorkSheet.Cells.Item($Row, 8).Font.Bold = $true

Replies

yaphets on Fri, 26 Oct 2012 08:28:49


anyone can help?

John Couch on Fri, 26 Oct 2012 11:14:22


OK, so I had a typo:

$WorkSheet.Cells.Item($Row, 8) = $sqlserver.Version $WorkSheet.Cells.Item($Row, 8).HorizontalAlignment = $xlConstants::xlCenter $WorkSheet.Cells.Item($Row, 8).Font.Bold = $true

SHould be:

$WorkSheet.Cells.Item($Row, 8) = $smoInstance.Version
            $WorkSheet.Cells.Item($Row, 8).HorizontalAlignment = $xlConstants::xlCenter
            $WorkSheet.Cells.Item($Row, 8).Font.Bold = $true

Now the Version shows up, but the status still says offline when it should say Online. 

Papy Normand on Sun, 28 Oct 2012 12:29:56


Hello,

I have tested with my own SQL Server 2008 Developer ( it is in french , this explains why the labels are in french )

In my SQL Server Management Studio :

- right-click on the SQL Server name in the right-panel

- in the conceptual menu, select Facets

- in the new form, select the Server facet

You will see the status of the server is offline

I have looked at :

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.serverstatus(v=sql.105).aspx

and i discovered this little line "This enumeration has a FlagsAttribute attribute that allows a bitwise combination of its member values"

Maybe , it is a problem of displaying the value of an enumeration reporting several values.

Have a nice day