PowerShell ADO.NET: Query SQL Server Databases
─□✕

PowerShell ADO.NET: Query SQL Server Databases

PowerShell Tips Editor 4 min read
PowerShell ADO.NET: Query SQL Server Databases

Combining PowerShell automation with live database data opens powerful reporting and orchestration scenarios that pure scripts cannot match. A PowerShell SQL Server query lets you pull data directly from SQL Server into pipeline-friendly objects, pass parameters safely, and export results to CSV or Excel without writing a single line of C#. This post covers Invoke-Sqlcmd, parameterized queries, and the ADO.NET alternative for finer control.

Quick Answer / TL;DR

Install the SqlServer module, then run Invoke-Sqlcmd -ServerInstance 'server\instance' -Database 'mydb' -Query 'SELECT ...'. Results are returned as DataRow objects with property access by column name.

Install SqlServer Module

The SqlServer module from the PowerShell Gallery provides Invoke-Sqlcmd and related cmdlets. It replaces the older SQLPS module that came with SQL Server Management Studio. Install it once per machine with a single command.

# Install SqlServer module
Install-Module SqlServer -Scope CurrentUser -Force

# Verify installation
Get-Module SqlServer -ListAvailable | Select-Object Name, Version

# Import for use
Import-Module SqlServer

Run a SELECT Query with Invoke-Sqlcmd

Invoke-Sqlcmd connects to SQL Server, runs the query, and returns results as objects. Each row becomes an object with properties matching column names. Windows authentication (the current user) is used by default. For SQL authentication, add -Username and -Password parameters (or better, a -Credential object).

# Windows authentication (current user)
$results = Invoke-Sqlcmd `
    -ServerInstance 'SQL01\SQLEXPRESS' `
    -Database 'AdventureWorks' `
    -Query 'SELECT TOP 10 FirstName, LastName, EmailAddress FROM Person.Person ORDER BY LastName'

# Access results like objects
$results | Format-Table FirstName, LastName, EmailAddress -AutoSize

# Filter in PowerShell after retrieval
$results | Where-Object LastName -like 'S*'

Parameterized Queries for Security

Never concatenate user input directly into SQL strings — that is the classic SQL injection vector. Use parameterized queries by passing a -Variable hashtable and referencing variables with $(VariableName) syntax in the query. This is not standard T-SQL parameter markers but it is Invoke-Sqlcmd‘s built-in substitution mechanism.

# Safe parameterized query using -Variable
$dept = 'Engineering'
$results = Invoke-Sqlcmd `
    -ServerInstance 'SQL01' `
    -Database 'HRSystem' `
    -Query "SELECT EmployeeID, FirstName, LastName FROM Employees WHERE Department = '`$(Department)'" `
    -Variable @{ Department = $dept }

Write-Host "Found $($results.Count) employees in $dept"

Run Queries from a .sql File

For complex queries, multi-statement batches, or queries maintained by a DBA team, store the SQL in a .sql file and execute it with -InputFile. This separates SQL from PowerShell and keeps both clean.

# Execute a .sql script file
Invoke-Sqlcmd `
    -ServerInstance 'SQL01' `
    -Database 'Reporting' `
    -InputFile 'C:\Scripts\SQL\monthly_report.sql' |
    Export-Csv -Path 'C:\Reports\monthly.csv' -NoTypeInformation

Write-Host 'Report generated and exported to CSV'

Export Results to CSV

Because Invoke-Sqlcmd returns objects with named properties, piping directly to Export-Csv just works. Column names in the CSV match the SQL column aliases. Add -NoTypeInformation to omit the redundant type header row.

$timestamp = Get-Date -Format 'yyyyMMdd_HHmm'
$outputPath = "C:\Reports\inventory_$timestamp.csv"

Invoke-Sqlcmd `
    -ServerInstance 'SQL01' `
    -Database 'Inventory' `
    -Query @'
    SELECT
        p.ProductName,
        p.SKU,
        i.QuantityOnHand,
        i.LastUpdated
    FROM Products p
    JOIN Inventory i ON p.ProductID = i.ProductID
    WHERE i.QuantityOnHand < 10
    ORDER BY i.QuantityOnHand ASC
'@ |
    Export-Csv -Path $outputPath -NoTypeInformation

Write-Host "Low-stock report exported to $outputPath"

ADO.NET SqlConnection Alternative

When you need precise control over connection pooling, transaction management, or stored procedure parameters, use ADO.NET directly. It does not require the SqlServer module and works in any PowerShell version with .NET Framework or .NET Core.

# ADO.NET direct connection — no module required
$connStr = 'Server=SQL01;Database=AdventureWorks;Integrated Security=True;'
$conn = New-Object System.Data.SqlClient.SqlConnection($connStr)
$conn.Open()

$cmd = $conn.CreateCommand()
$cmd.CommandText = 'SELECT TOP 5 Name, ProductNumber FROM Production.Product'

$adapter = New-Object System.Data.SqlClient.SqlDataAdapter($cmd)
$table   = New-Object System.Data.DataTable
$adapter.Fill($table) | Out-Null

$conn.Close()
$table | Format-Table Name, ProductNumber -AutoSize

Common Errors and Fixes

  • Invoke-Sqlcmd truncates text columns — use -MaxCharLength parameter. By default, Invoke-Sqlcmd truncates varchar and nvarchar columns at 4,000 characters. Add -MaxCharLength 8000 (or higher) to retrieve full long text values from columns containing scripts, notes, or JSON.
  • SQL auth vs Windows auth connection string difference. Windows auth: Integrated Security=True (no username/password). SQL auth: User ID=sa;Password=P@ssw0rd (no Integrated Security). For Invoke-Sqlcmd, use -Username and -Password for SQL auth instead of embedding in a connection string.

Related Cmdlets / See Also

Wrapping Up

Invoke-Sqlcmd makes SQL Server queries as natural as any other PowerShell cmdlet. Use parameterized queries always, -InputFile for complex SQL, and ADO.NET when you need transaction control. Combine with Export-Csv or the ImportExcel module for polished reports delivered automatically.

#ado-net#database#invoke-sqlcmd#sql
Send-Item -To