PowerShell: Querying Microsoft SQL Server with Invoke SqlCmd

To administer Microsoft SQL Server and run T-SQL queries, most developers and administrators use the SQL Server Management Studio (SSMS) GUI or VSCode with extensions. But in some scenarios, you need to get or write data to the MSSQL database from the command line or PowerShell script. To run queries against SQL Server databases, you can use the SQLCMD tool or the Invoke SqlCmd cmdlet.

Here’s how to use PowerShell to connect to a SQL Server database and run SQL queries.

The Invoke-SqlCmd cmdlet is part of the SqlServer module. You can install it from the online PowerShell Gallery using the command:

Install-Module -Name SqlServer –AllowClobber

To verify that you have installed the SQL PowerShell module, run the following command:

Get-Module sqlserver

List the commands available in the module:

Import-Module sqlserver

Get-Command -Module SqlServer

Hint. You can install the SqlServer module and use the Invoke-Sqlcmd on MacOS and Linux.

Now let’s look at how to connect to a local or remote SQL Server instance.

Specify your MSSQL instance name:

$SQLinst ="ny-sql01\SQLEXPRESS"

Note. The following commands list all available instances on the current computer:

Set-Location SQLSERVER:\SQL\localhost

Get-ChildItem

Request credentials to connect to SQL Server. You can specify your local user account credentials if mixed or Windows authentication is enabled in MSSQL:

$cred = Get-Credential

In order to connect to a Microsoft SQL instance and to list the database, run the following command:

Get-SqlDatabase -ServerInstance $SQLinst -Credential $cred

Note. You can use the -Password option to connect to SQL Server using a clear text password.

Now, using Invoke-SqlCmd, you can connect to any database and run a simple SQL query:

Invoke-Sqlcmd -ServerInstance $SQLinst -Credential $cred -Database sales -Query "SELECT * FROM sales.customers"

Note. Supported commands are T-SQL statements and the XQuery subset.

An error may occur when connecting to the database:

Invoke-Sqlcmd: A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 – The certificate chain was issued by an authority that is not trusted.)

In this case, you will need to add the -TrustServerCertificate parameter to the Invoke-Sqlcmd command.

You can export SQL table query results to a CSV file. To do that, add the following pipe to the command ((| Export-Csv -Path C:\VSCode\SqlResult.csv -Delimiter “;”), or view the results in a graphic GridView table (|Out-GridView).

Note. Check our article on using Tnsnames.ora file in SQL Developer.

The SQL connection parameters and the database name can be stored in a hash table so that they do not have to be specified in the command each time:

$params = @{Credential=$cred; Database='sales'; ServerInstance=$SQLinst}

In order to execute an SQL query, you now need to run:

$query = "SELECT * FROM sales"

Invoke-Sqlcmd @params -Query $query | ft

By default, Invoke-Sqlcmd returns a series of System.Data.DataRow type rows. You can view this format as a PowerShell object as follows:

$table= Invoke-Sqlcmd @params -Query $query

write-host ($table | Format-Table | Out-String)

If your SQL query is stored in a text file, you can run it using the -InputFiles parameter:

Invoke-Sqlcmd @params -InputFile "C:\VSCode\MySqlScript.sql"

You can run not only SELECT queries, but also other types of queries:

• Create table:

  $query = "CREATE TABLE resellers (Name nvarchar(40), City nvarchar(30), email nvarchar(50))"
  
  Invoke-Sqlcmd -Query $query @params

• Insert a new value to the table:

  $city = "Oakland"
  
  $name = "alex2000"
  
  $email = "alex2000@msn.com"
  
  $query = "INSERT INTO resellers (Name, City, email) VALUES('$name', '$city', '$email')"
  
  Invoke-Sqlcmd @params -Query $query