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