Failed to connect to server...

Jul 21, 2010 at 5:32 PM

I have a (Powersehll) script that goes thru each SQL server and issues a SQL SELECT statement using Get-SQLData cmdlet. For some servers it worked, but for others it returned the following error:

Exception calling "Connect" with "0" argument(s): "Failed to connect to server MyServer."

But when I run the same command for only the server that failed, it ran successfully. So, I was wondering is it because the connection time was not enough that caused the connection to fail ? If this is true, is this error coming from Get-SQLData ?  Is there a way to set the connection time ? or should I issue a Start-Sleep after the Get-SQLData to avoid connection timeout ?

Please help. Thanks in advance.

Jimmy

Coordinator
Jul 21, 2010 at 10:16 PM

In SMO and ADO.NET there is both a connection timeout and command timeout the former deals with establishing a connection and the latter with query timeout settings. I wondering if  the timeout issues are related to just establishing a connection.

As test just try using Get-SqlConnection with your list of servers. The connection time by default is 15 seconds. Is it taking more than 15 second to get a connection? A small modification to get-sqlconnection function in sqlserver.psm1 the sqlserver folder can override the default (add bolded line below):

function Get-SqlConnection
{
    param(
    [Parameter(Position=0, Mandatory=$true)] [string]$sqlserver,
    [Parameter(Position=1, Mandatory=$false)] [string]$username, 
    [Parameter(Position=2, Mandatory=$false)] [string]$password
    )
    Write-Verbose "Get-SqlConnection $sqlserver"
    
    if($Username -and $Password)
    { $con = new-object ("Microsoft.SqlServer.Management.Common.ServerConnection") $sqlserver,$username,$password }
    else
    { $con = new-object ("Microsoft.SqlServer.Management.Common.ServerConnection") $sqlserver }
    
    $con.ConnectTimeout = 60
 
    $con.Connect()
    Write-Output $con
    
} #Get-ServerConnection
Jul 22, 2010 at 5:21 PM

Thank you for getting back to me. After some debugging, it turns out the problem was because I didn't setup the alias in "SQL Server Configuration Manager" on the server that has the job scheduled to run the Powershell script.