SQL Query

PowerShell Help
1

Hello! I am reaching out as I am at a loss. I am trying to create a Powershell script that gets a upc code from the user. The upc code is then used in a SQL query to grab data. The script below does not produce any errors yet does not produce any results. All data in the view is blank after I run the code. I’m pretty sure it has something to do with the passing of the variable yet I am lost.

Write-Host "Start of script"

$getUPC = Read-Host -Prompt 'Please enter the full UPC number including zeroes.'

$upcNum = $getUPC

#Check variable upcNum

Write-Output $upcNum

function processView

{

Invoke-Sqlcmd -Query {ALTER VIEW kitFinder AS

(select t1.PLU_NUM, t1.PLU_DESC, t2.MasterUPC as "Kit MasterUPC", t2.ItemDescription as "UPC Description in Kit" from PLU t1 inner join kit t2 on t1.PLU_NUM = t2.ItemUPC Where PLU_NUM = '$upcNum')} -ServerInstance xxx.xxx.xxx.xxx -Username "admin" -Password "admin" -Database corp_DB

}

processView
2

Please observe the instructions for formatting code; [PRE] isn’t it. Instructions appear immediately above the posting textbox.

I’m not personally an enormous fan of Invoke-SqlCommand for running queries; you might instead look at https://leanpub.com/ditchexcelmakinghistoricalandtrendreportsinpowershell, which is an ebook I wrote (its free) that covers data manipulation using the underlying .NET Framework classes. The book (and its code module) have plenty of examples you can likely adapt.

I find a key, for me, is to build my SQL in a variable. That way, I can both execute it but also output just the final string (using Write-Verbose, for example), allowing me to see exactly the query being sent to SQL, after all my variables are inserted. It makes debugging complex queries a ton easier, as I can quickly copy and paste the actual query into SQL Management Studio and execute it there to test it.

3

I must have updated my original post while you were replying. It’s updated.

 

I’ll check out the ebook as well as rewrite to view my query before it’s executed. Thanks for the direction!

4

The only disadvantage you have using Invoke-SQLCmd is that you need to ensure that the SQLPS module is available when\where you execute the script. For simple SQL commands, it works well. If you require that it runs on with no software\module pre-req, then you can investigate using .NET or other database options.

With that said, this is how I format and use my SQL commands that I use in prod in multiple companies:

function Invoke-KitFinderUpdate {
    param (
        [string]$UPC
    )
    begin {
        Import-Module -Name SQLPS
    }
    process {
#You cannot indent here-strings :(
$sqlCmd = @"
    ALTER VIEW 
        kitFinder 
    AS
    SELECT t1.PLU_NUM
          ,t1.PLU_DESC
	      ,t2.MasterUPC AS 'Kit MasterUPC'
	      ,t2.ItemDescription AS 'UPC Description in Kit'
    FROM PLU t1 inner join kit t2 on t1.PLU_NUM = t2.ItemUPC 
    Where PLU_NUM = '$UPC';
"@

        $sqlParams =@{
            Query = $sqlCmd
            ServerInstance = 'xxx.xxx.xxx.xxx'
            Username = 'admin'
            Password = 'admin'
            Database = 'corp_DB'
        }

        Invoke-Sqlcmd @sqlParams
    }
    end {}
}

$getUPC = Read-Host -Prompt 'Please enter the full UPC number including zeroes.'

Invoke-KitFinderUpdate -UPC $getUPC
5

I’ve a question, if you’re querying data, why are you creating a View? Why not just query the data ?

function Invoke-KitFinderUpdate 
{
    [CmdletBinding()]
    param (
    [string]$SqlServername ,
    [string]$Database ,
    [string]$UPC
    )

    begin {
    [void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
    }

    process {

    $Query = "select t1.PLU_NUM, t1.PLU_DESC, t2.MasterUPC as 'Kit MasterUPC', t2.ItemDescription as 'UPC Description in Kit'
    from PLU t1 inner join kit t2 on t1.PLU_NUM = t2.ItemUPC
    Where PLU_NUM = '$UPC'"

    $server = New-Object Microsoft.SqlServer.Management.Smo.Server $SqlServername
    $db = New-Object Microsoft.SqlServer.Management.Smo.Database
    $db = $server.Databases.Item($Database)
    $Data = $db.ExecuteWithResults($Query)
    $Results = $Data.Tables[0]
    }

    end {
    Return $Results
    }
}

Invoke-KitFinderUpdate -SqlServername $SqlServername -Database corp_DB -UPC $getUPC

If you want to have the option of supplying a Sql Login, as opposed to domain account, then you could do something like this

function Invoke-KitFinderUpdate 
{
    [CmdletBinding()]
    param (
        [parameter(Mandatory=$true,ParameterSetName = "Windows")]
        [parameter(ParameterSetName = "SqlLogin")]
        [string]$SqlServername ,
        [parameter(Mandatory=$true,ParameterSetName = "Windows")]
        [parameter(ParameterSetName = "SqlLogin")]
        [string]$Database ,
        [parameter(Mandatory=$true,ParameterSetName = "Windows")]
        [parameter(ParameterSetName = "SqlLogin")]
        [string]$UPC ,
        [parameter(Mandatory=$true,ParameterSetName = "SqlLogin")]
        [string]$Username ,
        [parameter(Mandatory=$true,ParameterSetName = "SqlLogin")]
        [string]$Password
    )
    begin {
            
        $PsCmdlet.ParameterSetName
        [void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") 

        $Query = "select t1.PLU_NUM, t1.PLU_DESC, t2.MasterUPC as 'Kit MasterUPC', t2.ItemDescription as 'UPC Description in Kit'
        from PLU t1 inner join kit t2 on t1.PLU_NUM = t2.ItemUPC
        Where PLU_NUM = '$UPC"
    }
    process {

        $s = New-Object Microsoft.SqlServer.Management.Smo.Server $SqlServername 
        if($PsCmdlet.ParameterSetName -eq "sqlLogin")
        {
            $s.ConnectionContext.LoginSecure = $false
            $s.ConnectionContext.Login=$Username
            $s.ConnectionContext.Password=$Password
        }
        $db = New-Object Microsoft.SqlServer.Management.Smo.Database
        $db = $server.Databases.Item($Database)
        $Data = $db.ExecuteWithResults($Query)
        $Results = $Data.Tables[0]
      
    }
    end {
        Return $Results
    }
}

Not very elegant but does the job

6

Thank you all for the help! It looks like it’s working now. I created the view as I have to get data from three tables but do a second pass on one of them.