Author Posts

April 24, 2014 at 1:41 pm

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

April 24, 2014 at 2:10 pm

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

April 24, 2014 at 4:01 pm

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.

April 24, 2014 at 11:19 pm

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

April 25, 2014 at 5:39 am

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