Sql Command (Exception calling "ExecuteScalar" with "0" argument)

Question

When I run the below code on first attempt I get an unexplained error, but running the script again on a second attempt works fine...what would be wrong in my code?

By the way I am creating the database before this step...

  $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
  $SqlConnection.ConnectionString = "Server=$dBServer;Database=$dBName;Integrated Security=True" 
  $SqlConnection.Open() 

  $SqlCmd = New-Object System.Data.SqlClient.SqlCommand 
  $SqlCmd.CommandText = $dBCmd 
  $SqlCmd.Connection = $sqlConnection 

  $execute = $SqlCmd.ExecuteScalar() 
  $SqlConnection.Close() 

Error

Exception calling "ExecuteScalar" with "0" argument(s): "A transport-level error has occurred when sending the request to the server. (provider: Shared Memory  Provider, error: 0 - No process is on the other end of the pipe.)" At c:\scripts\DB\Powershell\RunSql.ps1:61 char:34
+   $execute = $sqlCmd.ExecuteScalar <<<< ()
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

Answer 1

Have you checked the SQL Server Configuration Manager to make sure that "Named Pipes" is enabled (under "SQL Server Network Configuration -> Protocols for SQL...")?

Answer 2

That is a common error that occurs if you try to execute a command using a connection that was reset by the server. It happens to me all the time whenever I run a Powershell script, restart the SQL Server, and then try and run the script again. The script thinks that the connection is still open, but when it tries to use it you get a transport-level error and it closes the connection. When you try to run the script again it will re-establish the connection and everything works fine.

If you want to force it to close the connection simply execute the $SqlConnection.Close() statement whenever you restart the SQL server.

Answer 3

For my strange case that I had I finally came to an acceptable solution by using $error variable instead of ($LastExitCode or $?) to detect sql query failure and loop for couple of attempts as my code works after second attempt.

$attempts = 0
$maxAttempts = 3

  while ($attempts -lt $maxAttempts)
  {
    $attempts++
    $error.clear() # Clears teh error variable incase of any previous errors in the script

      $SqlConnection = New-Object System.Data.SqlClient.SqlConnection 
      $SqlConnection.ConnectionString = "Server=$dBServer;Database=$dBName;Integrated Security=True"  
      $SqlConnection.Open()  

      $SqlCmd = New-Object System.Data.SqlClient.SqlCommand  
      $SqlCmd.CommandText = $dBCmd  
      $SqlCmd.Connection = $sqlConnection  

      $execute = $SqlCmd.ExecuteScalar()  
      $SqlConnection.Close()  

      if ($error.count -eq 0)
      {
        write-host "Sql Query was Successful."            
        break
      }        

      else
      {   
        write-host "Sql Query failed on attempt $attempts"
        $error.clear()
      }
  }