Powershell: SqlCommand Where clause always returns empty dataset

Question

I have a PowerShell script that is reading some data from a SQL Server using the standard SqlConnection, SqlCommand approach.

When I execute the following SELECT Bob, fred FROM x I get a list of values.

However, if I try and add a filter SELECT Bob, fred FROM x WHERE fred = 'abc' I get nothing - it doesn't matter what the filter is, it always returns an empty dataset - pasting the query into SQL Server returns rows.

I am using SqlDataAdapter.Fill to return a DataSet. Have tried using a DataTable and a Reader (executeReader) but the same result.

I can't select the entire table as there are too many rows.

Does anyone know how to filter rows in a query without having to return the entire table?

Where is your powershell command? Sounds like a problem with your SQL commands and not the query itself. — Drew
– Drew, Commented Aug 9, 2019 at 0:55
If you fully qualify the table name (i.e. [Database].[dbo].[Table]) do you get a result? — Rob
– Rob, Commented Aug 9, 2019 at 2:42
Try updating your question so that others can replicate your problem. Include some example Powershell code, an example SQL table schema and some rows of data to put in it (doesn't need to be millions of rows, two or three would be sufficient). — FooMonkey
– FooMonkey, Commented Aug 9, 2019 at 5:51

FooMonkey · Accepted Answer · 2019-08-09 06:07:39Z

Given the description in your question I'm assuming you're missing an | Out-Null somewhere in your PowerShell code so your function is returning something unexpected to your caller.

Let's assume you're starting with an SQL table such as the following:

if object_id(N'dbo.x') is not null drop table dbo.x;
create table dbo.x (
    Bob nvarchar(50) not null,
    Fred nvarchar(50) not null
);
insert dbo.x (Bob, Fred) values
    ('Alice', 'abc'),
    ('Bob', 'bcd'),
    ('Charlie', 'cde');
go

The following PowerShell script uses a parameterized query to return a single row from that table into a DataTable...

Get-Data.ps1:

$connectionString = "Data Source=(local);Integrated Security=SSPI;"
$conn = New-Object System.Data.SqlClient.SQLConnection -ArgumentList $connectionString
$conn.Open()

$command = New-Object System.Data.SqlClient.SqlCommand -ArgumentList $connection
$command.Connection = $conn
$command.CommandText = "SELECT Bob, fred FROM x WHERE fred = @1"
$command.Parameters.AddWithValue("@1", "abc") | Out-Null

$adapter = New-Object System.Data.SqlClient.SqlDataAdapter -ArgumentList $command
$dataTable = New-Object System.Data.DataTable
$adapter.Fill($dataTable) | Out-Null

$dataTable | Format-Table

And when you execute it, you get:

.\Get-Data.ps1

Bob   fred
---   ----
Alice abc

Collectives™ on Stack Overflow

Powershell: SqlCommand Where clause always returns empty dataset

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related