invoke-sqlcmd via function throwing positional parameter error

Question

I'm trying to use a function to run a query but it does not seem to accept my server instance and gives an error: "Run-SqlCommand : A positional parameter cannot be found that accepts argument 'mydbservername'."

When I execute the same invoke-sqlcmd outside of the function it works fine.

Function:

    function Run-SqlCommand
{
    [CmdletBinding()]
    Param
    (
        [parameter(Position=0, Mandatory=$True, ParameterSetName="Query")]
        [string]$Query,
        [parameter(Position=1, Mandatory=$True, ParameterSetName="DatabaseServerName")]
        [string]$DatabaseServerName,
        [parameter(Position=2, Mandatory=$True, ParameterSetName="Database")]
        [string]$Database,
        [parameter(Position=3, Mandatory=$True, ParameterSetName="Username")]
        [string]$Username,
        [parameter(Position=4, Mandatory=$True, ParameterSetName="Password")]
        [string]$Password
    )

    try {
        $result = Invoke-SqlCmd -query $Query -serverinstance $DatabaseServerName -Database $Database -Username $Username -Password $Password -ErrorAction Stop
        }
    catch {
        Write-Warning $_.Exception.Message
        }

}

In another script I define the query, server, database and user/pass and call the function passing those variables:

$SuperUserQuery =@"
select uid,username,first_name,last_name,ad_username,active
from users
where (superuser = 'Y' AND active = '1')
Order by username Asc
"@

      $DatabaseServerName = "mydbservername"
      $Database = "dbname"
      $Username = "dbuser"
      $Password = "dbpass"


Run-SqlCommand $SuperUserQuery $DatabaseServerName $Database $Username $Password

When I do that I get the error I mentioned above:

"Run-SqlCommand : A positional parameter cannot be found that accepts argument 'mydbservername'."

If I just run the invoke-sqlcmd directly in the script I try to call the function in - it works just fine.

$result = Invoke-SqlCmd -query $SuperUserQuery -serverinstance $DatabaseServerName -Database $Database -Username $Username -Password $Password -ErrorAction Stop

Why does this work when called directly but not work when called via my function?

Well, I don't really understand why so would still like someone to help me understand....but I fixed it by changing the parameters...

        [parameter(Mandatory=$True)]
        [string]$Query,
        [parameter(Mandatory=$True)]
        [string]$DatabaseServerName,
        [parameter(Mandatory=$True)]
        [string]$Database,
        [parameter(Mandatory=$True)]
        [string]$Username,
        [parameter(Mandatory=$True)]
        [string]$Password

It worked when i changed to that. I guess maybe I don't understand "position" or "parametersetname".

Why are you reinventing the wheel here? What's wrong with just using `Invoke-SqlCmd`? — Thom A, May 05 '19 at 08:49

score 0 · Answer 1 · answered May 05 '19 at 13:02

I think what you are trying to do is what is called Splatting. With splatting, you can build easy to read/maintain code, especially when it involves cmdlets that take a lot of parameters.

For this, you create a Hashtable object and put all parameters you want to use for the Invoke-Sqlcmd cmdlet in there like so:

$sqlQuery = @"
SELECT uid,
       username,
       first_name,
       last_name,
       ad_username,
       active
FROM   users
WHERE  ( superuser = 'Y'
         AND active = '1' )
ORDER  BY username ASC 
"@

$sqlParams = @{
    'Query'          = $sqlQuery
    'ServerInstance' = 'mydbservername'
    'Database'       = 'dbname'
    'Username'       = 'dbuser'
    'Password'       = 'dbpass'
    # you can even add common parameters in there like
    'ErrorAction'    = 'Stop'
}

Then you use it like this:

try {
    $result = Invoke-Sqlcmd @sqlParams
}
catch {
    Write-Warning $_.Exception.Message
}

Sort of - but I wanted to use the function multiple times to run different queries (and use it for other scripts/projects in the future) so I'm not sure splatting is exactly what I'm looking for. I've never written a powershell function before so I think I got carried away on the params. For now I will go with what I have until I can figure out how to properly use Position and ParameterSetName. — Josh Nelson, May 06 '19 at 04:01

invoke-sqlcmd via function throwing positional parameter error

1 Answers1