Minimum service pack requirement for SQL server 2005?

Apr 8, 2014 at 2:51 PM
Hi Kendal,

Attended your CLE SQL Saturday presentation few months ago. Impressed by product and amount of information reported. Using on multiple servers, but having issue with one running SQL Server 2005, SP2. Verified the SMO component installed. My question is are there minimum service packs for each of the SQL releases? Getting the following error:

2014-04-08 09:56:34.9713 ? Beginning SQL Server Inventory
2014-04-08 09:56:35.0643 ? -LoggingPreference: Verbose
2014-04-08 09:56:35.0703 ? -ComputerName: <my computer>
2014-04-08 09:56:35.0753 ? -MaxConcurrencyThrottle: 6
2014-04-08 09:56:35.0803 ? -PrivateOnly: False
2014-04-08 09:56:35.0843 ? -ParentProgressId: -1
2014-04-08 09:56:35.0893 ? -IncludeDatabaseObjectPermissions: False
2014-04-08 09:56:35.0933 ? -IncludeDatabaseObjectInformation: True
2014-04-08 09:56:35.0973 ? -IncludeDatabaseSystemObjects: False
2014-04-08 09:56:35.1103 ? Beginning network scan
2014-04-08 09:56:35.1153 ? -ComputerName: <my computer>
2014-04-08 09:56:35.1203 ? -PrivateOnly: False
2014-04-08 09:56:35.1243 ? -MaxConcurrencyThrottle: 6
2014-04-08 09:56:35.1293 ? -ResolveAliases: True
2014-04-08 09:56:35.1373 ? Resolving IP address for <my computer>
2014-04-08 09:56:35.2973 ? Testing PING connectivity to 1 addresses
2014-04-08 09:56:35.3063 $ Testing PING connectivity to <my computer> [1 of 1]
2014-04-08 09:56:37.3925 $ PING response from <my computer>True
2014-04-08 09:56:37.6606 $ PING connectivity test complete
2014-04-08 09:56:37.6796 ? Testing WMI connectivity to 1 addresses
2014-04-08 09:56:37.6886 $ Testing WMI connectivity to <my computer> [1 of 1]
2014-04-08 09:56:37.9486 $ WMI response from <my computer> (<my IP address>): True
2014-04-08 09:56:38.2116 $ WMI connectivity test complete
2014-04-08 09:56:38.2236 ? Network scan complete
2014-04-08 09:56:38.2286 ? -IP Addresses Scanned: 1
2014-04-08 09:56:38.2326 ? -PING Replies: 1
2014-04-08 09:56:38.2386 ? -WMI Replies: 1
2014-04-08 09:56:38.2476 ? Beginning SQL Service discovery scan
2014-04-08 09:56:38.3246 ? Scanning <my computer> at IP address <my IP address> for SQL Services [Device 1 of 1]
2014-04-08 09:56:39.1087 ? ERROR: Unable to retrieve service information from <my computer> (<my IP address>Exception calling "EndInvoke" with "1" argument(s): "The following exception was thrown when trying to enumerate the collection: "An exception occurred in SMO while trying to manage a service."."
2014-04-08 09:56:39.3797 ? SQL Server service discovery complete
2014-04-08 09:56:39.3877 ? Beginning scan of 0 instance(s)
2014-04-08 09:56:39.7198 ? Instance scan complete (Success: 0; Failure: 0)
2014-04-08 09:56:39.7308 ? Writing Inventory to disk
2014-04-08 09:56:39.7348 + No machines found!
Coordinator
Apr 15, 2014 at 4:11 PM
Good question! I haven't run a full set of tests to see if there's a problem with an older version of SMO connecting to a newer version of SQL Server so it's possible that there could be an issue, e.g. SMO 2005 connecting to a 2012 server.

Generally speaking you want to make sure that you're running a version of SMO that matches the highest version of SQL server in your environment. If you're doing that and still running into this error then there's likely a different issue at play here, e.g. permissions. You can put that to the test by running this snippet of code in an ISE window (make sure to update with the correct server name):
# Load SMO Assemblies
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | ForEach-Object {
 if ($_.GetName().Version.Major -ge 10) {
  [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | Out-Null
   [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement') | Out-Null
 }
}

$error.Clear()
$ManagedComputer = New-Object -TypeName 'Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer' -ArgumentList 'COMPUTERNAME.DOMAIN.COM'
$ManagedComputer.Services | Format-Table -Property DisplayName, StartMode, ServiceState, ProcessId
Remove-Variable -Name ManagedComputer
$Error

If everything's working you should see a table of services returned. Let me know if that's the case and we'll try the next step in troubleshooting.

Kendal