Windows OS Hub
  • Windows Server
    • Windows Server 2022
    • Windows Server 2019
    • Windows Server 2016
    • Windows Server 2012 R2
    • Windows Server 2012
    • Windows Server 2008 R2
    • SCCM
  • Active Directory
    • Active Directory Domain Services (AD DS)
    • Group Policies
  • Windows Clients
    • Windows 11
    • Windows 10
    • Windows 8
    • Windows 7
    • Windows XP
    • MS Office
    • Outlook
  • Virtualization
    • VMWare
    • Hyper-V
    • KVM
  • PowerShell
  • Exchange
  • Cloud
    • Azure
    • Microsoft 365
    • Office 365
  • Linux
    • CentOS
    • RHEL
    • Ubuntu
  • Home
  • About

Windows OS Hub

  • Windows Server
    • Windows Server 2022
    • Windows Server 2019
    • Windows Server 2016
    • Windows Server 2012 R2
    • Windows Server 2012
    • Windows Server 2008 R2
    • SCCM
  • Active Directory
    • Active Directory Domain Services (AD DS)
    • Group Policies
  • Windows Clients
    • Windows 11
    • Windows 10
    • Windows 8
    • Windows 7
    • Windows XP
    • MS Office
    • Outlook
  • Virtualization
    • VMWare
    • Hyper-V
    • KVM
  • PowerShell
  • Exchange
  • Cloud
    • Azure
    • Microsoft 365
    • Office 365
  • Linux
    • CentOS
    • RHEL
    • Ubuntu

 Windows OS Hub / SQL Server / Querying Microsoft SQL Server (MSSQL) Database with PowerShell

November 18, 2020 PowerShellSQL Server

Querying Microsoft SQL Server (MSSQL) Database with PowerShell

In this article we will discuss all effective ways to connect to a Microsoft SQL Server and run SQL queries from PowerShell. There are many ways how you can work with SQL Server using PowerShell, and it is easy to get confused when you study lots of articles in the Web, since all of them describe different methods, and even an experienced administrator may have questions.

Contents:
  • T-SQL Queries in PowerShell Using System.Data.OleDb
  • Running SQL Query in PowerShell Using System.Data.SqlClient Class
  • SQL Query in PowerShell Using SQL Server Management Studio Module
  • Invoke-Sqlcmd Cmdlet from SQLServer PowerShell Module

T-SQL Queries in PowerShell Using System.Data.OleDb

Since PowerShell can access .NET Framework classes, you can use classes from System.Data.OleDb to execute T-SQL queries.

Here is a sample PowerShell script to connect SQL Server using System.Data.OleDb class. Let’s run a SELECT query against a table in the MS SQL database:

$dataSource = “lon-sql01\testdb”
$database = “master”
$sql = “SELECT * FROM sysdatabases”
$auth = “Integrated Security=SSPI;”
$connectionString = “Provider=sqloledb; ” +
“Data Source=$dataSource; “ +
“Initial Catalog=$database; “ +
“$auth; “
$connection = New-Object System.Data.OleDb.OleDbConnection $connectionString
$command = New-Object System.Data.OleDb.OleDbCommand $sql,$connection
$connection.Open()
$adapter = New-Object System.Data.OleDb.OleDbDataAdapter $command
$dataset = New-Object System.Data.DataSet
[void] $adapter.Fill($dataSet)
$connection.Close()
$rows=($dataset.Tables | Select-Object -Expand Rows)
echo $rows

microsoft sql server connection from powershell using OleDb.OleDbConnection class

Here is an example of a PowerShell script to execute an INSERT/UPDATE/DELETE query against MSSQL database:

$dataSource = “lon-sql01\testdb”
$database = “test”
$sql = "insert into test_table (test_col) Values ('Test')"
$auth = “Integrated Security=SSPI;”
$connectionString = “Provider=sqloledb; ” +
“Data Source=$dataSource; ” +
“Initial Catalog=$database; ” +
“$auth; “
$connection = New-Object System.Data.OleDb.OleDbConnection $connectionString
$command = New-Object System.Data.OleDb.OleDbCommand $sql,$connection
$connection.Open()
$command = New-Object data.OleDb.OleDbCommand $sql
$command.connection = $connection
$rowsAffected = $command.ExecuteNonQuery()

