question

Shawn_Melton avatar image
Shawn_Melton asked

Pulling Instance name to pass to SMO object

OK, I have a headache so there is probably something I am missing here... I have this command: *Get-WmiObject -Namespace "root\\Microsoft\\SqlServer\\ComputerManagement" -Class ServerSettings | Select InstanceName* From that it will return the instance names on a server that exist for SQL Server. So I now want to issue a command using SMO to pull in if C2 Audit mode is enabled or not. However I need to pass the instance name for that command: *(Code here to load SMO objects)* *$s = New-Object Microsoft.SqlServer.Management.Smo.Server InstanceName* *$s.Configuration.C2AuditMode | Format-Table DisplayName, RunValue, ConfigValue -AutoSize* So I would like to execute it against each instance, but I don't want the script to be dependent upon me hard-coding the instance name. Any ideas? I'm sure yall have some!
scriptpowershellsql-server-smo
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

waushh2 avatar image
waushh2 answered
Try it this way: [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null Get-WmiObject -Namespace "root\Microsoft\SqlServer\ComputerManagement" -Class ServerSettings | ForEach-Object { $in = $_.InstanceName; if ($in -eq "MSSQLSERVER") { $in = "." } else { $in = (".\{0}" -f $_.InstanceName) }; $s = New-Object Microsoft.SqlServer.Management.Smo.Server $in; $s.Configuration.C2AuditMode | Select-Object @{Name="InstanceName"; Expression={$in}}, DisplayName, RunValue, ConfigValue } | Format-Table * -AutoSize Kind regards, Wolfgang
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

yep that worked...now what'd you do?
0 Likes 0 ·
Ah...you put the server name in front of it. What if I wanted to run this against a cluster, where the servername does not come into play?
0 Likes 0 ·
Huh, the class does not exist on a clustered instance.
0 Likes 0 ·
Blackhawk-17 avatar image
Blackhawk-17 answered
Pipe the **Get-WmiObject -Namespace "root\\Microsoft\\SqlServer\\ComputerManagement" -Class ServerSettings | Select InstanceName** call into a foreach container and pass the instance name in, perhaps, to a function. I'm not near anywhere I can test/code this out right now but I think that would work. EDIT--- Get-WmiObject -Namespace "root\Microsoft\SqlServer\ComputerManagement" -Class ServerSettings | ForEach-Object -Process {MyFunction $_.InstanceName}
13 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Get-WmiObject -Namespace "root\\Microsoft\\SqlServer\\ComputerManagement" -Class ServerSettings | ForEach {"This is the instance name $_.InstanceName"} Gives me the full system object path: servername\\root\\microsoft\\...:ServerSettings.instancename="name".instanceName I don't think that will pass to the SMO object correctly. Is there a way to clean it up?
0 Likes 0 ·
If I throw the Select InstanceName in there I can bring it down to: @{InstanceName=XXXX}.name
0 Likes 0 ·
What versions of SQL, SMO, PowerShell?
0 Likes 0 ·
Have you tried running my code snippet? I don't get a full system object path... just the instance names.
0 Likes 0 ·
AHHH... Try this instead: ForEach {"This is the instance name: " + $_.InstanceName"}
0 Likes 0 ·
now where do I find information on why I should have used the "+" (plus sign)?
0 Likes 0 ·
@meltondba The plus sign is just to concat the static **This is the instance name:** with the variable **$\_.InstanceName**. So, if you have 3 instances named Instance1, Instance2, and Instance3, you will get something like this when executing the line ForEach {"This is the instance name: " + $\_.InstanceName} This is the instance name: Instance1
This is the instance name: Instance2
This is the instance name: Instance3
0 Likes 0 ·
OK. so in the context of being able to combine my two snipets together it does not work in this manner. The value of the InstanceName when using it with the SMO has to be in System.Int32, so the input string was not in the correct format. This is what I have written up...
0 Likes 0 ·
Source: http://sqlblog.com/blogs/allen_white/archive/2008/12/07/loading-smo-assemblies-into-powershell.aspx Load SMO assembly, and if we're running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries $v = [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') $p = $v.FullName.Split(',') $p1 = $p[1].Split('=') $p2 = $p1[1].Split('.') if ($p2[0] -ne '9') { [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | out-null [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement') | out-null } Get-WmiObject -Namespace "root\\Microsoft\\SqlServer\\ComputerManagement10" -Class ServerSettings | ForEach-Object { if (+$_.InstanceName -eq "MSSQLSERVER") {$s = New-Object Microsoft.SqlServer.Management.Smo.Server $env:ComputerName} ESLE {$s = New-Object Microsoft.SqlServer.Management.Smo.Server + $_.InstanceName} $s.Configuration.C2AuditMode | Format-Table DisplayName, RunValue, ConfigValue -AutoSize }
0 Likes 0 ·
Basically what I said... you just pass the whole object in rather than a property set.
0 Likes 0 ·
Maybe I'm not following. Passing the whole object still returns empty results. If I Get-Member just the command to return the InstanceName it shows it is a System.String and the SMO object can only accept a System.Int32. Do I not need to conver it?
0 Likes 0 ·
After I pass the object into the SMO command [$s = New-Object Microsoft.SqlServer.Management.Smo.Server $_.InstanceName]. I get blank results. So I threw in just $s after the command to see what it spit out. It shows the connection information and the instance name is blank, so some how it is still not getting it.
0 Likes 0 ·
Let's take small steps and make sure we each see the same thing. What output do you get if you run this: Get-WmiObject -Namespace "root\\Microsoft\\SqlServer\\ComputerManagement10" -Class ServerSettings | ForEach-Object -Process {"Instance: " + $_.InstanceName}
0 Likes 0 ·
Blackhawk-17 avatar image
Blackhawk-17 answered
Okay... what does the following return for you? Get-WmiObject -Namespace "root\Microsoft\SqlServer\ComputerManagement10" -Class ServerSettings | ForEach-Object -Process {$s = New-Object Microsoft.SqlServer.Management.Smo.Server $_.InstanceName; $s.Configuration.C2AuditMode | Format-Table DisplayName, RunValue, ConfigValue -AutoSize; }
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I get the column headings but no return values.
0 Likes 0 ·
sqlmashup avatar image
sqlmashup answered
this should handle sql server 2005 and sql server 2008 **clusters**... [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null Get-WmiObject -Query "SELECT PrivateProperties FROM MSCluster_Resource WHERE Type = 'SQL Server'" -Namespace "Root\MSCluster" | ForEach-Object { $SqlName = if ($_.PrivateProperties.InstanceName -eq "MSSQLSERVER") {$_.PrivateProperties.VirtualServerName} else {"$($_.PrivateProperties.VirtualServerName)\$($_.PrivateProperties.InstanceName)"}; $SmoServer = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.Server" -ArgumentList $SqlName; $SmoServer.Configuration.C2AuditMode; } | Select-Object -Property @(@{Name="SqlName";Expression={$SqlName}}, "DisplayName", "RunValue", "ConfigValue");
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.