How to query if an OS has SQL Server installed ?

Try Invoke-Sqlcmd -Query "SELECT @@VERSION;" -QueryTimeout 3

I meant, if i have a list of servers, how would i do it  ?

another command to try is Get-ItemProperty 'HKLM:\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL'

$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
} 
}

Select allOpen in new window

Try something like this.

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
} 
}

Select allOpen in new window

| where { $_.Name -match "mssql*" }

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

Ahh here goes the question, what OS are you working with ?

Server 2008, 2k8 R2, Std and Ent

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.

hmm, do not like that solution, it should be another way..

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.

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

Select allOpen 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)"

Select allOpen 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')

Select allOpen in new window

and I get what I want, but I want it with powershell

So do you already have the powershell sql cmdlets ?

If not I guess we will have to go some other route  ?

I do not have the SQL cmd lets installed. Microsoft.SqlServer.Smo is the server class object for SQL.

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.

No, I do not have the tsql.

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/

@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

Select allOpen 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

Select allOpen in new window

Thanks for your help

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

Select allOpen in new window

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

Sorry it's a typo..

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

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

Did you get the details result file?

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

Select allOpen in new window

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

Select allOpen in new window

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

Select allOpen in new window

Still looking for some help??