SQL SERVER – Using PowerShell and Native Client to run queries in SQL Server

I have been getting my hands dirty working with PowerShell commands for a fun learning experience and few have made to this blog. The after effect of this is that I have been pinged for few queries on them too. Recently I got a call from one of my friend and he wanted to use PowerShell to connect to SQL and run some queries. Initially I thought that he is talking about SQL PowerShell, but his intention was to connect to SQL using SQL Server Native Client and run the query. That was strange but I was ready and up for the challenge in front of me. The curiosity is just too much that I couldn’t sleep till I found a solution to this challenge he put to me.

I wrote a small sample and sharing here for your benefit. First, we need to create a sample database and a table which would be used by PowerShell.

CREATE DATABASE SQLAuthority
GO
USE SQLAuthority
GO
CREATE TABLE EmployeeMaster (
EmployeeID INT
,FirstName VARCHAR(10)
,LastName VARCHAR(10)
)
GO

Now, our task is to use PowerShell and insert data into EmployeeMaster. Here is the step by step PowerShell script.

# Make Connecting string to the SQL Server Instance and the Database
$con = New-Object System.Data.SqlClient.SqlConnection("Data Source=.; Initial Catalog=SQLAuthority; Integrated Security=SSPI")
# Define variables and the values to be inserted
$EmployeeID = 1
$FirstName = 'Pinal'
$LastName = 'Dave'
# Open database connection
$con.Open()
#Create SQL Insert Statement for table EmployeeMaster with the values
$stmt_insert = "INSERT INTO [EmployeeMaster]
([EmployeeID],[FirstName],[LastName])
VALUES($EmployeeID ,'$FirstName' ,'$LastName')"
# Create command to execute to SQL connection
$cmd = $con.CreateCommand()
$cmd.CommandText = $stmt_insert
# Invoke the Insert statement
$cmd.ExecuteNonQuery()

Once we execute above, the data would be inserted into the table. Now, to fetch the data, I am going to use invoke-SQLCMD cmdlet

Add-PSSnapin SqlServerCmdletSnapin100
$DataSet = Invoke-Sqlcmd -Database "SQLAuthority" -Query "SELECT * FROM [EmployeeMaster]" -ServerInstance .
foreach ($element in $DataSet)
{
$element
}
# Close database Connection
$con.Close()

We can run all the statements together in one PowerShell script and use Windows PowerShell ISE to execute it.

You might get a few errors while using Invoke-SQLCMD which I have explained here

SQL SERVER – FIX – The term ‘Invoke-Sqlcmd’ is not recognized as the name of a cmdlet

Hope this would give you a head start of using PowerShell to connect to SQL and run queries. This was a cool experiment for me and I thought was worth sharing. Have you ever had such requirements? Do share your experience over comments below.

Reference : Pinal Dave (https://blog.sqlauthority.com)