Solved

How to query if an OS has SQL Server installed ?

Posted on 2013-01-15
29
717 Views
Last Modified: 2013-02-04
Hello,

How to query if an OS has SQL Server installed ?

If so, i need to know the version:

SQL Server 2005, SQL Server 2008 or SQL Server 2008 R2, etc, etc, etc.

thanks,
0
Comment
Question by:namerg
  • 13
  • 7
  • 7
  • +1
29 Comments
 
LVL 9

Expert Comment

by:David Carr
ID: 38779527
Try Invoke-Sqlcmd -Query "SELECT @@VERSION;" -QueryTimeout 3
0
 

Author Comment

by:namerg
ID: 38779530
I meant, if i have a list of servers, how would i do it  ?
0
 
LVL 9

Expert Comment

by:David Carr
ID: 38779540
another command to try is Get-ItemProperty 'HKLM:\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL'
0
 
LVL 28

Expert Comment

by:becraig
ID: 38779543
$servers = (gc serverlist.txt)

foreach ($server in $servers)
{
$server = hostname
invoke-command -Computername $server -scriptblock {
gwmi win32_service -computerName $server | {where $_.Name -match "mssql*" }
Invoke-Sqlcmd -Query "SELECT @@VERSION;" -QueryTimeout 3
} 
}

Open in new window


Try something like this.
0
 

Author Comment

by:namerg
ID: 38779557
hmm, i get an error:


