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

Question

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?

By the way, you should dispose your connection and reader, see stackoverflow.com/questions/42107851/… — Charlieface
– Charlieface, Commented Feb 25, 2021 at 13:13
thanks for your replies. Thought it's all covered in the accepted answer — user3732793
– user3732793, Commented Feb 25, 2021 at 14:52

Dan Guzman · Accepted Answer · 2021-02-25 12:56:10Z

2

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)

answered Feb 25, 2021 at 12:56

Dan Guzman

46.8k3 gold badges52 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related