Get-itemproperty

I am trying to get teh correct syntax for this

(Get-ItemProperty -Path ‘Registry::HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server’ )
this works and shows all the ps attributes but I need to add the below as I need the instance names as I am trying to retrieve them for the bit code
-Name InstalledInstances).InstalledInstances

I don’t have any SQL servers to test with, but our the instances registry keys (the folders in regedit) or values (data points in right hand side)?

Keys are retrievable with Get-Item whereas values are returned from Get-ItemProperty on the target Key

MattNad,
Welcome to the forum. :wave:t3:

When you post code, sample data, console output or error messages please format it as code using the preformatted text button ( </> ). Simply place your cursor on an empty line, click the button and paste your code.

Thanks in advance

How to format code in PowerShell.org 1 <---- Click :point_up_2:t4: :wink:

( !! Sometimes the preformatted text button hides behind the settings gear symbol. :wink: )

I wonder that this works … actually you should get an error … at least I get one when I use your syntax … there is a colon where it does not belong.

This should do the trick:

(Get-ItemProperty -Path 'Registry::HKLM\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances

Well the change but stll getting nothing in the results

$sqlinstances = (Get-ItemProperty -Path ‘Registry::HKLM\SOFTWARE\Microsoft\Microsoft SQL Server’).InstalledInstances
then
$sqlinstances no results generated

Hmm … I tried it on 5 different servers and it worked every single time just fine. :man_shrugging:

I also just picked a server at random that has MS SQL installed and running this:

PS> (Get-ItemProperty "hklm:\software\microsoft\Microsoft SQL Server").InstalledInstances
MSSQLSERVER

yielded output. I did use a slightly modified path as “HKLM:” is already present as a PSDrive for accessing the registry.

1 Like

… as expected … :man_shrugging: :+1: :wink:

How you specify the path actually does not matter … that’s all the same:

(Get-ItemProperty -Path 'Registry::HKLM\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances
(Get-ItemProperty -Path 'Registry::HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances
(Get-ItemProperty -Path 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances

So there might be another issue on @MattNad’s systems. :man_shrugging:

1 Like

Like maybe there are no instances :slight_smile: Just playing devils advocate. When I try the commands, I get nothing as I dont have SQL installed.

How I got the list

Get all SQL Server instances

$SqlInstances = Get-Service | Where-Object { $.Name -like ‘MSSQL*’ } |
Select-Object @{Name=“InstanceName”; Expression={
if ($
.Name -eq “MSSQLSERVER”) {
“MSSQLSERVER” # Default instance doesn’t include a specific instance name
}
else {
$_.Name.Substring(6) # Extract instance name (skip ‘MSSQL$’)
}
}}

When you post code, sample data, console output or error messages please format it as code using the preformatted text button ( </> ). Simply place your cursor on an empty line, click the button and paste your code.

Thanks in advance

Guide to Posting Code - Redux 1 <---- Click :point_up_2:t4: :wink:

( !! Sometimes the preformatted text button hides behind the settings gear symbol. :wink: )

I don’t get it. You’ve been asking about how to get a property value from a registry key, and most recently store it in a variable called $sqlinstances.

But you say that’s not returning anything for you, when others have tested and at least in our instances it works. Then you say “how I got the list” but the code you provided doesn’t look at the registry it pulls the Services on the machine you run the code on.

What exactly are you trying to accomplish in total because we’ve already provided the correct syntax for your original question.

I always got the Instance name using the provider, because I find it more reliable than the registry.
I do not need to know the version number or anything ahead of time.

Import-Module SQLPS
$InstanceName = Get-ChildItem SQLSERVER:\SQL\$ENV:COMPUTERNAME | Select-Object -ExpandProperty DisplayName
$InstanceName

You can keep recusrsing from there and do just about anything you would want to do in SQL using the provider. It is slower but more powerfull in my mind.

Set-Location -Path SQLSERVER:\SQL\$ENV:COMPUTERNAME
Set-Location $InstanceName
Set-Location Databases
Get-ChildItem

Note: that you have to set location in SQL you can not do recursive Get-ChildItem queries. You have to be in the location and then run Get-ChildItem. If there are no databases you will get an error there. Also if you are not a member of builtin\admins you will nto be able to see the databases.

You could also use a foreach loop in the event that there is more than one instance of SQL running.