Expressions are only allowed as the first element of a pipeline.
At C:\scripts\VMware\sql_check.ps1:23 char:128
+ invoke-command -Computername $server -scriptblock { gwmi win32_service -computerName $server | {where $_.Name
    + CategoryInfo          : ParserError: (:) [], ParseException
    + FullyQualifiedErrorId : ExpressionsMustBeFirstInPipeline

$servers = (gc "C:\scripts\VMware\servers.txt")

foreach ($server in $servers)
{
$server = hostname
invoke-command -Computername $server -scriptblock { gwmi win32_service -computerName $server | {where $_.Name -match "mssql*" }
Invoke-Sqlcmd -Query "SELECT @@VERSION;" -QueryTimeout 3
} 
}

Open in new window

0
 
LVL 28

Expert Comment

by:becraig
ID: 38779604
| where { $_.Name -match "mssql*" }
0
 

Author Comment

by:namerg
ID: 38779618
Hmm, I get the following error:

[is-gk65vv1] Connecting to remote server failed with the following error message : The client cannot connect to the destination specified in the request. Verify that the service on the de
stination is running and is accepting requests. Consult the logs and documentation for the WS-Management service running on the destination, most commonly IIS or WinRM. If the destination
 is the WinRM service, run the following command on the destination to analyze and configure the WinRM service: "winrm quickconfig". For more information, see the about_Remote_Troubleshoo
ting Help topic.
    + CategoryInfo          : OpenError: (:) [], PSRemotingTransportException
    + FullyQualifiedErrorId : PSSessionStateBroken
0
 
LVL 28

Expert Comment

by:becraig
ID: 38779636
Ahh here goes the question, what OS are you working with ?
0
 

Author Comment

by:namerg
ID: 38779640
Server 2008, 2k8 R2, Std and Ent
0
 
LVL 28

Expert Comment

by:becraig
ID: 38780266
Ok so you would need to enable winrm on the servers you want to query how many servers are we looking at approximately ?

I can give you a solution to setup winrm then verify it is working and then query if sql is installed then report the version.
0
 

Author Comment

by:namerg
ID: 38780318
hmm, do not like that solution, it should be another way..
0
 
LVL 28

Expert Comment

by:becraig
ID: 38780337
Is there a specific reason why you do not want winrm functioning on your servers ?
Or is there something else in the approach that does not seem to do what you need ?


I know the winrm functionality is built in to allow remote calls / psremoting sessions from powershell, which is invaluable for debugging as well as general administration.
0
 

Author Comment

by:namerg
ID: 38781226
I found another way,

It is working 95% well.
clear-host
$start = get-date
write-host "Start: "  $start
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
$FilePath = "C:\scripts\VMware"
$OutFile = Join-Path -path $FilePath -childPath ("SQLVersions_" + (get-date).toString('yyyyMMdd_hhmmtt') + ".log")

# Version inventory
@(foreach ($svr in get-content "C:\scripts\VMware\servers.txt") {
	$s = New-Object "Microsoft.SqlServer.Management.Smo.Server" $svr
	$s | select Name, Version, Product, OSVersion, ProductLevel, Edition
})  | export-csv -noType $OutFile
$end = get-date
write-host "End: "  $end

Open in new window

Result:
"Name","Version","Product","OSVersion","ProductLevel","Edition"
"CBEDM",,,,,
"CBEDMTEST","10.50.2500","Microsoft SQL Server","6.1 (7601)","SP1","Standard Edition (64-bit)"
"CBWFTEST","10.50.2500","Microsoft SQL Server","6.1 (7601)","SP1","Standard Edition (64-bit)"

Open in new window

If I can add the property if it is server 2008, 2005 or R2. I am golden.
In SQL Mgmt Console you can do a query for
select @@version
select serverproperty('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

Open in new window

and I get what I want, but I want it with powershell
0
 
LVL 28

Expert Comment

by:becraig
ID: 38781367
So do you already have the powershell sql cmdlets ?

If not I guess we will have to go some other route  ?
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:namerg
ID: 38782619
I do not have the SQL cmd lets installed. Microsoft.SqlServer.Smo is the server class object for SQL.
0
 
LVL 28

Expert Comment

by:becraig
ID: 38782779
I am not sure how the server class object for sql relates to having the sql cmdlets on the server you are calling these servers from ??
My ask is simply: "Do you have a tsql utility for powershell you can use to run the queries you need to run ?"

Anyhow you can simply use sqlcmd to make the call and run the sql query I am sure you know how to use sqlcmd so I won't suggest the syntax other than to say it is your best option unless you don't have that on your server either and think it would be a bad idea to drop onto a box.
0
 

Author Comment

by:namerg
ID: 38782984
No, I do not have the tsql.
0
 
LVL 40

Expert Comment

by:Subsun
ID: 38789753
Check the script from article and see if it works for you...
http://www.orcsweb.com/blog/james/fun-with-powershell-how-to-get-sql-server-information/
0
 

Author Comment

by:namerg
ID: 38793737
@subsun. thank you very much.
But, how do i make the code to read from a file as stated on 38781226
$SqlServer = "CBEDMTEST.example.com"
# get instances based on services

	$localInstances = @()
	[array]$captions = gwmi win32_service -computerName $SqlServer | ?{$_.Name -match "mssql*" -and $_.PathName -match "sqlservr.exe"} | %{$_.Caption}
	foreach ($caption in $captions) {
		if ($caption -eq "MSSQLSERVER") {$localInstances += "MSSQLSERVER"} 
		    else {
			$temp = $caption | %{$_.split(" ")[-1]} | %{$_.trimStart("(")} | %{$_.trimEnd(")")}
			$localInstances += $temp
		}
	}
	# load the SQL SMO assembly
	[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
	if ($localInstances.count -gt 1) {
		$SqlInfo = @()
		foreach ($currInstance in $localInstances) {
			if ($currInstance -eq "MSSQLSERVER") {
				$serverName = "$SqlServer"
			} else {
				$serverName = "$SqlServer\$currInstance"
			}
			$server = New-Object -typeName Microsoft.SqlServer.Management.Smo.Server -argumentList "$serverName"
			$tempSqlInfo = "" | Select Version,Edition,fullVer,majVer,minVer,Build,Arch,Level,Root,Instance
			[string]$tempSqlInfo.fullVer = $server.information.VersionString.toString()
			[string]$tempSqlInfo.Edition = $server.information.Edition.toString()
			[int]$tempSqlInfo.majVer = $server.version.Major
			[int]$tempSqlInfo.minVer = $server.version.Minor
			[int]$tempSqlInfo.build = $server.version.Build
			switch ($tempSqlInfo.majVer) {
				8 {[string]$tempSqlInfo.Version = "SQL Server 2000"}
				9 {[string]$tempSqlInfo.Version = "SQL Server 2005"}
				10 {if ($tempSqlInfo.minVer -eq 0 ) {[string]$tempSqlInfo.Version = "SQL Server 2008"} 
				    else {[string]$tempSqlInfo.Version = "SQL Server 2008 R2"}
				   }
				default {[string]$tempSqlInfo.Version = "Unknown"}
			}
			[string]$tempSqlInfo.Arch = $server.information.Platform.toString()
			[string]$tempSqlInfo.Level = $server.information.ProductLevel.toString()
			[string]$tempSqlInfo.Root = $server.information.RootDirectory.toString()
			[string]$tempSqlInfo.Instance = $currInstance
			$SqlInfo += $tempSqlInfo
		}
	} else {	
		$server = New-Object -typeName Microsoft.SqlServer.Management.Smo.Server -argumentList "$SqlServer"
		$SqlInfo = "" | Select Version,Edition,fullVer,majVer,minVer,Build,Arch,Level,Root,Instance
		[string]$SqlInfo.fullVer = $server.information.VersionString.toString()
		[string]$SqlInfo.Edition = $server.information.Edition.toString()
		[int]$SqlInfo.majVer = $server.version.Major
		[int]$SqlInfo.minVer = $server.version.Minor
		[int]$SqlInfo.build = $server.version.Build
		switch ($SqlInfo.majVer) {
			8 {[string]$SqlInfo.Version = "SQL Server 2000"}
			9 {[string]$SqlInfo.Version = "SQL Server 2005"}
			10 {if ($SqlInfo.minVer -eq 0 ) {[string]$SqlInfo.Version = "SQL Server 2008"}
				    else {[string]$SqlInfo.Version = "SQL Server 2008 R2"}
			   }
			default {[string]$SqlInfo.Version = "Unknown"}
		}
		[string]$SqlInfo.Arch = $server.information.Platform.toString()
		[string]$SqlInfo.Level = $server.information.ProductLevel.toString()
		[string]$SqlInfo.Root = $server.information.RootDirectory.toString()
		[string]$SqlInfo.Instance = $localInstances[0]
	}
	return $SqlInfo

Open in new window

Desired result a csv with the following info:
Hostname: CBEDMTEST.example.com
Version  : SQL Server 2008 R2
Edition  : Standard Edition (64-bit)
fullVer  : 10.50.2500.0
majVer   : 10
minVer   : 50
Build    : 2500
Arch     : NT x64
Level    : SP1
Root     : C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL
Instance : MSSQLSERVER

Open in new window

Thanks for your help
0
 
LVL 40

Expert Comment

by:Subsun
ID: 38794452
Try this..
[array]$Result = $null
$Serverlist = GC C:\Server.txt
Foreach ($SqlServer in $Serverlist){
# get instances based on services
	$localInstances = @()
	[array]$captions = gwmi win32_service -computerName $SqlServer -ErrorAction SilentlyContinue | ?{$_.Name -match "mssql*" -and $_.PathName -match "sqlservr.exe"} | %{$_.Caption}
	If ($captions){
	foreach ($caption in $captions) {
		if ($caption -eq "MSSQLSERVER") {$localInstances += "MSSQLSERVER"} 
		    else {
			$temp = $caption | %{$_.split(" ")[-1]} | %{$_.trimStart("(")} | %{$_.trimEnd(")")}
			$localInstances += $temp
		}
	}
	# load the SQL SMO assembly
	[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
	if ($localInstances.count -gt 1) {
		$SqlInfo = @()
		foreach ($currInstance in $localInstances) {
			if ($currInstance -eq "MSSQLSERVER") {
				$serverName = "$SqlServer"
			} else {
				$serverName = "$SqlServer\$currInstance"
			}
			$server = New-Object -typeName Microsoft.SqlServer.Management.Smo.Server -argumentList "$serverName"
			$tempSqlInfo = "" | Select Hostname,Version,Edition,fullVer,majVer,minVer,Build,Arch,Level,Root,Instance
			[string]$tempSqlInfo.Hostname = $SqlServer
			[string]$tempSqlInfo.fullVer = $server.information.VersionString.toString()
			[string]$tempSqlInfo.Edition = $server.information.Edition.toString()
			[int]$tempSqlInfo.majVer = $server.version.Major
			[int]$tempSqlInfo.minVer = $server.version.Minor
			[int]$tempSqlInfo.build = $server.version.Build
			switch ($tempSqlInfo.majVer) {
				8 {[string]$tempSqlInfo.Version = "SQL Server 2000"}
				9 {[string]$tempSqlInfo.Version = "SQL Server 2005"}
				10 {if ($tempSqlInfo.minVer -eq 0 ) {[string]$tempSqlInfo.Version = "SQL Server 2008"} 
				    else {[string]$tempSqlInfo.Version = "SQL Server 2008 R2"}
				   }
				default {[string]$tempSqlInfo.Version = "Unknown"}
			}
			[string]$tempSqlInfo.Arch = $server.information.Platform.toString()
			[string]$tempSqlInfo.Level = $server.information.ProductLevel.toString()
			[string]$tempSqlInfo.Root = $server.information.RootDirectory.toString()
			[string]$tempSqlInfo.Instance = $currInstance
			$SqlInfo += $tempSqlInfo
		}
	} else {	
		$server = New-Object -typeName Microsoft.SqlServer.Management.Smo.Server -argumentList "$SqlServer"
		$SqlInfo = "" | Select Hostname,Version,Edition,fullVer,majVer,minVer,Build,Arch,Level,Root,Instance
		[string]$tempSqlInfo.Hostname = $SqlServer
		[string]$SqlInfo.fullVer = $server.information.VersionString.toString()
		[string]$SqlInfo.Edition = $server.information.Edition.toString()
		[int]$SqlInfo.majVer = $server.version.Major
		[int]$SqlInfo.minVer = $server.version.Minor
		[int]$SqlInfo.build = $server.version.Build
		switch ($SqlInfo.majVer) {
			8 {[string]$SqlInfo.Version = "SQL Server 2000"}
			9 {[string]$SqlInfo.Version = "SQL Server 2005"}
			10 {if ($SqlInfo.minVer -eq 0 ) {[string]$SqlInfo.Version = "SQL Server 2008"}
				    else {[string]$SqlInfo.Version = "SQL Server 2008 R2"}
			   }
			default {[string]$SqlInfo.Version = "Unknown"}
		}
		[string]$SqlInfo.Arch = $server.information.Platform.toString()
		[string]$SqlInfo.Level = $server.information.ProductLevel.toString()
		[string]$SqlInfo.Root = $server.information.RootDirectory.toString()
		[string]$SqlInfo.Instance = $localInstances[0]
		}
		$Result += $SqlInfo
	}
}
$Result | Select * | Export-Csv C:\report.csv -NoTypeInformation

Open in new window

0
 

Author Comment

by:namerg
ID: 38794572
Hmm, get the following error, i think ia related to line 49

Property 'Hostname' cannot be found on this object; make sure it exists and is settable.
At C:\scripts\ActiveDirectory\getInfo-SqlServer\getInfo-SqlServer.ps1:57 char:24
+         [string]$tempSqlInfo. <<<< Hostname = $SqlServer
    + CategoryInfo          : InvalidOperation: (Hostname:String) [], RuntimeException
    + FullyQualifiedErrorId : PropertyNotFound
0
 
LVL 40

Expert Comment

by:Subsun
ID: 38794648
Sorry it's a typo..

Change line number 50 from [string]$tempSqlInfo.Hostname = $SqlServer to [string]$SqlInfo.Hostname = $SqlServer
0
 

Author Comment

by:namerg
ID: 38794772
Hmm, errors like these:

You cannot call a method on a null-valued expression.
At C:\scripts\ActiveDirectory\getInfo-SqlServer\getInfo-SqlServer.ps1:59 char:72
+         [string]$SqlInfo.fullVer = $server.information.VersionString.toString <<<< ()
    + CategoryInfo          : InvalidOperation: (toString:String) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At C:\scripts\ActiveDirectory\getInfo-SqlServer\getInfo-SqlServer.ps1:60 char:66
+         [string]$SqlInfo.Edition = $server.information.Edition.toString <<<< ()
    + CategoryInfo          : InvalidOperation: (toString:String) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull
0
 
LVL 40

Expert Comment

by:Subsun
ID: 38794788
Did you get the details result file?
0
 

Author Comment

by:namerg
ID: 38794896
I guess it did :)

Hostname	Version	Edition	fullVer	majVer	minVer	Build	Arch	Level	Root	Instance
CBEDMTEST	SQL Server 2008 R2	Standard Edition (64-bit)	10.50.2500.0	10	50	2500	NT x64	SP1	C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL	MSSQLSERVER
CBWFTEST	SQL Server 2008 R2	Standard Edition (64-bit)	10.50.2500.0	10	50	2500	NT x64	SP1	C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL	MSSQLSERVER

Open in new window

0
 
LVL 40

Expert Comment

by:Subsun
ID: 38794991
Made some changes.. Try now..
[array]$Result = $null
$Serverlist = GC C:\Server.txt
Foreach ($SqlServer in $Serverlist){
# get instances based on services
	$localInstances = @()
	[array]$captions = gwmi win32_service -computerName $SqlServer -ErrorAction SilentlyContinue | ?{$_.Name -match "mssql*" -and $_.PathName -match "sqlservr.exe"} | %{$_.Caption}
	If ($captions){
	foreach ($caption in $captions) {
		if ($caption -eq "MSSQLSERVER") {$localInstances += "MSSQLSERVER"} 
		    else {
			$temp = $caption | %{$_.split(" ")[-1]} | %{$_.trimStart("(")} | %{$_.trimEnd(")")}
			$localInstances += $temp
		}
	}
	# load the SQL SMO assembly
	[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
	if ($localInstances.count -gt 1) {
		$SqlInfo = @()
		foreach ($currInstance in $localInstances) {
			if ($currInstance -eq "MSSQLSERVER") {
				$serverName = "$SqlServer"
			} else {
				$serverName = "$SqlServer\$currInstance"
			}
			$server = New-Object -typeName Microsoft.SqlServer.Management.Smo.Server -argumentList "$serverName"
			$tempSqlInfo = "" | Select Hostname,Version,Edition,fullVer,majVer,minVer,Build,Arch,Level,Root,Instance
			[string]$tempSqlInfo.Hostname = $SqlServer
			[string]$tempSqlInfo.fullVer = $server.information.VersionString.toString()
			[string]$tempSqlInfo.Edition = $server.information.Edition.toString()
			[int]$tempSqlInfo.majVer = $server.version.Major
			[int]$tempSqlInfo.minVer = $server.version.Minor
			[int]$tempSqlInfo.build = $server.version.Build
			switch ($tempSqlInfo.majVer) {
				8 {[string]$tempSqlInfo.Version = "SQL Server 2000"}
				9 {[string]$tempSqlInfo.Version = "SQL Server 2005"}
				10 {if ($tempSqlInfo.minVer -eq 0 ) {[string]$tempSqlInfo.Version = "SQL Server 2008"} 
				    else {[string]$tempSqlInfo.Version = "SQL Server 2008 R2"}
				   }
				default {[string]$tempSqlInfo.Version = "Unknown"}
			}
			[string]$tempSqlInfo.Arch = $server.information.Platform.toString()
			[string]$tempSqlInfo.Level = $server.information.ProductLevel.toString()
			[string]$tempSqlInfo.Root = $server.information.RootDirectory.toString()
			[string]$tempSqlInfo.Instance = $currInstance
			$SqlInfo += $tempSqlInfo
		}
	 } else {
			if ($localInstances -eq "MSSQLSERVER") {
			  $serverName = "$SqlServer"
			} else {
			  $serverName = "$SqlServer\$localInstances"
			}
		$server = New-Object -typeName Microsoft.SqlServer.Management.Smo.Server -argumentList "$serverName"
		$SqlInfo = "" | Select Hostname,Version,Edition,fullVer,majVer,minVer,Build,Arch,Level,Root,Instance
		[string]$SqlInfo.Hostname = $SqlServer
		[string]$SqlInfo.fullVer = $server.information.VersionString.toString()
		[string]$SqlInfo.Edition = $server.information.Edition.toString()
		[int]$SqlInfo.majVer = $server.version.Major
		[int]$SqlInfo.minVer = $server.version.Minor
		[int]$SqlInfo.build = $server.version.Build
		switch ($SqlInfo.majVer) {
			8 {[string]$SqlInfo.Version = "SQL Server 2000"}
			9 {[string]$SqlInfo.Version = "SQL Server 2005"}
			10 {if ($SqlInfo.minVer -eq 0 ) {[string]$SqlInfo.Version = "SQL Server 2008"}
				    else {[string]$SqlInfo.Version = "SQL Server 2008 R2"}
			   }
			default {[string]$SqlInfo.Version = "Unknown"}
		}
		[string]$SqlInfo.Arch = $server.information.Platform.toString()
		[string]$SqlInfo.Level = $server.information.ProductLevel.toString()
		[string]$SqlInfo.Root = $server.information.RootDirectory.toString()
		[string]$SqlInfo.Instance = $localInstances[0]
		}
		$Result += $SqlInfo
	}
}
$Result | Select * | Export-Csv C:\report.csv -NoTypeInformation

Open in new window

0
 

Author Comment

by:namerg
ID: 38807797
The CSV gets generated but still getting errors like the following:

PS C:\scripts\ActiveDirectory\getInfo-SqlServer> .\getInfo-SqlServer.ps1
Get-WmiObject : Specified cast is not valid.
At C:\scripts\ActiveDirectory\getInfo-SqlServer\getInfo-SqlServer.ps1:13 char:25
+     [array]$captions = gwmi <<<<  win32_service -computerName $SqlServer -ErrorAction SilentlyContinue | ?{$_.Name -match "mssql*" -and $_.PathName -match "sqlservr.exe"} | %{$_.Caption}
    + CategoryInfo          : NotSpecified: (:) [Get-WmiObject], InvalidCastException
    + FullyQualifiedErrorId : System.InvalidCastException,Microsoft.PowerShell.Commands.GetWmiObjectCommand

You cannot call a method on a null-valued expression.
At C:\scripts\ActiveDirectory\getInfo-SqlServer\getInfo-SqlServer.ps1:63 char:72
+         [string]$SqlInfo.fullVer = $server.information.VersionString.toString <<<< ()
    + CategoryInfo          : InvalidOperation: (toString:String) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At C:\scripts\ActiveDirectory\getInfo-SqlServer\getInfo-SqlServer.ps1:64 char:66
+         [string]$SqlInfo.Edition = $server.information.Edition.toString <<<< ()
    + CategoryInfo          : InvalidOperation: (toString:String) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At C:\scripts\ActiveDirectory\getInfo-SqlServer\getInfo-SqlServer.ps1:76 char:64
+         [string]$SqlInfo.Arch = $server.information.Platform.toString <<<< ()
    + CategoryInfo          : InvalidOperation: (toString:String) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Get-WmiObject : Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
At C:\scripts\ActiveDirectory\getInfo-SqlServer\getInfo-SqlServer.ps1:13 char:25
+     [array]$captions = gwmi <<<<  win32_service -computerName $SqlServer -ErrorAction SilentlyContinue | ?{$_.Name -match "mssql*" -and $_.PathName -match "sqlservr.exe"} | %{$_.Caption}
    + CategoryInfo          : NotSpecified: (:) [Get-WmiObject], UnauthorizedAccessException
    + FullyQualifiedErrorId : System.UnauthorizedAccessException,Microsoft.PowerShell.Commands.GetWmiObjectCommand

You cannot call a method on a null-valued expression.
At C:\scripts\ActiveDirectory\getInfo-SqlServer\getInfo-SqlServer.ps1:78 char:69
+         [string]$SqlInfo.Root = $server.information.RootDirectory.toString <<<< ()
    + CategoryInfo          : InvalidOperation: (toString:String) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Open in new window

0
 
LVL 40

Accepted Solution

by:
Subsun earned 500 total points
ID: 38807856
These are error in retrieving WMI object, probably not able to connect to server or access is denied.. You can either add $ErrorActionPreference = "SilentlyContinue" in first line to suppress errors..
Or have some kind of error control like follows to list the errors..
[array]$Result = $null
$Serverlist = GC C:\Server.txt
Foreach ($SqlServer in $Serverlist){
# get instances based on services
	$localInstances = @()
	Try
	{
	[array]$captions = gwmi win32_service -computerName $SqlServer -ErrorAction Stop | ?{$_.Name -match "mssql*" -and $_.PathName -match "sqlservr.exe"} | %{$_.Caption}

	If ($captions){
	foreach ($caption in $captions) {
		if ($caption -eq "MSSQLSERVER") {$localInstances += "MSSQLSERVER"} 
		    else {
			$temp = $caption | %{$_.split(" ")[-1]} | %{$_.trimStart("(")} | %{$_.trimEnd(")")}
			$localInstances += $temp
		}
	}
	# load the SQL SMO assembly
	[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
	if ($localInstances.count -gt 1) {
		$SqlInfo = @()
		foreach ($currInstance in $localInstances) {
			if ($currInstance -eq "MSSQLSERVER") {
				$serverName = "$SqlServer"
			} else {
				$serverName = "$SqlServer\$currInstance"
			}
			$server = New-Object -typeName Microsoft.SqlServer.Management.Smo.Server -argumentList "$serverName"
			$tempSqlInfo = "" | Select Hostname,Version,Edition,fullVer,majVer,minVer,Build,Arch,Level,Root,Instance
			[string]$tempSqlInfo.Hostname = $SqlServer
			[string]$tempSqlInfo.fullVer = $server.information.VersionString.toString()
			[string]$tempSqlInfo.Edition = $server.information.Edition.toString()
			[int]$tempSqlInfo.majVer = $server.version.Major
			[int]$tempSqlInfo.minVer = $server.version.Minor
			[int]$tempSqlInfo.build = $server.version.Build
			switch ($tempSqlInfo.majVer) {
				8 {[string]$tempSqlInfo.Version = "SQL Server 2000"}
				9 {[string]$tempSqlInfo.Version = "SQL Server 2005"}
				10 {if ($tempSqlInfo.minVer -eq 0 ) {[string]$tempSqlInfo.Version = "SQL Server 2008"} 
				    else {[string]$tempSqlInfo.Version = "SQL Server 2008 R2"}
				   }
				default {[string]$tempSqlInfo.Version = "Unknown"}
			}
			[string]$tempSqlInfo.Arch = $server.information.Platform.toString()
			[string]$tempSqlInfo.Level = $server.information.ProductLevel.toString()
			[string]$tempSqlInfo.Root = $server.information.RootDirectory.toString()
			[string]$tempSqlInfo.Instance = $currInstance
			$SqlInfo += $tempSqlInfo
		}
	 } else {
			if ($localInstances -eq "MSSQLSERVER") {
			  $serverName = "$SqlServer"
			} else {
			  $serverName = "$SqlServer\$localInstances"
			}
		$server = New-Object -typeName Microsoft.SqlServer.Management.Smo.Server -argumentList "$serverName"
		$SqlInfo = "" | Select Hostname,Version,Edition,fullVer,majVer,minVer,Build,Arch,Level,Root,Instance
		[string]$SqlInfo.Hostname = $SqlServer
		[string]$SqlInfo.fullVer = $server.information.VersionString.toString()
		[string]$SqlInfo.Edition = $server.information.Edition.toString()
		[int]$SqlInfo.majVer = $server.version.Major
		[int]$SqlInfo.minVer = $server.version.Minor
		[int]$SqlInfo.build = $server.version.Build
		switch ($SqlInfo.majVer) {
			8 {[string]$SqlInfo.Version = "SQL Server 2000"}
			9 {[string]$SqlInfo.Version = "SQL Server 2005"}
			10 {if ($SqlInfo.minVer -eq 0 ) {[string]$SqlInfo.Version = "SQL Server 2008"}
				    else {[string]$SqlInfo.Version = "SQL Server 2008 R2"}
			   }
			default {[string]$SqlInfo.Version = "Unknown"}
		}
		[string]$SqlInfo.Arch = $server.information.Platform.toString()
		[string]$SqlInfo.Level = $server.information.ProductLevel.toString()
		[string]$SqlInfo.Root = $server.information.RootDirectory.toString()
		[string]$SqlInfo.Instance = $localInstances[0]
		}
		$Result += $SqlInfo
	  }
		}
	catch [System.Exception]
	{
	Write-host "Not able to connect to Server $SqlServer Error : $($_.Exception.Message)"
	}
}
$Result | Select * | Export-Csv C:\report.csv -NoTypeInformation

Open in new window

0
 
LVL 40

Expert Comment

by:Subsun
ID: 38820864
Still looking for some help??
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

In this previous article (https://oddytee.wordpress.com/2016/05/05/provision-new-office-365-user-and-mailbox-from-exchange-hybrid-via-powershell/), we made basic license assignments to users in O365. When I say basic, the method is the simplest way …
OfficeMate Freezes on login or does not load after login credentials are input.
This tutorial will walk an individual through the steps necessary to configure their installation of BackupExec 2012 to use network shared disk space. Verify that the path to the shared storage is valid and that data can be written to that location:…
This tutorial will walk an individual through the steps necessary to enable the VMware\Hyper-V licensed feature of Backup Exec 2012. In addition, how to add a VMware server and configure a backup job. The first step is to acquire the necessary licen…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now