PowerShell for SQL Server

Question

Hi. I need some help with PowerShell to iterate list of SQL instances and run a query from an inputfile against those instances. It's straightforward enough to read a list of SQL instance names (and port numbers) from a .CSV file using FOR EACH and hold those values in variables and loop through them. I can also embed a SQL query in the PS code like:

$query = "SELECT '$instance,$port'"

$results = Invoke-Sqlcmd -ServerInstance "$instance,$port" -Query $query -Verbose

$results | format-table

And that outputs:

 Column1                      

 -------                      

 myservername\myinstancename,myportnumber

But how do I pass the values read from the .CSV into a query in an inputfile? So far, my inputfile looks like this:

SELECT '$instance,$port' AS [INSTANCE]

...and that just outputs this:

 INSTANCE       

 --------       

 $instance,$port

Thanks.

Answer 1

Hello,

The reason your query file is outputting $instance,$port literally is because PowerShell does not expand variables inside the SQL script when you pass it as an external file. The SQL engine only sees the text in the file, not the PowerShell variables. To solve this, you need to read the query file into PowerShell as a string, perform variable substitution there, and then pass the resulting query string to Invoke-Sqlcmd.

For example, instead of letting SQL interpret $instance and $port, you should load the file with Get-Content and replace placeholders before execution. A common approach is to use a token in the SQL file, such as {{INSTANCE}} and {{PORT}}, then in PowerShell do something like:

powershell

$queryTemplate = Get-Content "C:\path\to\query.sql" -Raw

$query = $queryTemplate -replace "{{INSTANCE}}", $instance -replace "{{PORT}}", $port

$results = Invoke-Sqlcmd -ServerInstance "$instance,$port" -Query $query

$results | Format-Table

This way, the substitution happens in PowerShell before the query is sent to SQL Server. If you keep $instance inside the SQL file, SQL Server will never expand it because it doesn’t know about PowerShell variables.

So the correct method is: treat the SQL file as a template, replace placeholders with PowerShell variables, then run the modified query string against each instance. That ensures the values from your CSV are injected properly into the query at runtime.

I hope you've found something useful here. If it helps you get more insight into the issue, it's appreciated to accept the answer. Should you have more questions, feel free to leave a message. Have a nice day!

Domic Vo.

Answer 2

Use PowerShell variables as SQLCMD variables and reference them in the input file with $(...) syntax, then pass them with the -Variable parameter of Invoke-Sqlcmd.

Example PowerShell (looping over CSV rows):

# Example: CSV has columns Instance, Port
$servers = Import-Csv .\servers.csv

foreach ($s in $servers) {
    $instance = $s.Instance
    $port     = $s.Port

    # Build SQLCMD variable assignments
    $vars = "instance='$instance'", "port='$port'"

    Invoke-Sqlcmd \
        -ServerInstance "$instance,$port" \
        -InputFile 'C:\Path\To\query.sql' \
        -Variable $vars \
        -Verbose | Format-Table
}

Then in the query.sql input file, use SQLCMD variable syntax (note the backtick to escape $ in PowerShell if you inline the text there, but in a .sql file you just write it literally):

SELECT '$(instance),$(port)' AS [INSTANCE];

Invoke-Sqlcmd will replace $(instance) and $(port) with the values passed via -Variable before executing the query, so the result will show the actual instance and port instead of the literal $instance,$port text.

---

References:

• Context # Invoke-Sqlcmd