SQL Query

Welcome Forums General PowerShell Q&A SQL Query

This topic contains 5 replies, has 4 voices, and was last updated by

 
Participant
3 months ago.

  • Author
    Posts
  • #108044

    Participant
    Points: 4
    Rank: Member

    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
  • #108046

    Keymaster
    Points: 1,619
    Helping HandTeam Member
    Rank: Community Hero

    Please observe the instructions for formatting code;

    
    

    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.

    • #108049

      Participant
      Points: 4
      Rank: Member

      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!

  • #108076

    Participant
    Points: 159
    Helping Hand
    Rank: Participant

    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
    
  • #108097

    Participant
    Points: 35
    Rank: Member

    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

  • #108178

    Participant
    Points: 4
    Rank: Member

    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.

The topic ‘SQL Query’ is closed to new replies.