Invoke-SQLCMD and hashtable

Welcome Forums General PowerShell Q&A Invoke-SQLCMD and hashtable

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

 
Participant
4 years, 7 months ago.

  • Author
    Posts
  • #14807

    Participant
    Points: 0
    Rank: Member

    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

    Participant
    Points: 0
    Rank: Member

    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

    Participant
    Points: 0
    Rank: Member

    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

    Participant
    Points: 0
    Rank: Member

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

  • #14809

    Participant
    Points: 0
    Rank: Member

    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
    

The topic ‘Invoke-SQLCMD and hashtable’ is closed to new replies.