powershell sql queries

SQLExample

For querying all the data from table MachineName we can use the command like below one.

$Query=“Select * from MachineName”

$Inst=“ServerInstance”

$DbName=“DatabaseName

$UID=“User ID”

$Password=“Password”

Invoke-Sqlcmd2 -Serverinstance $Inst -Database $DBName -query $Query -Username $UID -Password $Password

SQLQuery

For querying all the data from table MachineName we can use the command like below one.

$Query=“Select * from MachineName”

$Inst=“ServerInstance”

$DbName=“DatabaseName

$UID=“User ID”

$Password=“Password”

Invoke-Sqlcmd2 -Serverinstance $Inst -Database $DBName -query $Query -Username $UID -Password $Password

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();
    }
}