Connecting to SQL Server Using PowerShell

In this blog, I would like to show you how we can connect to a SQL Server Instance using PowerShell cmdlets.

Let us consider a situation where you are in a remote machine and you don’t have SQL Server Management Studio(SSMS) client tool to access the SQL Server and you would like to query your SQL Server. In this case, PowerShell command is one of the best way to query the data.

Using a SQL Server Provider Path:

SQL Server: HEARTTHROB

Instance Name: SQL16

Database Name: msdb

Schema Name: dbo

Table Name: sysjobs

This query needs to be run using Powershell (Run as Administrator)

Import-Module SQLPS -DisableNameChecking

cd SQLSERVER:\SQL

/* SQL Cmdlets */

CD SQL

/* Getting into SQL Server Machine. For my case it is HEARTTHROB */

cd HEARTTHROB

/* Select the Instance : For the Named Instance Instance_Name( For my case it is SQL16)  For the Default Instance : DEFAULT  */

CD SQL16

/* Getting into Databases */

CD Databases

/* Select the particular Database */

CD msdb

/* Getting Into tables */

CD Tables

/* Getting Into Particular Table */

CD dbo.sysjobs

Invoke-Sqlcmd -Query "SELECT top 2 * from dbo.sysjobs;" -QueryTimeout 3

Invoke-Sqlcmd -Query "SELECT @@version;" -QueryTimeout 3

You can get the reference of this from this MSDN article:

A. Specify Instances in the SQL Server PowerShell Provider- https://msdn.microsoft.com/en-us/library/hh245280.aspx

B. SQL Server Identifiers in PowerShell - https://msdn.microsoft.com/en-us/library/cc281841.aspx

C. Specify Instances in the SQL Server PowerShell Provider - https://msdn.microsoft.com/en-us/library/hh245280.aspx

Creating a Connection Object:

Query:

[string] $Server= " HEARTTHROB"

[string] $Database = "USERDB"

[string] $SqlQuery= $("SELECT count ( *)   FROM [Sales]")

$Command = New-Object System.Data.SQLClient.SQLCommand

$Command.Connection = $Connection

$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 = $SqlQuery

$SqlCmd.Connection = $SqlConnection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

$SqlAdapter.SelectCommand = $SqlCmd

$DataSet = New-Object System.Data.DataSet

$SqlAdapter.Fill($DataSet)

$DataSet.Tables[0] | out-file "C:\powershell_query_test_result2.csv"

You might face the below issues while running the PS Commands

While importing Import-Module SQLPS -DisableNameChecking - You might get the below error:

Import-Module : File C:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules\SQLPS\Sqlps.ps1 cannot be loaded because running scripts is disabled on this system. For more information, see about_Execution_Policies at https://go.microsoft.com/fwlink/?LinkID=135170.

At line:1 char:1

+ Import-Module SQLPS -DisableNameChecking

+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

+ CategoryInfo          : SecurityError: (:) [Import-Module], PSSecurityException

+ FullyQualifiedErrorId : UnauthorizedAccess,Microsoft.PowerShell.Commands.ImportModuleCommand

To fix it you can run the command: Set-ExecutionPolicy RemoteSigned ; One prompt will come up- click yes to all.While importing Import-Module SQLPS -DisableNameChecking- - It might give you the below error:

• WARNING: Failed to load the 'SQLAS' extension: SQL Server WMI provider is not available on PANDA1. --> Invalid namespace

You can run the below commands to fix it:

/* Unrestricted Execution policy */

SET ExecutionPolicy UnRestreicted

/* Import the SQLPS module */

Import-Module SQLPS –DisableNameChecking

/* Import the SQLAS Commandlet*/

Import-module sqlascmdlets

/* List SQLAS commands*/

Get-command -module SQLASCmdlets

Author:       Samarendra Panda – Support Engineer, SQL Server BI Developer team, Microsoft

Reviewer:   Kane Conway - Support Escalation Engineer, SQL Server BI Developer team, Microsoft