How to query if an OS has SQL Server installed ?

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,
namergSystems AdministratorAsked:
Who is Participating?
 
SubsunCommented:
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
 
David CarrCommented:
Try Invoke-Sqlcmd -Query "SELECT @@VERSION;" -QueryTimeout 3
0
 
namergSystems AdministratorAuthor Commented:
I meant, if i have a list of servers, how would i do it  ?
0
Protect Your Employees from Wi-Fi Threats

As Wi-Fi growth and popularity continues to climb, not everyone understands the risks that come with connecting to public Wi-Fi or even offering Wi-Fi to employees, visitors and guests. Download the resource kit to make sure your safe wherever business takes you!

 
David CarrCommented:
another command to try is Get-ItemProperty 'HKLM:\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL'
0
 
becraigCommented:
$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
 
namergSystems AdministratorAuthor Commented:
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
 
becraigCommented:
| where { $_.Name -match "mssql*" }
0
 
namergSystems AdministratorAuthor Commented:
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
 
becraigCommented:
Ahh here goes the question, what OS are you working with ?
0
 
namergSystems AdministratorAuthor Commented:
Server 2008, 2k8 R2, Std and Ent
0
 
becraigCommented:
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
 
namergSystems AdministratorAuthor Commented:
hmm, do not like that solution, it should be another way..
0
 
becraigCommented:
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
 
namergSystems AdministratorAuthor Commented:
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
 
becraigCommented:
So do you already have the powershell sql cmdlets ?

If not I guess we will have to go some other route  ?
0
 
namergSystems AdministratorAuthor Commented:
I do not have the SQL cmd lets installed. Microsoft.SqlServer.Smo is the server class object for SQL.
0
 
becraigCommented:
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
 
namergSystems AdministratorAuthor Commented:
No, I do not have the tsql.
0
 
SubsunCommented:
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
 
namergSystems AdministratorAuthor Commented:
@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
 
SubsunCommented:
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
 
namergSystems AdministratorAuthor Commented:
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
 
SubsunCommented:
Sorry it's a typo..

Change line number 50 from [string]$tempSqlInfo.Hostname = $SqlServer to [string]$SqlInfo.Hostname = $SqlServer
0
 
namergSystems AdministratorAuthor Commented:
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
 
SubsunCommented:
Did you get the details result file?
0
 
namergSystems AdministratorAuthor Commented:
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
 
SubsunCommented:
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
 
namergSystems AdministratorAuthor Commented:
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
 
SubsunCommented:
Still looking for some help??
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.