Month: March 2015

I often use different variety of this snippet to do stuff with MSSQL databases (and other types as well, with minor changes to the script).
The script will use SQL Auth by default.
To change this to Windows Auth, change the Connection String to:

$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBname; Integrated Security = True;"

Note: With Windows Auth, the script will run as the current user.

$SQLServer = "YourDBInstance"
$SQLDBname = "DBName"

$SQLUser = "DBuser"
$SQLPwd = "DBpassword"

$SQLQuery = @"

-- insert your query here --

"@

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection 
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBname; Integrated Security = False; User ID=$SQLUser; Password=$SQLPwd;"
  
$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) 

$SqlConnection.Close() 

$output = $DataSet.Tables[0]

– F