The $rowsAffected variable contains the number of added or changed rows. To run an update or delete query, just change the line of the SQL query in the $sql variable.

Running SQL Query in PowerShell Using System.Data.SqlClient Class

To access MS SQL Server from PowerShell, you can use another built-in .NET class – System.Data.SqlClient. Here is an example of a SELECT query in a PowerShell script with SqlClient:

$server = "lon-sql01\testdb"
$database = "Test"
$sql = "select * from test_table"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$server;Database=$database;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $sql
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]

using SqlClient.SqlCommand class in powershell to run query against microsoft sql server database

An example of an INSERT/DELETE/UPDATE query:

$server = "lon-sql01\testdb"
$database = "Test"
$sql = "insert into test_table (test_col) Values ('Test')"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$server;Database=$database;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $sql
$SqlCmd.Connection = $SqlConnection
$SqlConnection.Open()
$rowsAffected = $SqlCmd.ExecuteNonQuery();
$SqlConnection.Close()

Note. The code containing SqlClient classes is very much like the code with OleDB. These classes work in a similar way:

  1. An MSSQL server connection object is created;
  2. An object with an SQL query is created, and the connection object is assigned to it;
  3. Then in case of running a SELECT query, an adapter object is created and the query is executed in the context of this object;
  4. In case of running an INSERT/UPDATE/DELETE query, the object with the query (containing the connection object) executes the ExecuteNonQuery() method.

SQL Query in PowerShell Using SQL Server Management Studio Module

To use Microsoft.SqlServer.Smo (SMO) classes, SQL Server Management Studio must be installed on your computer.

Load the SMO module, create a new server object and then run a SELECT query:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo");
$serverInstance = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "lon-sql01\testdb"
$results = $serverInstance.Databases['test'].ExecuteWithResults('select * from test_table')
foreach ($res in $results.Tables) {
$nbsp;echo $res
}

using SQL Server Management Studio module in powpershell to run SQL query

For an insert/update/delete query, run ExecuteNonQuery:

$db = $serverInstance.Databases['test']
$db.ExecuteNonQuery("insert into test_table (test_col) Values ('123456')")

Note. You can also install SMO libraries through the NuGet Package Manager:

  1. Download nuget.exe https://www.nuget.org/downloads;
  2. Run PowerShell as an administrator and go to the directory containing nuget.exe;
  3. Run: .\nuget.exe Install Microsoft.SqlServer.SqlManagementObjects.
    nnuget install Microsoft.SqlServer.SqlManagementObjects module
  4. Microsoft.SqlServer.SqlManagementObjects folder with all DLLs will appear in the current directory;
  5. Load the SMO library into your PowerShell session from a DLL file. Add it to your script:

add-type –Path "C:\Users\username\Downloads\Microsoft.SqlServer.SqlManagementObjects.150.18208.0\lib\net45\Microsoft.SqlServer.Smo.dll"

Then SMO classes will become available for use.

Invoke-Sqlcmd Cmdlet from SQLServer PowerShell Module

To use the Invoke-Sqlcmd cmdlet, install the SqlServer for PowerShell module. Run PowerShell with the administrator privileges and execute the command:

Install-Module -Name SqlServer

(Press Y and then ENTER to accept the installer notifications.)

After the installation, you can make sure that the module has been installed correctly by running this command:

Get-Module SqlServer -ListAvailable

Install powershell Module SqlServer

The Invoke-Sqlcmd cmdlet is easier and more intuitive than other ways of connection to an Microsoft SQL Server from PowerShell. Invoke-Sqlcmd uses the same syntax for SELECT and INSERT/UPDATE/DELETE queries.

Here is an example of a Select query:

