Invoke-SQLCMD and hashtable

Tagged: ,

This topic contains 4 replies, has 2 voices, and was last updated by Profile photo of Damien Solodow Damien Solodow 2 years, 7 months ago.

  • Author
    Posts
  • #14807
    Profile photo of Damien Solodow
    Damien Solodow
    Participant

    I've working on a script that does a SQL query and parses results.

    As part of the script I'm creating a hashtable for the parameters for Invoke-SQLCmd as it makes it more readable and the like.
    The problem I'm running into is when I use invoke-sqlcmd with the hash table it seems to be mangling the SQL instance name.

    Here's the relevant snippet:

    $ssrpmenroll = @{
    Database = "ssrpm"
    ServerInstance = 'SQLSERVERNAME\INSTANCE$NAME'
    Query = "select samaccountname,[enrollment time],[profile name],blocked 
    from [enrolled users] 
    where samaccountname=`'$samaccountname'"
    }
    Invoke-Sqlcmd $ssrpmenroll
    

    The SQL instance name contains a $, which is I think the source of my issue.
    $ssrpmenroll.serverinstance returns SQLSERVERNAME\INSTANCE$NAME

  • #14811
    Profile photo of Damien Solodow
    Damien Solodow
    Participant

    I'm (possibly incorrectly) using splatting and hash-table interchangeably here. 🙂

    Looking at the code you posted, the only thing I see different is that you put the query all on one line rather than breaking it up. I've not seen that cause an issue before, and the error I'm getting when running the script is a "can't connect to SQL instance" rather than a "your query is broke" error.

  • #14812
    Profile photo of Tore Groneng
    Tore Groneng
    Participant

    hi,

    I was assuming that you had a variable called $samaccountname that would be expanded in your hashtable. What does the hashtable look like after you run it? (just type $ssrpmenroll and press enter)

    Also notice the @ in my invoke-sqlcmd. You use @ and not $ for splatting. Splatting is just a way to specify named parameters with values like a hashtable.

    As long as you use single quotes (') you should be safe against $ in your instance name. Powwershell only expand the $variablename if you include one in double quotes (").

    /Tore

  • #14821
    Profile photo of Damien Solodow
    Damien Solodow
    Participant

    It looks like it was the @ rather than $ that did it. Doh!

  • #14809
    Profile photo of Tore Groneng
    Tore Groneng
    Participant

    hi,

    Looks like you are trying to use splatting. Try this:

    
    $ssrpmenroll = @{
    Database = "ssrpm"
    ServerInstance = 'SQLSERVERNAME\INSTANCE$NAME'
    Query = "select samaccountname,[enrollment time],[profile name],blocked from [enrolled users] where samaccountname=`'$samaccountname'"
    }
    Invoke-Sqlcmd @ssrpmenroll
    

You must be logged in to reply to this topic.