Connecting to SQL Server Using PowerShell

Published Jan 15 2019 05:20 PM 6,727 Views
First published on MSDN on Nov 30, 2016
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 http://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
%3CLINGO-SUB%20id%3D%22lingo-sub-318885%22%20slang%3D%22en-US%22%3EConnecting%20to%20SQL%20Server%20Using%20PowerShell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-318885%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3EFirst%20published%20on%20MSDN%20on%20Nov%2030%2C%202016%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20In%20this%20blog%2C%20I%20would%20like%20to%20show%20you%20how%20we%20can%20connect%20to%20a%20SQL%20Server%20Instance%20using%20PowerShell%20cmdlets.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20Let%20us%20consider%20a%20situation%20where%20you%20are%20in%20a%20remote%20machine%20and%20you%20don%E2%80%99t%20have%20SQL%20Server%20Management%20Studio(SSMS)%20client%20tool%20to%20access%20the%20SQL%20Server%20and%20you%20would%20like%20to%20query%20your%20SQL%20Server.%20In%20this%20case%2C%20PowerShell%20command%20is%20one%20of%20the%20best%20way%20to%20query%20the%20data.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CSTRONG%3EUsing%20a%20SQL%20Server%20Provider%20Path%20%3C%2FSTRONG%3E%20%3A%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20SQL%20Server%3A%20HEARTTHROB%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20Instance%20Name%3A%20SQL16%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20Database%20Name%3A%20msdb%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20Schema%20Name%3A%20dbo%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20Table%20Name%3A%20sysjobs%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20This%20query%20needs%20to%20be%20run%20using%20Powershell%20(Run%20as%20Administrator)%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20Import-Module%20SQLPS%20-DisableNameChecking%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20cd%20SQLSERVER%3A%5CSQL%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%2F*%20SQL%20Cmdlets%20*%2F%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20CD%20SQL%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%2F*%20Getting%20into%20SQL%20Server%20Machine.%20For%20my%20case%20it%20is%20HEARTTHROB%20*%2F%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20cd%20HEARTTHROB%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%2F*%20Select%20the%20Instance%20%3A%20For%20the%20Named%20Instance%20Instance_Name(%20For%20my%20case%20it%20is%20SQL16)%26nbsp%3B%20For%20the%20Default%20Instance%20%3A%20DEFAULT%26nbsp%3B%20*%2F%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20CD%20SQL16%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%2F*%20Getting%20into%20Databases%20*%2F%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20CD%20Databases%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%2F*%20Select%20the%20particular%20Database%20*%2F%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20CD%20msdb%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%2F*%20Getting%20Into%20tables%20*%2F%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20CD%20Tables%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%2F*%20Getting%20Into%20Particular%20Table%20*%2F%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20CD%20dbo.sysjobs%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20Invoke-Sqlcmd%20-Query%20%22SELECT%20top%202%20*%20from%20dbo.sysjobs%3B%22%20-QueryTimeout%203%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20Invoke-Sqlcmd%20-Query%20%22SELECT%20%40%40version%3B%22%20-QueryTimeout%203%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F68508i712FADEBE01A2985%22%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20You%20can%20get%20the%20reference%20of%20this%20from%20this%20MSDN%20article%3A%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20A.%20Specify%20Instances%20in%20the%20SQL%20Server%20PowerShell%20Provider-%20%3CA%20href%3D%22https%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fhh245280.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20https%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fhh245280.aspx%20%3C%2FA%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20B.%20SQL%20Server%20Identifiers%20in%20PowerShell%20-%20%3CA%20href%3D%22https%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fcc281841.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20https%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fcc281841.aspx%20%3C%2FA%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20C.%20Specify%20Instances%20in%20the%20SQL%20Server%20PowerShell%20Provider%20-%20%3CA%20href%3D%22https%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fhh245280.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20https%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fhh245280.aspx%20%3C%2FA%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CSTRONG%3E%20Creating%20a%20Connection%20Object%3A%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20Query%3A%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%5Bstring%5D%20%24Server%3D%20%22%20HEARTTHROB%22%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%5Bstring%5D%20%24Database%20%3D%20%22USERDB%22%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%5Bstring%5D%20%24SqlQuery%3D%20%24(%22SELECT%20count%20(%20*)%26nbsp%3B%26nbsp%3B%20FROM%20%5BSales%5D%22)%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%24Command%20%3D%20New-Object%20System.Data.SQLClient.SQLCommand%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%24Command.Connection%20%3D%20%24Connection%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%24SqlConnection%20%3D%20New-Object%20System.Data.SqlClient.SqlConnection%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%24SqlConnection.ConnectionString%20%3D%20%22Server%20%3D%20%24Server%3B%20Database%20%3D%20%24Database%3B%20Integrated%20Security%20%3D%20True%3B%22%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%24SqlCmd%20%3D%20New-Object%20System.Data.SqlClient.SqlCommand%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%24SqlCmd.CommandText%20%3D%20%24SqlQuery%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%24SqlCmd.Connection%20%3D%20%24SqlConnection%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%24SqlAdapter%20%3D%20New-Object%20System.Data.SqlClient.SqlDataAdapter%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%24SqlAdapter.SelectCommand%20%3D%20%24SqlCmd%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%24DataSet%20%3D%20New-Object%20System.Data.DataSet%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%24SqlAdapter.Fill(%24DataSet)%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%24DataSet.Tables%5B0%5D%20%7C%20out-file%20%22C%3A%5Cpowershell_query_test_result2.csv%22%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CSTRONG%3E%20You%20might%20face%20the%20below%20issues%20while%20running%20the%20PS%20Commands%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20While%20importing%20%3CSTRONG%3E%20Import-Module%20SQLPS%20-DisableNameChecking%20%3C%2FSTRONG%3E%20-%20You%20might%20get%20the%20below%20error%3A%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20Import-Module%20%3A%20File%20C%3A%5CProgram%20Files%20(x86)%5CMicrosoft%20SQL%20Server%5C130%5CTools%5CPowerShell%5CModules%5CSQLPS%5CSqlps.ps1%20cannot%20be%20loaded%20because%20running%20scripts%20is%20disabled%20on%26nbsp%3Bthis%20system.%20For%20more%20information%2C%20see%20about_Execution_Policies%20at%20%3CA%20href%3D%22http%3A%2F%2Fgo.microsoft.com%2Ffwlink%2F%3FLinkID%3D135170%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20http%3A%2F%2Fgo.microsoft.com%2Ffwlink%2F%3FLinkID%3D135170%20%3C%2FA%3E%20.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20At%20line%3A1%20char%3A1%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%2B%20Import-Module%20SQLPS%20-DisableNameChecking%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%2B%20~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%2B%20CategoryInfo%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3A%20SecurityError%3A%20(%3A)%20%5BImport-Module%5D%2C%20PSSecurityException%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%2B%20FullyQualifiedErrorId%20%3A%20UnauthorizedAccess%2CMicrosoft.PowerShell.Commands.ImportModuleCommand%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20To%20fix%20it%20you%20can%20run%20the%20command%3A%20%3CSTRONG%3E%20Set-ExecutionPolicy%20RemoteSigned%20%3B%20%3C%2FSTRONG%3E%20One%20prompt%20will%20come%20up-%20click%20yes%20to%20all.While%20importing%20%3CSTRONG%3E%20Import-Module%20SQLPS%20-DisableNameChecking%20%3C%2FSTRONG%3E%20-%20-%20It%20might%20give%20you%20the%20below%20error%3A%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EWARNING%3A%20Failed%20to%20load%20the%20'SQLAS'%20extension%3A%20SQL%20Server%20WMI%20provider%20is%20not%20available%20on%20PANDA1.%20--%26gt%3B%20Invalid%20namespace%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%20You%20can%20run%20the%20below%20commands%20to%20fix%20it%3A%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%2F*%20Unrestricted%20Execution%20policy%20*%2F%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20SET%20ExecutionPolicy%20UnRestreicted%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%2F*%20Import%20the%20SQLPS%20module%20*%2F%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20Import-Module%20SQLPS%20%E2%80%93DisableNameChecking%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%2F*%20Import%20the%20SQLAS%20Commandlet*%2F%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20Import-module%20sqlascmdlets%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%2F*%20List%20SQLAS%20commands*%2F%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20Get-command%20-module%20SQLASCmdlets%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CSTRONG%3E%20Author%3A%20%3C%2FSTRONG%3E%20Samarendra%20Panda%20%E2%80%93%20Support%20Engineer%2C%20SQL%20Server%20BI%20Developer%20team%2C%20Microsoft%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CSTRONG%3E%20Reviewer%3A%20%3C%2FSTRONG%3E%20Kane%20Conway%20-%20Support%20Escalation%20Engineer%2C%20SQL%20Server%20BI%20Developer%20team%2C%20Microsoft%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-318885%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Nov%2030%2C%202016%20In%20this%20blog%2C%20I%20would%20like%20to%20show%20you%20how%20we%20can%20connect%20to%20a%20SQL%20Server%20Instance%20using%20PowerShell%20cmdlets.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-318885%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Econnectivity%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Jan 15 2019 05:20 PM
Updated by: