Registry Keys on multiple SQL instances

Question

Post reply

Registry Keys on multiple SQL instances

MiguelSQL

Hall of Fame

Points: 3112
More actions
March 1, 2016 at 9:03 am

#305217

Hello,
I can't figure this out.... I'm on PowerShell version 2, and version 4, and just starting to learn PS.
This is a very basic code to find the location of the errorlog on the registry.
I know we can do that by connecting to SQL, but this is intended to our SysAdmin friends that don't have access to SQL (non-DBAs), and for machines that I don't support and I cant get to it, but still want to help
I want to be able to tell them "run this powershell" and send the the file!
I have the following script:
# Searches for SQL server installed on machine
$RegKey = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL??*\MSSQLServer\parameters"
# Filter -e for errorlog. -d for master file
$Filter = "-e"
# Stores a list of the SQLArg Registry Keys
$Param1 = Get-Item $RegKey | Select-Object -ExpandProperty property
# Creates an Object, and filters those starting with the filter
$Param2 = $Param1 | ForEach-Object { New-Object psobject -Property @{"property"=$_; "Value" = (Get-ItemProperty -Path $RegKey -Name $_).$_}}
# Finally, removes -e from the string
$Param3 = (($Param2 | Where-Object {$_.Value -like ($Filter+"*") }).Value).Replace($Filter,"")
New-Object psobject -Property @{"Path"=$Param3 ; "ServerName" = $env:COMPUTERNAME}
However, the code does not work when we have more than one instance installed.
The reason is that $Param1 returns an array ( SQLArg0, SQLArg1, SQLArg2, SQLArg0, SQLArg1, SQLArg2) and then when I run this part of code...:
Get-ItemProprty -Path $Regkey -Name $Param1
I get the error
Get-ItemProperty : The member "SQLArg0" is already present.
At line:1 char:17
+ Get-ItemProperty <<<< -Path $RegKey -Name $Param1
+ CategoryInfo : NotSpecified: (:) [Get-ItemProperty], ExtendedTypeSystemException
+ FullyQualifiedErrorId : AlreadyPresentPSMemberInfoInternalCollectionAdd,Microsoft.PowerShell.Commands.GetItemPro
pertyCommand
So I need to somehow loop, but I can't figure out how to loop here...
Suggestions? Resources?
Thanks
Miguel
http://www.cookingsql.com

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gary Varga SSC Guru Points: 82166 More actions · Answer 1

I am away from a computer with both PowerShell and SQL Server to running blind here.

What is the output at the command line for $Param1?

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

MiguelSQL Hall of Fame Points: 3112 More actions · Answer 2

Thank you Gary.

I made a mistake on the post, so I corrected it (edit)

If one instance, $Param1 returns an array ( SQLArg0, SQLArg1, SQLArg2)

so $Param2 returns its values, for each SQLArg key.

Value Property

-ec:\mssq\... SQLArg0

.... .......

However, if there are two instances, $Param1 returns an array ( SQLArg0, SQLArg1, SQLArg2, SQLArg0, SQLArg1, SQLArg2)

and $Param2 cannot get the values (item property value)

thanks

Gary Varga SSC Guru Points: 82166 More actions · Answer 3

It sounds like you need to loop around the $Param1 array in threes. Something a little like this:

for ($instanceIndex = 0; ($instanceIndex + 2) -lt $Param1.Count; $instanceIndex += 3)

{

$sqlInstanceInfo1 = $Param1[$instanceIndex];

$sqlInstanceInfo2 = $Param1[$instanceIndex + 1];

$sqlInstanceInfo3 = $Param1[$instanceIndex + 2];

}

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

MiguelSQL Hall of Fame Points: 3112 More actions · Answer 4

Thank you for your time, but this will not work because

#1- The SQLArg is not always in groups of three. Sometimes a server might have a start up parameter, you know, those like -T<flag> so it'll have an extra SQLArg3 SQLArg4 ,etc

#2- Then I do command Get-Item -Name I can't pass the -Name param, meaning the RegKey hive.... I can do it.

I need to grab the first Instance Hive, put in an object, and then pass the name and its property... so I can pull the info I need.

I just can't figure out how to do that

Gary Varga SSC Guru Points: 82166 More actions · Answer 5

I think it must be the way you are reading it from the registry. Are you flattening a structure? If so you might need to have you retrieval of registry data in a loop instead.

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

MiguelSQL Hall of Fame Points: 3112 More actions · Answer 6

mmm I run hte script on another server and I didn't have issues.

I think I might have an issue on the first server.

meaning my script works, but no on that server. I'll have to go back and check.

thank you for your time.

Gary Varga SSC Guru Points: 82166 More actions · Answer 7

You are most welcome. Good luck in finding the issue (BTW reporting back here might help someone else later on).

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

MiguelSQL Hall of Fame Points: 3112 More actions · Answer 8

after looking into it... I found the problem.

It's a version issue. Version 2 vs Version 4.

If you have a PSCustom object, $ObjA , and you want to select a NoteProperty, called "ntprt"

here is how you do it:

PSVersion 4:

$ObjA.ntpr

PSVersion 2;

$ObjA | Select-Object -Property ntpr

So in my script, the problem was a version issue, not LOOPING.

gosh... that's what a good night sleep does to you 🙂

Gary Varga SSC Guru Points: 82166 More actions · Answer 9

Thanks for coming back to post the solution.

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!