Connecting Powershell to SQL Server
Using Powershell to connect to SQL Server comes in handy if you want to automatically run queries on a schedule. Maybe output the results to a file or email them. It can also come in handy if you want to run the same query on multiple servers or databases. Here I will cover a few use cases.
First case, just connecting and running a query.
$SQLServer = "SampleServer" #Enter the server name here $SQLDBName = "SampleDatabase" #Enter the database name here $SqlQuery = "SELECT * FROM Sample.Table" $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True" # Integrated security will require the user running have permission to the server and DB. $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() $DataSet.Tables[0]
So now you may ask what if you want to put or send that information somehwere. Easy, just use out-file to send the results to a file. Don’t forget the pipe.
$DataSet.Tables[0]|Out-File "C:\OutputDirectory\QueryResults.txt"
If you’re going to run this on multiple databases or perhaps you want to append the results each day to the same file. Just add -Append.
$DataSet.Tables[0]|Out-File "C:\OutputDirectory\QueryResults.txt" -Append
Okay, great, now you might want to send the file somewhere.
$attachment = new-object Net.Mail.Attachment("C:\OutputDirectory\QueryResults.txt") #Your output location, now that you're using it more than one place, you might want to set it to a variable at top of the script like $outputPath = "C:\OutputDirectory\QueryResults.txt" $smtpServer = "smtp-relay.gmail.com" #Enter your relay, this is common for G-Suite uers. $mailMessage = new-object Net.Mail.MailMessage $smtpObj = new-object Net.Mail.SmtpClient($smtpServer) $mailMessage.From = "do-not-reply@sample.com" $mailMessage.ReplyTo = "do-not-reply@sample.com" $mailMessage.To.Add("serviceaccount@sample.com") $mailMessage.subject = "SQL Query Results" $mailMessage.body = "Attached shows our SQL Query Results." $mailMessage.Attachments.Add($attachment) # Send email $smtpObj.Send($mailMessage)
So know you might want to know how to run on multiple servers. Easy, just put into a for each loop and turn server into a variable.
$ADServers = (Get-ADComputer -LDAPFilter "(name=*)" -SearchBase "OU=SQLSERVERS,DC=sample,DC=com").name #Enter your Organizational Unit Here foreach($server in $ADServers){ $SQLServer = "$server" $SQLDBName = "SampleDatabase" $SqlQuery = "SELECT * FROM Sample.Table" $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; 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) $SqlConnection.Close() $DataSet.Tables[0]|Out-File C:\OutputDirectory\QueryResults.txt -Append #Make sure to append or results will be overwritten for each server }
Another thing I found hand is breaking out the result columns and assigning to a variable, for further manipulating, foreach to the rescue.
foreach ($Row in $dataset.Tables[0].Rows) { $SampleColumn = "$($Row.SampleColumn)" }
By Randall | Published | No Comments
Leave a Reply