Monday, August 12, 2019

Connect to SQL with Powershell

Connect to SQL Server with Powershell used to feed a powershell dashboard

Script post colors

# Check if SqlServer module is installed and install it
if (!(Get-InstalledModule sqlServer)) {Install-Module -Name SqlServer}

# Param input 1 for facilites and 2 for providers, option 3 to set password
[string] $Server= "SQLServer"
[string] $Database = "myDatabase"
[string] $UserSqlQuery= @"
select * from my table

[string] $UserSqlQuery2= @"
select * from my table2

$outfile = "C:\support\scripts\dashboard\data\my-data1.xml"
if ($dataType -eq 3){ # Save password creds to file, must be set per machine
$c | Export-CliXml -Path 'C:\support\scripts\dashboard\data\cred.xml'

if ($dataType -eq 2)
$outfile = "C:\support\scripts\dashboard\data\my-data2.xml"

$resultsDataTable = New-Object System.Data.DataTable
$credential = Import-CliXml -Path C:\support\scripts\dashboard\data\cred.xml

#Run SQL Command
function run-SqlQuery ($sqlServer, $sqlDatabase, $sqlQuery, $dbCreds ) {
$dbUser = $dbCreds.UserName#.Replace("\","")
$dbPass = $dbCreds.GetNetworkCredential().password
$dbDataSet=invoke-sqlcmd -query $sqlQuery -database $sqlDatabase -serverinstance $sqlServer -username $dbUser -password $dbPass
return $dbDataSet

$resultsDatatable = run-sqlQuery $Server $Database $UserSqlQuery $credential

$resultsDatatable |Export-Clixml -Path $outfile

No comments:

Post a Comment