Powershell invoke-sqlcmd : Value cannot be null. Parameter name: ServerInstance

Question

I'm having issues making calls to the local database using the method outlined below.

Error Message

invoke-sqlcmd : Value cannot be null. Parameter name: ServerInstance At C:\filelocation\HealthCheckCombined.ps1:86 char:3 1. invoke-sqlcmd -query $agentquery -serverinstance $servername ... 2. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidArgument: (:) [Invoke-Sqlcmd], ArgumentNullException + FullyQualifiedErrorId : CannotGetServerInstance,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

---

Environment(s)

• Server 2016 Candidate 3
• Server 2012
• SQL Server 2014
• SQL Server 2012
• PowerShell 3.0, 4.0 & 5.0

---

Goal

I'm trying to run a query via PowerShell against whatever SQL instance is listed in the servers.txt (config file).

---

Two components-

1. External Configuration File (servers.txt)
2. PowerShell script containing functions, loop to create an array from servers.txt and execute the function.

So the contents of servers.txt looks like=

server=test2k16\powershell
server=test2k16\healthcheck

Here's the section where I import the text file and create the function=

#===============================================================================
#Configurable variables
#===============================================================================
$configfile = 'C:\filelocation\servers.txt'
Import-Module "sqlps"
#===============================================================================

#===============================================================================
#SQL Agent Jobs
#===============================================================================
function SQLAgent{
$agentquery= @"
    declare @count int
    select @count = count(1) from msdb.dbo.sysjobs as sj
    join msdb.dbo.sysjobhistory as sjh on sj.job_id = sjh.job_id
    where sj.enabled != 0
    and sjh.sql_message_id > 0
    and sjh.run_date > CONVERT(char(8), (select dateadd (day,(-30), getdate())), 112)
    and sjh.Step_id <= 1

    if (@count >= 1)
        begin
            select distinct sj.name as SQLJobName
            from msdb.dbo.sysjobs as sj
            join msdb.dbo.sysjobhistory as sjh on sj.job_id = sjh.job_id
            where sj.enabled != 0
            and sjh.sql_message_id > 0
            and sjh.run_date > CONVERT(char(8), (select dateadd (day,(-30), getdate())), 112)
            and sjh.Step_id <= 1
        order by name
    end

        else
        begin
            Select 'No Job Failed in Last Month' as SQLJobName
        end
"@

        invoke-sqlcmd -query $agentquery -serverinstance $servername -username "user" -password "password" | Format-Table -AutoSize -Wrap
}
#===============================================================================

Now I make the magic happen by formatting the imported variables and looping through them while running the function=

#===============================================================================
#Run Health Check for each server
#===============================================================================
$import = $(foreach ($line in get-content $configfile) {$line.tolower().split(" ")}) | sort | get-unique
ForEach ($_ in $import){
    $servername = $import.trimstart("server=")
}
ForEach ($_ in $servername){
    SQLAgent
}
#===============================================================================

---

Findings thus far

• Extracting the code within in the function and importing the text file works perfectly fine. No error.
• The $servername variable in the loop displays the correct values (test2k16\powershell & test2k16\healthcheck) if I change the script to only display those variables in the loop

I'm obviously missing something... I've been searching the stack and Google for a day now and finding nothing. Hopefully it's something small I overlooked or don't understand about PowerShell yet.

Thanks in advance for any help!

Answer 1

You are referencing $ServerName for the -ServerInstance parameter. It expects a string, and you are presenting it with an array of strings. Also, you are using the ForEach loop incorrectly the last two times. It should be a variable name, and not the automatic variable of $_. Example:

ForEach($Server in $ServerName){
    SQLAgent
}

Then change your -ServerInstance in your function to reference $Server. Better yet, set parameters for your function and feed it the info it needs within your loop.

Function SQLAgent($Server){
    <Code goes here!>
    invoke-sqlcmd -query $agentquery -serverinstance $server -username "user" -password "password" | Format-Table -AutoSize -Wrap
}
$ServerList = Get-Content $configfile | ForEach{$_.Split('=')[1]}
ForEach($Item in $ServerList){
    SQLAgent -Server $Item
}