As I mentionned in previous blog posts, I am currently messing around with PowerShell for SQL. For all of us newcomers in the PoShSQL world, it can very quickly become overwhelming when we start looking at all the various snapins and modules that are made available to us to interact with Microsoft SQL Server. Today I’d like to discuss a very short script (4 lines) that I have produced and that allows anyone to connect to a database and to execute any SQL Query against it to retrieve data. My script requires that you are executing it on a machine that has Microsoft SQL Server 2008 or greater installed on it. In my case, I am running it from my local machine which has an instance of Microsoft SQL server 2014 installed on.
For the purpose of this demo, I have created a local database named Inventory which contains a table Fruits as shown in Figure 1 below.
Figure 1 – Table Fruits shown via the SQL Server Management Studio
My script uses SQL Integrated Security to authenticate my user (local admin account). For my purposes, I have hardcoded the name of the SQL Server Instance, as well as the name of the table I want to execute my SQL Query against. I then ask for the user executing the PowerShell Script to type in the SQL Query to exectue as a parameter. I then pass these three value over to the Invoke-SQLCmd cmdlet provided by the SQL Server Provider snapin. My script looks like the following:
$instance = “localhost\SharePoint”
$dbName = “Inventory”
$query = Read-Host “Query”
Invoke-SQLCmd -Query $query -ServerInstance $instance -Database $dbName
Executing this script will return the values out of my Fruits table as shown in Figure 2. I have passed it the following SQL Query to retrieve all items from my Fruits table: Select * from Fruits
Figure 2 – Retrieving values from SQL Server using PowerShell
Enjoy!