Invoke-Sqlcmd -ServerInstance "lon-sql01\testdb" -Query "sp_who"

Using PowerShell Invoke-Sqlcmd to access SQL server

This is an example of an INSERT query:

Invoke-Sqlcmd -ServerInstance "lon-sql01\testdb" -Database "test1" -Query "insert into test_table (test_col) Values (‘123321’)"

Unlike other methods, a query in the Invoke-Sqlcmd is always set in the –Query parameter.

Which SQL connection option should you use?

A choice between oledb/smo/sqlclient/invoke-sqlcmd is based on the task and the environment where you are going to run a PowerShell script.

If you want to deploy a script to multiple servers (for example, your script collects monitoring data locally), using SMO or SqlServer PowerShell module (Invoke-SQLcmd) is not reasonable, since you will have to install extra packages on the remote hosts to run the script, and it is better to avoid it if there are a lot of servers.

In its turn, the SqlServer for PowerShell module offers many other cmdlets to work with your SQL Server (you can learn more here: https://docs.microsoft.com/en-us/powershell/module/sqlserver). The module contains more commands to manage SQL Server itself.

If your script will perform non-administrative tasks (is responsible for some part of business logic, for example), it is worth to use System.Data.SqlClient/SMO, as they provide more convenient development tools. An advantage of OleDB is that it can work not only with an SQL Server, but also with Access/Oracle/Firebird/Interbase.

0 comment
3
Facebook Twitter Google + Pinterest
previous post
Password Change Notification When an AD User Password is About to Expire
next post
FAQ: Live Migration of Virtual Machines with VMWare vMotion

Related Reading

Using Previous Command History in PowerShell Console

January 31, 2023

How to Install the PowerShell Active Directory Module...

January 31, 2023

Finding Duplicate E-mail (SMTP) Addresses in Exchange

January 27, 2023

How to Disable or Uninstall Internet Explorer (IE)...

January 26, 2023

How to Delete Old User Profiles in Windows?

January 25, 2023

Leave a Comment Cancel Reply

Categories

  • Active Directory
  • Group Policies
  • Exchange Server
  • Microsoft 365
  • Azure
  • Windows 11
  • Windows 10
  • Windows Server 2022
  • Windows Server 2019
  • Windows Server 2016
  • PowerShell
  • VMWare
  • Hyper-V
  • Linux
  • MS Office

Recent Posts

  • Configure User’s Folder Redirection with Group Policy

    February 3, 2023
  • Using Previous Command History in PowerShell Console

    January 31, 2023
  • How to Install the PowerShell Active Directory Module and Manage AD?

    January 31, 2023
  • Finding Duplicate E-mail (SMTP) Addresses in Exchange

    January 27, 2023
  • How to Delete Old User Profiles in Windows?

    January 25, 2023
  • How to Install Free VMware Hypervisor (ESXi)?

    January 24, 2023
  • How to Enable TLS 1.2 on Windows?

    January 18, 2023
  • Allow or Prevent Non-Admin Users from Reboot/Shutdown Windows

    January 17, 2023
  • Fix: Can’t Extend Volume in Windows

    January 12, 2023
  • Wi-Fi (Internet) Disconnects After Sleep or Hibernation on Windows 10/11

    January 11, 2023

Follow us

woshub.com
  • Facebook
  • Twitter
  • RSS
Popular Posts
  • Installing RSAT Administration Tools on Windows 10 and 11
  • Get-ADUser: Find Active Directory User Info with PowerShell
  • How to Hide Installed Programs in Windows 10 and 11?
  • Managing Printers and Drivers with PowerShell in Windows 10 / Server 2016
  • How to Create a UEFI Bootable USB Drive to Install Windows 10 or 7?
  • PowerShell: Get Folder Sizes on Disk in Windows
  • Deploy PowerShell Active Directory Module without Installing RSAT
Footer Logo

@2014 - 2023 - Windows OS Hub. All about operating systems for sysadmins


Back To Top