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
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
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.
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
It looks like it was the @ rather than $ that did it. Doh!