CODEWITHSUNDEEP
sql-serverpowershell
user3732793
user3732793

Reputation: 1977

PowerShell System.Data.SqlClient.SqlConnection shows no error but also no result

This script runs without any problems for a SQL Server connection:

[string] $connectionString = "Server=$server;Database=$database;Integrated Security = False; User ID = $uid; Password = $pwd;"

$sqlConn = New-Object System.Data.SqlClient.SqlConnection
$sqlConn.ConnectionString = $connectionString

$sqlConn.Open()
Write-Host "The connection is $($sqlConn.State)"
$command = $sqlConn.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()
$sqlConn.Close();
Write-Host "The connection is $($sqlConn.State)"

$table = new-object “System.Data.DataTable”
$table.Load($result)

But only with that result

The connection is Open
The connection is Closed

I have tried many proper SQL queries which run in Management Studio without any problems. Any hint how to properly execute and maybe check the SQL connection?

Upvotes: 0

Views: 1893

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46360

The $result variable is a SqlDataReader. You need to leave the connection open when loading the data table from the reader:

$sqlConn.Open()
Write-Host "The connection is $($sqlConn.State)"

$command = $sqlConn.CreateCommand()
$command.CommandText = $query
$table = new-object “System.Data.DataTable”
$result = $command.ExecuteReader()
$table.Load($result)

$sqlConn.Close();
Write-Host "The connection is $($sqlConn.State)"

Consider simplifying using a SqlDataAdapter:

$dataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($query, $connectionString)
$table = new-object “System.Data.DataTable”
$dataAdapter.Fill($table)

Upvotes: 2

Related Questions