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

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(开发者_如何学Python) 

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

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.

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

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()
      }
  }

I think you can force it go straight to tcp(1433) and skip making a named pipes connection by using

"Server=$dBServer,1433;Database=$dBName;Integrated Security=True"

Many libraries,clients try named pipes first, but typically best practice is only to have 1433 listening on your target. I think the Ozie's code works too...it is simple trying named pipes, failing, trying 1433 which is the standard way MS things often work. You could try fussign with network libaries etc as others have described, but using the right conn string is typically a good idea so you know which port you are using for firewalls etc.

I had this same issue, I managed to resolve it by moving the connection string to the same line where the connection is instantiated.

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection $conString

Not sure why, but assigning it afterwards does not work. Perhaps someone can shed some light on the why?