2

I had the following code in a PowerShell script: 

$sql_instance = "SQLEXPRESS"
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') 
$server = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $sql_instance
Write-Host $server.InstanceName

And nothing was returned. After about 20 minutes of researching I realized I forgot to include the computer name when creating the $server object.

The code should have been: 

$sql_instance = "MYSERVER\SQLEXPRESS"

My question is: how can I verify that the server object created is actually representing a valid SQL Server instance?

I imagine something like: If $server.IsNotValid -eq "True" Then Throw Exception

8kb
  • 183
  • 2
  • 7

2 Answers2

4
$server.ConnectionContext.Connect()

will error if you are not able to connect to the server.

Chad Miller
  • 1,101
  • 8
  • 11
  • This is great. Interesting coincidence that I spent a good chunk of time yesterday reading your Powershell articles on SQLServerCentral. Thanks! – 8kb Sep 01 '10 at 16:26
  • Server class is kind of wierd in that it doesn't error out on non-existent servers. BTW you can also read the PowerShell module code at sqlpsx.codeplex.com. The code above comes from the Get-SqlServer function. – Chad Miller Sep 01 '10 at 17:18
1

As far as I know you cannot test for a connection to SQL server until you try to retrieve a property (or explicitly make a connection with server connection) $conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection

I can't test it right now but I believe that you will get an error if the connection call fails. If not you should if you try to retrieve any property.

Jim B
  • 24,081
  • 4
  • 36
  • 60