Diagnose SQL running from Power Shell

Question

I am trying to run a SQL from Power Shell(which is on my windows 7 64 bit desktop) and the remote database host is MS SQL Server 2012.

The code is:

$Server= ".\DB_HOST_NAME"
$Database = "master"
$UserSqlQuery= $("select count(*) from [master].[sys].[some_table]")

# executes a query and populates the $datatable with the data
function ExecuteSqlQuery ($Server, $Database, $SQLQuery) {
    $Datatable = New-Object System.Data.DataTable

    $Connection = New-Object System.Data.SQLClient.SQLConnection
    $Connection.ConnectionString = "server='$Server';database='$Database';trusted_connection=true;" 
    $Connection.Open()
    $Command = New-Object System.Data.SQLClient.SQLCommand
    $Command.Connection = $Connection
    $Command.CommandText = $SQLQuery
    $Reader = $Command.ExecuteReader()
    $Datatable.Load($Reader)
    $Connection.Close()
    $Datatable
    return $Datatable
}

# declaration not necessary, but good practice
$resultsDataTable = New-Object System.Data.DataTable
$resultsDataTable = ExecuteSqlQuery $Server $Database $UserSqlQuery 

Write-Host "Statistic: " $resultsDataTable
Write-Host "Message: Transaction Delay is " $resultsDataTable.Rows.Count

When I run this from Windows PowerShell, I get following errors:

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. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)" At H:\test2.ps1:11 char:5 + $Connection.Open() + ~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : SqlException

Exception calling "ExecuteReader" with "0" argument(s): "ExecuteReader requires an open and available Connection. The connection's current state is closed." At H:\test2.ps1:15 char:5 + $Reader = $Command.ExecuteReader() + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : InvalidOperationException

Exception calling "Load" with "1" argument(s): "Value cannot be null. Parameter name: dataReader" At H:\test2.ps1:16 char:5 + $Datatable.Load($Reader) + ~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : ArgumentNullException

So the first error is at $Connection.Open() but I don't know anything about power shell to figure out what is wrong. I have tried running the same SQL on the same DB host and it does return a number.

Can I get some help to know what am I doing wrong here?

Search this site for A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found. This is not a PowerShell issue. — Ken White
DBA checked and confirmed that TCP/IP is enabled but named pipes are disabled. Could that(named pipes being disabled) be an issue? — 300

Dan Guzman Dan Guzman · Accepted Answer · 2018-05-09T02:52:41

This code:

$Server= ".\DB_HOST_NAME"

should be:

$Server= "DB_HOST_NAME"

The specification ".\DB_HOST_NAME" is interpreted as a named instance (DB_HOST_NAME) running on your local box.

Diagnose SQL running from Power Shell

1 Answers