2
$connectionString = "Data Source=iaosd;Initial Catalog=fasdasd;User ID=sdfsdfsfd;Password=sd0fuisdofij;Connect Timeout=60"

$timer = [Diagnostics.Stopwatch]::StartNew()

# test db connectivity
try {
    $sqlConnection = New-Object System.Data.SqlClient.SqlConnection -ErrorAction Stop
    $sqlConnection.ConnectionString = $connectionString
    $sqlConnection.Open()
    $sqlConnection.Close()
    Write-Host "connection successful"
} catch {
    Write-Host "ERROR"
}

$timer.stop() 

Write-Host $timer.Elapsed

Hi, This code actually works immediately if $dbServer is localhost and the other details are correct. But when I change the $dbserver to some random text it takes around 1 minute to complete and it crashes my gui. If I don't put any text in $dbServer the try catch works as expected

"Exception calling "Open" with "0" argument(s): "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible

  • CategoryInfo : NotSpecified (:) [], MethodInvocationException

  • Fully QualifiedErrorId : SqlException "

Stefan Wuebbe
  • 2,109
  • 5
  • 17
  • 28
Tom
  • 23
  • 4
  • Sorry, I tried your code, and it worked fine. If I changed the $dbserver to something invalid, the call to $sqlConnection.Open() throws the exception, it is caught by the catch, and 'ERROR' is printed. – toastifer Oct 13 '22 at 21:51
  • Write-Host "[$connectionString]" to check if it is OK. Check from another computer. – filimonic Oct 13 '22 at 22:18
  • @toastifer Oh. What version of Powershell are you using? I'm currently using 5.1.19041.1682 and Powershell is hanging on $sqlConnection.Open() – Tom Oct 13 '22 at 22:38
  • Works (well, the ```catch``` block is executed) here as well on Windows PowerShell 5.1.22000.832 if I just cut & paste your sample code verbatim. Have you got an example of a literal connection string (i.e. just plain text, no variables) that fails for you? – mclayton Oct 13 '22 at 22:40
  • Data Source=iaosd;Initial Catalog=fasdasd;User ID=sdfsdfsfd;Password=sd0fuisdofij;Connect Timeout=60. Also I'm doing some further tests right now. This section of code does crash my GUI but when I isolate this code (in a separate script) it does eventually hit the catch however it takes about 1 minute to do this (I timed it with Diagnostics.Stopwatch]. I have tested this 4 times and each time it took exactly 1 minute to complete +- 30ms. – Tom Oct 13 '22 at 22:53
  • @mclayton I just tried it on another computer with a fresh install of powershell 9.16 and again the script took 1 minute 2 seconds to tun. Do you know why it is taking so long? I have updated my original post with the code I'm running. – Tom Oct 13 '22 at 23:30
  • Sounds like it's polling for servers on your network, and waiting for it to time out. Your connection string has `Timeout=60` in it, which would be 1 minute, which would explain the delay. Since you're running your code in the main GUI thread, that would lock your GUI for a full minute as well. Are you on a domain? – Ken White Oct 14 '22 at 00:12
  • Connect to database with SQL Server Management Studio and in explorer look at log files under Management. – jdweng Oct 14 '22 at 02:13

1 Answers1

3

Calling the .Open() method from SqlConnection will block your thread which explains why your GUI app is crashing.

The alternatives you could use are either, opening the connection inside a Runspace which runs in a different thread hence the GUI wouldn't freeze. If you decide to go this route, these answers can help you out with the code:

Otherwise, the other alternative you have could be using the OpenAsync() method from the class, and wait for the Task to complete using a loop, this would block the thread but only for milliseconds, 200 to be precise in this example. It's also worth noting you could reduce the TimeOut by changing Connect Timeout=60 in your connection string.

try {
    $connectionString = "Data Source=iaosd;Initial Catalog=fasdasd;User ID=sdfsdfsfd;Password=sd0fuisdofij;Connect Timeout=10"
    $sqlConnection    = [Data.SqlClient.SqlConnection] $connectionString

    $task = $sqlConnection.OpenAsync()

    do {
        $response = $task.Wait(200)
        # Assuming this would be an event of a WinForms App,
        # here you could use:
        # [System.Windows.Forms.Application]::DoEvents()
        # To gain control over your form while waiting.
        # Not the best of practices though useful for simple events.
    } until($response)

    $task.GetAwaiter().GetResult()
}
catch {
    $w32except = $_.Exception.InnerException.InnerException.InnerException
    if($w32except -is [ComponentModel.Win32Exception]) {
        return Write-Error $w32except
    }
    Write-Error $_
}
finally {
    $sqlConnection, $task | ForEach-Object Dispose
}

I've added inline comments assuming this could be an event of a WinForms App, though I definitely recommend you to read Remarks from Application.DoEvents Method in detail.

Santiago Squarzon
  • 41,465
  • 5
  • 14
  • 37