PowerShell powershell sql queries
Introduction
By going through this document You can get to know how to use SQL queries with powershell
Parameters
| Item | Description |
|---|---|
| $ServerInstance | Here we have to mention the instance in which the database is present |
| $Database | Here we have to mention the database in which the table is present |
| $Query | Here we have to the query which you we want to execute in SQ |
| $Username & $Password | UserName and Password which have access in database |
Remarks
You can use the below function if in case you are not able to import SQLPS module
function Import-Xls
{
[CmdletBinding(SupportsShouldProcess=$true)]
Param(
[parameter(
mandatory=$true,
position=1,
ValueFromPipeline=$true,
ValueFromPipelineByPropertyName=$true)]
[String[]]
$Path,
[parameter(mandatory=$false)]
$Worksheet = 1,
[parameter(mandatory=$false)]
[switch]
$Force
)
Begin
{
function GetTempFileName($extension)
{
$temp = [io.path]::GetTempFileName();
$params = @{
Path = $temp;
Destination = $temp + $extension;
Confirm = $false;
Verbose = $VerbosePreference;
}
Move-Item @params;
$temp += $extension;
return $temp;
}
# since an extension like .xls can have multiple formats, this
# will need to be changed
#
$xlFileFormats = @{
# single worksheet formats
'.csv' = 6; # 6, 22, 23, 24
'.dbf' = 11; # 7, 8, 11
'.dif' = 9; #
'.prn' = 36; #
'.slk' = 2; # 2, 10
'.wk1' = 31; # 5, 30, 31
'.wk3' = 32; # 15, 32
'.wk4' = 38; #
'.wks' = 4; #
'.xlw' = 35; #
# multiple worksheet formats
'.xls' = -4143; # -4143, 1, 16, 18, 29, 33, 39, 43
'.xlsb' = 50; #
'.xlsm' = 52; #
'.xlsx' = 51; #
'.xml' = 46; #
'.ods' = 60; #
}
$xl = New-Object -ComObject Excel.Application;
$xl.DisplayAlerts = $false;
$xl.Visible = $false;
}
Process
{
$Path | ForEach-Object {
if ($Force -or $psCmdlet.ShouldProcess($_)) {
$fileExist = Test-Path $_
if (-not $fileExist) {
Write-Error "Error: $_ does not exist" -Category ResourceUnavailable;
} else {
# create temporary .csv file from excel file and import .csv
#
$_ = (Resolve-Path $_).toString();
$wb = $xl.Workbooks.Add($_);
if ($?) {
$csvTemp = GetTempFileName(".csv");
$ws = $wb.Worksheets.Item($Worksheet);
$ws.SaveAs($csvTemp, $xlFileFormats[".csv"]);
$wb.Close($false);
Remove-Variable -Name ('ws', 'wb') -Confirm:$false;
Import-Csv $csvTemp;
Remove-Item $csvTemp -Confirm:$false -Verbose:$VerbosePreference;
}
}
}
}
}
End
{
$xl.Quit();
Remove-Variable -name xl -Confirm:$false;
[gc]::Collect();
}
}
