---
title: "Getting Started with PowerShell Invoke-SQLCmd Command"
description: "The PowerShell Invoke-SQLCmd cmdlet offers a powerful way of interacting with SQL databases. Learn how to use this command in this ATA Learning tutorial!"
canonical: "https://adamtheautomator.com/powershell-invoke-sqlcmd/"
---

# Getting Started with PowerShell Invoke-SQLCmd Command

> The PowerShell Invoke-SQLCmd cmdlet offers a powerful way of interacting with SQL databases. Learn how to use this command in this ATA Learning tutorial!

Source: https://adamtheautomator.com/powershell-invoke-sqlcmd/

---

ATA Learning

Tap to hide

[

ATA Learning

](/)

*   [Home](/)
*   [Tutorials](/tutorials/)
*   [Instructors](/author/)
*   [Advertising](/advertising/)
*   [Recommended Resources](/resources/)
*   [About Adam](/about-adam/)

Search for:  

*   [](https://twitter.com/adbertram)
*   [](https://github.com/Adam-the-Automator)
*   [](https://www.linkedin.com/company/adam-the-automator-llc)
*   [](/feed/)

![Getting Started with PowerShell Invoke-SQLCmd Command](https://adamtheautomator.com/wp-content/uploads/2023/03/sql-server-management.jpg)

# Getting Started with PowerShell Invoke-SQLCmd Command

[![](https://secure.gravatar.com/avatar/bbb3aa33d8c35820d2892462d21634beb25120c1b1e62e96974f69814715f6bd?s=192&d=mm&r=g)Victor Silva](https://adamtheautomator.com/author/victorsilva/)3 April 20239 min. read

Categories: [IT Ops](/category/it-ops/)

Tags:[PowerShell](/tag/powershell/)[SQL](/tag/sql/)

Table of Contents

*   [Prerequisites](#prerequisites)
*   [Installing the SqlServer Module](#installing-the-sqlserver-module)
*   [Connecting to an SQL Server via the PowerShell Invoke-SQLCmd](#connecting-to-an-sql-server-via-the-powershell-invoke-sqlcmd)
*   [Connecting to an SQL Server with a Password as SecureString](#connecting-to-an-sql-server-with-a-password-as-securestring)
*   [Connecting to an SQL Server as a Windows User](#connecting-to-an-sql-server-as-a-windows-user)
*   [Creating and Querying Data from a Database](#creating-and-querying-data-from-a-database)
*   [Inserting Data Into a Database From an SQL File](#inserting-data-into-a-database-from-an-sql-file)
*   [Manipulating Query Outputs](#manipulating-query-outputs)
*   [Conclusion](#conclusion)

There is no question that SQL server management in a GUI platform works great. But if traditional T-SQL queries are more of your expertise, you will find the [PowerShell `Invoke-SQLCmd` cmdlet](https://learn.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps) enjoyable.

The `Invoke-SqlCmd` cmdlet provides a more PowerShell-oriented experience for working with SQL Server databases. And in this tutorial, you will start managing your SQL databases with the `Invoke-SqlCmd` cmdlet.

Dive in and start streamlining your SQL database management!

## Prerequisites

Before jumping to the PowerShell `Invoke-SQLCmd` command, be sure you have the following in place to follow along with this tutorial:

*   A computer to run PowerShell – This tutorial uses Windows Server 2022 with PowerShell v7.3.X.

Related:[PowerShell 7 Upgrade : A How to Walk Through](https://adamtheautomator.com/powershell-7-upgrade/)

*   A Microsoft SQL Server with an existing database – This tutorial uses an SQL 2022 Standard edition with a database called EmployeesDB.

Related:[Getting Started With Microsoft SQL Server Standard Edition](https://adamtheautomator.com/microsoft-sql-server-standard-edition/)

*   An [SQL Server user](https://www.guru99.com/sql-server-create-user.html#create-new-login-in-sql-server) with privileges to connect to the server and make actions.

## Installing the SqlServer Module

With the `Invoke-SqlCmd` cmdlet, you can execute SQL queries and scripts within a PowerShell console. As a result, you can automate database management tasks and integrate SQL Server with other PowerShell commands and modules. And yes, without having to switch between different tools and interfaces.

But like other cmdlets, to take advantage of the `Invoke-SQLCmd` cmdlet, you must install the SqlServer module. This module supports the latest SQL features that allow you to automate database development and server administration.

Related:[Understanding and Building PowerShell Modules](https://adamtheautomator.com/powershell-modules/)

1\. Open PowerShell as administrator and run the following [`Install-Module`](https://learn.microsoft.com/en-us/powershell/module/powershellget/install-module) command to download and install the `SQLServer` module from the PowerShell gallery.

```powershell
Install-Module SQLServer
```

Related:[Getting Started with PowerShell Gallery](https://adamtheautomator.com/powershell-gallery/)

Input **Y** and press Enter when prompted to proceed with the installation.

![powershell invoke-sqlcmd Installing the SqlServer module](https://adamtheautomator.com/wp-content/uploads/2023/03/image-249.png)

Installing the SqlServer module

2\. Once installed, run the below [`Import-Module`](https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.core/import-module) command, which does not provide output, but enables all commands related to the SqlServer module.

```
Import-Module SQLServer
```

Related:[How to Import PowerShell Modules Like a Boss](https://adamtheautomator.com/import-powershell-modules/)

3\. Now, run each command below, which does not produce output but sets variables containing appropriate data for the server name, username, and password. Doing so lets you streamline your workflow and avoid manually entering commonly used data when needed.

Related:[Stop the Confusion: Keep End Users Secure with Strong Passwords](https://adamtheautomator.com/strong-passwords/)

Using these variables consistently throughout your scripts and commands saves time and reduces errors caused by typos or other mistakes.

```powershell
# Set the SQL server name
$serverName = "mssql-vm"
# Set the SQL server credentials
$adminSqlLogin = "vmsilvamolina"
$password = "S3cur3P4ss@1"
```

> _Perhaps you plan for these variables to persist. If so, set them as environment variables you can call when needed._

Related:[PowerShell Environment Variables: The Ultimate Guide](https://adamtheautomator.com/powershell-environment-variables/)

## Connecting to an SQL Server via the PowerShell `Invoke-SQLCmd`

With the SqlServer module installed, you are ready to get started with the PowerShell `Invoke-SQLCmd` cmdlet. This cmdlet lets you connect to your SQL server and run a query.

Before you can query your SQL server, ensure you have the variables you set in the last step of the “Installing the SqlServer Module” section handy.

Run the following `Invoke-SQLCmd` command, which does not provide output but performs the following:

*   Connect to your SQL server (`$serverName`).
*   Authorize the connection with the specified credentials (`$adminSqlLogin` and `$password`).
*   Run a `-Query` to your SQL server to `PRINT` the `Hello world` text.

```powershell
Invoke-SQLCmd -ServerInstance $serverName -Username $adminSqlLogin -Password $password -Query "PRINT 'Hello world'"
```

> _Note that the `-Username` parameter can only specify an SQL account, not a Windows account._

Did nothing happen? Not exactly. If you do not see errors, everything went well.

Now, run the same command in step one, but this time, with a `-Debug` parameter to expose more information about the operation performed by the command.

```powershell
Invoke-SQLCmd -ServerInstance $serverName -Username $adminSqlLogin -Password $password -Query "PRINT 'Hello world'" -Debug
```

Perfect! In the output below, the query shows the result with no errors. Input Y and press Enter to confirm running the operation.

![Getting more information when connecting to the SQL server](https://adamtheautomator.com/wp-content/uploads/2023/03/image-250.png)

Getting more information when connecting to the SQL server

## Connecting to an SQL Server with a Password as SecureString

You have successfully connected to your SQL server using variables that hold your credentials, which undoubtedly works. But note that hard-coding or putting your password as it is to a variable leaves it at risk. Why not try converting your password to [SecureString](https://learn.microsoft.com/en-us/dotnet/api/system.security.securestring)?

> _PowerShell offers the [secret management module](https://adamtheautomator.com/powershell-encrypt-password/) if you wish to encrypt your password instead for added security._

Run the following commands to perform the following:

*   [`ConvertTo-SecureString`](https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.security/convertto-securestring) – Convert your `$password` variable’s string value to a secure string stored to `$secStringPassword`.
*   `New-Object` – Create a [PSCredential](https://learn.microsoft.com/en-us/dotnet/api/system.management.automation.pscredential) object with a username (`$adminSqlLogin`) and password (`$secStringPassword`).

```powershell
# Convert the $password variable's value to SecureString
$secStringPassword = ConvertTo-SecureString $password -AsPlainText -Force
# Create a credential object
$credObject = New-Object System.Management.Automation.PSCredential ($adminSqlLogin, $secStringPassword)
```

Now, run the following command to connect to your SQL server and run the same query of printing the `Hello world` text. This time, use the credential object you created in the previous step (`$credObject`) to authorize the connection.

```powershell
Invoke-SQLCmd -ServerInstance $serverName -Credential $credObject -Query "PRINT 'Hello world'" -Debug
```

![Connecting to the SQL server using a secure password](https://adamtheautomator.com/wp-content/uploads/2023/03/image-251.png)

Connecting to the SQL server using a secure password

## Connecting to an SQL Server as a Windows User

When querying data, you typically want to execute commands with the appropriate permissions and access levels. Suppose you need to run your command as a Windows user. If so, you must launch a new PowerShell session as that specific Windows user.

To query data as a Windows user without relying on the `-Username` parameter:

Run the following command to start a PowerShell background job ([`Start-Job`](https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.core/start-job)) within a `-ScriptBlock`.

The background job runs to `-Query` data from the `sys.databases` table as a Windows user (`<USERNAME>`) authenticated with its credentials.

```powershell
Start-Job -ScriptBlock { `
Invoke-SQLCmd -ServerInstance $serverName -Query "select * from sys.databases"`
} -Credential (Get-Credential -Credential <USERNAME>)
```

Take note of the job’s **Name** (i.e., **Job20**), as shown below. You will need the job name to verify the job in the following step.

![Querying Data as a Windows User via a PowerShell background job](https://adamtheautomator.com/wp-content/uploads/2023/03/image-252.png)

Querying Data as a Windows User via a PowerShell background job

Now, run the below command to get (`Get-Job`) and receive (`Receive-Job`) all PowerShell background jobs. Ensure you change `<jobName>` with the one job name you noted in the previous step.

The `-Keep` parameter allows you to retrieve the collected stream data again after the first view, printed in table format (`Format-Table`).

```powershell
Get-Job -Name <jobName> | Receive-Job -Keep | Format-Table
```

![Getting all PowerShell background jobs](https://adamtheautomator.com/wp-content/uploads/2023/03/image-253.png)

Getting all PowerShell background jobs

## Creating and Querying Data from a Database

Since you can now connect to your SQL server, it is time to start working more deeply with the `Invoke-SQLCmd` command’s parameters. First, you will connect to a specific database and create a table with data to query.

1\. Run the following commands to set a database name to a variable (`$databaseName`) and run a `-Query` to confirm the database access.

```powershell
# Add a new variable for the database name
$databaseName = "EmployeesDB"
# Run a query to confirm the access
Invoke-SQLCmd -ServerInstance $serverName `
		-Database $databaseName `
		-Credential $credObject `
		-Query "select Count(*) from sys.tables"
```

The query returns the number of tables created on the database, as shown below:

![Setting a database name](https://adamtheautomator.com/wp-content/uploads/2023/03/image-254.png)

Setting a database name

2\. Next, execute the following commands to run a `-Query` to `CREATE` a `TABLE` called `EmployeeData` and `INSERT` two entries (records).

These commands do not provide output but create a table with columns called `EmployeeID`, `EmployeeName`, and `EmployeeHireDate`.

```powershell
$query = @"
CREATE TABLE EmployeeData (
    EmployeeID int,
    EmployeeName varchar(255),
    EmployeeHireDate varchar(255)
);
INSERT INTO EmployeeData(EmployeeID, EmployeeName, EmployeeHireDate) 
VALUES (1, 'Victor', '2023-01-01');
INSERT INTO EmployeeData(EmployeeID, EmployeeName, EmployeeHireDate) 
VALUES (2, 'Manuel', '2023-01-01');
"@
Invoke-SQLCmd -ServerInstance "$serverName" `
		-Database $databaseName `
		-Credential $credObject `
		-Query $query
```

3\. After creating a table, run the below command to retrieve data from the newly-created table (`EmployeeData`). The information is stored in `$EmployeesDB`, a PowerShell object you can access and manipulate like any other PowerShell object.

Related:[Back to Basics: Understanding PowerShell Objects](https://adamtheautomator.com/powershell-objects/)

This command does not produce output, but you will access the data in the following step.

```powershell
$EmployeesDB = Invoke-SQLCmd -ServerInstance "$serverName" `
		-Database $databaseName `
		-Credential $credObject `
		-Query "SELECT * FROM Employees;"
```

Did you get an error? Errors occur at some point that you must learn how to handle, and below are some of them:

*   The error below shows up because the queried table name needs to be corrected. Rectify the specified table name from `Employees` to `EmployeeData`, and the query will be successful.

![Getting an error from an incorrectly specified table name](https://adamtheautomator.com/wp-content/uploads/2023/03/image-255.png)

Getting an error from an incorrectly specified table name

*   Perhaps you wish to abort the query execution immediately after getting an error. If so, using the `-AbortOnError` parameter. Like in the screenshot below, you will see information about the error.

![Aborting query execution upon getting an error](https://adamtheautomator.com/wp-content/uploads/2023/03/image-256.png)

Aborting query execution upon getting an error

*   Or you can just suppress error messages from popping up with the `-OutputSqlErrors` parameter with a `$false` value, as shown below. Note that this parameter only accepts Boolean values.

![Suppressing error messages](https://adamtheautomator.com/wp-content/uploads/2023/03/image-257.png)

Suppressing error messages

4\. Lastly, run the below [`foreach`](https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_foreach) statement to access and output the data in the `$EmployeesDB` object to your PowerShell console.

Related:[Back to Basics: The PowerShell Foreach Loop](https://adamtheautomator.com/powershell-foreach/)

```powershell
# Print the queried information from EmployeesDB
foreach ($Employee in $EmployeesDB) {
	Write-Output "Employee ID: $($Employee.EmployeeID)"
	Write-Output "Employee Name: $($Employee.EmployeeName)"
	Write-Output "Employee Hire Date: $($Employee.EmployeeHireDate)"
	Write-Output ""
}
```

As you can see below, with the [`Write-Output`](https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/write-output) commands, the retrieved data are printed to the console.

Related:[PowerShell Write-Output: Your Friendly Output Companion](https://adamtheautomator.com/powershell-write-output/)

![Showing the EmployeeData table’s content](https://adamtheautomator.com/wp-content/uploads/2023/03/image-258.png)

Showing the EmployeeData table’s content

## Inserting Data Into a Database From an SQL File

Successfully inserting and querying data felt good, right? But when you have to insert multiple entries, writing the query for each entry can be tedious. Worry not, though. The `InputFile` parameter with the `Invoke-SQLCmd` command will come in handy.

Instead of manually writing the queries, use an SQL file with one `INSERT` query and multiple values of each entry, as follows:

1\. Open your preferred text/code editor, populate the following query to the file and save it as _run.sql_. Notice below that you only have to focus on writing the values for each entry.

The query below inserts new employees to your `EmployeeData` table.

Related:[How to Edit Files with a Real PowerShell Text Editor](https://adamtheautomator.com/powershell-text-editor/)

```powershell
INSERT INTO EmployeeData (
    EmployeeID,
    EmployeeName,
    EmployeeHireDate
)
VALUES
    (
        3,
        'Jhon',
        '2023-01-02'
    ),
    (
        4,
        'Mike',
        '2023-01-02'
    ),
    (
        5,
        'Peter',
        '2023-01-02'
    );
```

2\. Next, execute the following command, which does not provide output, but runs the query from your SQL file (`C:\run.sql`).

```powershell
$EmployeesDB = Invoke-SQLCmd -ServerInstance "$serverName" `
		-Database $databaseName `
		-Credential $credObject `
		-InputFile "C:\run.sql"
```

3\. Now, run the below foreach statement to output data from your `EmployeesDB` table.

```powershell
foreach ($Employee in $EmployeesDB) {
	Write-Output "Employee ID: $($Employee.EmployeeID)"
	Write-Output "Employee Name: $($Employee.EmployeeName)"
	Write-Output "Employee Hire Date: $($Employee.EmployeeHireDate)"
	Write-Output ""
}
```

Below, you can see data from your EmployeesDB table including the three newly-inserted employee data via the _run.sql_ file.

![Querying data from the EmployeesDB table including inserted data from the .sql file ](https://adamtheautomator.com/wp-content/uploads/2023/03/image-259.png)

Querying data from the EmployeesDB table including inserted data from the _.sql_ file

> _If you need to specify the number of seconds before the query time out, use the `-QueryTimeout` parameter, as shown below. By default, when you run an `Invoke-SQLcmd` command without the timeout parameter, queries do not time out. The `-QueryTimeout` parameter indicates how long the command waits for an SQL request to complete._

```powershell
Invoke-SQLCmd -ServerInstance "$serverName" `
		-Database $databaseName `
		-Credential $credObject `
		-InputFile "C:\run.sql" `
		-QueryTimeout 5
```

## Manipulating Query Outputs

Overall, you have seen how versatile and powerful is the PowerShell `Invoke-SQLCmd` command for executing SQL queries and scripts. But did you know you can customize the `Invoke-SQLCmd` command’s output to suit your specific requirements? Yes, by appending the `-OutputAs` parameter.

You can integrate your customized output with other PowerShell commands and modules to automate and streamline your database management tasks.

To see how you can manipulate query outputs:

1\. Run the following command to `-Query` data from the `EmployeeData` table and store the output as `DataRows` to `$EmployeesDB`. This parameter lets you display query results to a table-like format.

This command does not print output to the console, but you will verify the queried data in the following

```powershell
# Store data in a variable
$EmployeesDB = Invoke-SQLCmd -ServerInstance "$serverName" `
		-Database $databaseName `
		-Credential $credObject `
		-Query "SELECT * FROM EmployeeData;" `
		-OutputAs DataRows
```

2\. Next, call the $EmployeesDB variable to verify the stored data.

`$EmployeesDB`

![Displaying the query result as DataRows (table format) ](https://adamtheautomator.com/wp-content/uploads/2023/03/image-260.png)

Displaying the query result as DataRows (table format)

Or append a period, and keep pressing TAB to cycle through different functions you may use, as shown below.

![Cycling through different functions](https://adamtheautomator.com/wp-content/uploads/2023/03/image-1.gif)

Cycling through different functions

3\. Run the same command below as in the previous step, but this time, replace the `DataRows` value with `DataSet`. This parameter value lets you save the query results into an XML-formatted object (`$xmlEmployeeData`).

Like the previous example, this command does not produce output to the console, but you will verify the output in the next step.

```powershell
$xmlEmployeeData = Invoke-SQLCmd -ServerInstance "$serverName" `
		-Database $databaseName `
		-Credential $credObject `
		-Query "SELECT * FROM EmployeeData;" `
		-OutputAs DataSet
```

4\. Finally, call `$xmlEmployeeData` and verify the XML-formatted object.

```powershell
$xmlEmployeeData
```

![Verifying the data stored in XML format ](https://adamtheautomator.com/wp-content/uploads/2023/03/image-261.png)

Verifying the data stored in XML format

Or run the below command to view the data in a table format (`.Tables`) like in step two.

```powershell
$xmlEmployeeData.Tables
```

![Viewing data in table format](https://adamtheautomator.com/wp-content/uploads/2023/03/image-262.png)

Viewing data in table format

## Conclusion

Throughout this tutorial, you have taken the first steps to learning the PowerShell `Invoke-SQLCmd` command. You have successfully connected to an SQL database, run different queries, and manipulated result outputs.

Working within a GUI, like the SQL Server Management Studio, certainly makes database management less disheartening. But if you know the `Invoke-SQLCmd` command like the back of your hand, you can accomplish more with PowerShell.

Related:[Getting Started With SQL Server Management Studio (SSMS)](https://adamtheautomator.com/sql-server-management-studio/)

Why not automate daily tasks with the `Invoke-SQLCmd` command? Or extract data from your SQL servers to manipulate with PowerShell? Whichever next step you choose, good luck in your journey to becoming a PowerShell or DBA expert!

Share this article

[Share on X](https://twitter.com/intent/tweet?url=https%3A%2F%2Fadamtheautomator.com%2Fpowershell-invoke-sqlcmd%2F&text=Getting%20Started%20with%20PowerShell%20Invoke-SQLCmd%20Command)[Share on Facebook](https://www.facebook.com/sharer/sharer.php?u=https%3A%2F%2Fadamtheautomator.com%2Fpowershell-invoke-sqlcmd%2F)[Share on LinkedIn](https://www.linkedin.com/sharing/share-offsite/?url=https%3A%2F%2Fadamtheautomator.com%2Fpowershell-invoke-sqlcmd%2F)

## Related Posts

![](https://adamtheautomator.com/wp-content/uploads/publisher/3ec5d9c85b2b8183bec6e5cc0f0356c5/a323a4d03629188de1d5ed18ca60a64a65f94bef73d5390488bde5511859fa05.webp)

### [AZ-800 vs. AZ-802: Avoid the Wrong Windows Server Exam](/az-800-vs-az-802/)

See what the AZ-800 retirement means for your Windows Server certification path and how to re-weight your study plan around the current AZ-802 exam.

![](https://adamtheautomator.com/wp-content/uploads/publisher/3ec5d9c85b2b811089d5d31807a89aa3/4f6fca0bf1e9083ef5217f16ceed8f939aa4f322e0b4be8d8e3dbb7be8ca3114.webp)

### [Automate Windows 11 Context Menu Actions with Registry](/automate-windows-11-context-menu-registry/)

Add custom right-click actions to Windows 11 context menus by editing registry verbs, deploying .reg files, and scripting with PowerShell.

![](https://adamtheautomator.com/wp-content/uploads/publisher/3ec5d9c85b2b81d9ab7cd5dbb6caf044/21b786d67f19d1813150edf9871e36ead78714b63880de8c6161c7fde11a9769.webp)

### [Pktmon: Capture Windows Traffic Without a Third-Party Driver](/pktmon-capture-windows-traffic/)

Use the in-box Windows Pktmon tool to capture packets and packet-drop reasons, then convert the ETL to pcapng and text for offline analysis.

## Categories

*   [IT Ops](/category/it-ops/)
*   [Cloud](/category/cloud/)
*   [DevOps](/category/devops/)
*   [Home Ops](/category/home-ops/)
*   [Information Security](/category/infosec/)
*   [Software Development](/category/software-development/)

## Site

*   [Home](/)
*   [Tutorials](/tutorials/)
*   [Instructors](/author/)
*   [Advertising](/advertising/)
*   [Recommended Resources](/resources/)
*   [About Adam](/about-adam/)

Copyright 2026© ATA Learning | [Privacy Policy](/privacy/)