As I have been migrating over my scripts from VBScript to PowerShell I have had to convert over several scripts.
One example of this is a section of code that I use to pull data from Tables in SQL databases.
This code assumes you are using SQL authentication credentials.
$account = “user”
$pw = “password”
$table = “table or view”
$db = “sql database”
$server = “sql server”
# Specify SQL Query
$SqlQuery = “Select * From $table”
# Create SQL connection object
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
#Set the ‘Connection String’ property
$SqlConnection.ConnectionString = “Server = $server; Database = $db;user = $account; password = $pw”
# Create SQLCommand object
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
#Set SQLCommand Text property (SQL Query)
$SqlCmd.CommandText = $SqlQuery
#Set the Connection property
$SqlCmd.Connection = $SqlConnection
#Create SQL Adapter repository
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
#Run SQL Connection
$SqlAdapter.SelectCommand = $SqlCmd
#Create SQL DateSet object
$DataSet = New-Object System.Data.DataSet
#Populate $DataSet with Connection results
$nRecs = $SqlAdapter.Fill($DataSet)
$nRecs | Out-Null
#Populate Table object with required Table
$ objTable = $DataSet.Tables
#Parse through the rows
ForEach($objRow In $objTable)
#Echo Column Names
In the above code you will need to supply replacements for, $account, $pw, $table, $db and $server. Depending on the database you are connecting to and the structure of the table you will want to modify the contents of $SqlQuery (SQL query string) and the $objRow.column would need to be changed to a valid column name for the table you are querying against.
I hope this helps.
All information is provided on an AS-IS basis, with no warranties and confers no rights.