Powershell Formatting Invoke-sqlcmd

Question

I'm creating a script to grab info from multiple SQL servers an pump it to a file that users can get to and not have to call me all the time. the problem I am running into right now is formatting the output of the invoke-sql command so I can put it into a hashtable. I want to get the SQL version in one column (i.e. Microsoft SQL Server 2008) and the second column to have the build number (i.e. 10.0.5512.0). What follows is the code I've created and what it outputs. As you can see I get the info but cannot put it into a hashtable. It just puts ... in the table.

$SQLVerPart1= Invoke-Sqlcmd -Query "select @@VERSION;" -ServerInstance $Server | Out-String | ForEach-Object { $_ -replace '-.*'}

$SQLVerPart2 = Invoke-Sqlcmd -Query "select SERVERPROPERTY ('productversion')" -ServerInstance $Server | Out-String 

$SQLVerPart1 | Out-File -Append $Log
$SQLVerPart2 | Out-File -Append $Log
$SQLVer.add($SQLVerPart1,$SQLVerPart2)

OUTPUT:

Column1

Microsoft SQL Server 2008 (SP3)

Column1

10.0.5512.0

Name Value

---- ----- ... ...

KevinD · Accepted Answer · 2014-02-13 19:40:27Z

2

You want to try to avoid converting to strings too early. The Out-String in the variable assignments are including the Column1 heading for each object. Get the raw properties like this:

$SQLVerPart1 = (Invoke-Sqlcmd -Query "select @@VERSION" -ServerInstance $Server).Column1 -replace "\n", "" -replace '-.*' 
$SQLVerPart2 = (Invoke-Sqlcmd -Query "select SERVERPROPERTY ('productversion')" -ServerInstance $Server).Column1

As for the hash table, are you sure that's what you want? If you have two instances of "Microsoft SQL Server 2008 (SP3)", the second Add will fail. Perhaps you want an array of objects instead. Something like:

$SQLVer = $ListOfServers | ForEach-Object {
  $SQLVerPart1 = (Invoke-Sqlcmd -Query "select @@VERSION" -ServerInstance $_).Column1 -replace "\n", "" -replace '-.*' 
  $SQLVerPart2 = (Invoke-Sqlcmd -Query "select SERVERPROPERTY ('productversion')" -ServerInstance $_).Column1

  New-Object -Type PSObject -Property @{ Version = $SqlVerPart1; BuildNumber = SqlVerPart2 }
}

Then you can worry about logging it or outputting it after you've collected the data:

$SQLVer | Out-String

answered Feb 13, 2014 at 19:40

KevinD

3,1732 gold badges24 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David W Over a year ago

That's exactly what I needed. The second part works a charm, but I'm having trouble deciphering it. As far as the hash table goes, I'm simply looping through each of my servers and dropping the version info at the beginning before I get into other stuff such as databases, sizes, owners, etc. so I think the first part you gave me will do just fine. Thanks for the quick response!

genesys · Accepted Answer · 2014-02-13 19:01:04Z

0

I do something similar... with my script. This wont get you 100% of the way there, but it should help get you going in the direction you want.

For my SQL Server audit script, I run these four commands:

$ProductVersion = Invoke-Sqlcmd2 -Query "SELECT SERVERPROPERTY('productversion')" -ServerInstance $InstanceName
$ProductEdition = Invoke-Sqlcmd2 -Query "SELECT SERVERPROPERTY('edition')" -ServerInstance $InstanceName
$CurrentTime = Invoke-Sqlcmd2 -Query "SELECT GETDATE()" -ServerInstance $InstanceName
$UserName = Invoke-Sqlcmd2 -Query "SELECT SUSER_SNAME()" -ServerInstance $InstanceName

Later, I create a hash table like so:

$hashPropsFormalities = @{
    "Instance Name" = $InstanceName
    "Product Version" = $ProductVersion[0]
    "Product Edition" = $ProductEdition[0]
    "User Name" = $UserName[0]
    "Current Time" = $CurrentTime[0]
}

And then print the the hash table to the console:

$hashPropsFormalities | FT -HideTableHeaders

In order to capture a copy of the console output in a text file, I use Start-Transcript (and it's counterpart, Stop-Transcript).

answered Feb 13, 2014 at 19:01

genesys

3201 gold badge4 silver badges12 bronze badges

1 Comment

David W Over a year ago

Thanks. I'll hang onto this code for future ref. Both are excellent answers.

Collectives™ on Stack Overflow

Powershell Formatting Invoke-sqlcmd

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related