Invoke-sqlcmd -qeury parameter does not accept variable - says it's empty

PowerShell Help
1

Hello,

 

I have a short script here:

 

$servers =“nl001s”,“nl0016”
$usersip = “‘m.bdo@test.com’”
$query = “`"SELECT (SELECT [AdObjectId] FROM [rtc].[dbo].[ResourceDirectory] WHERE [Resource].[ResourceId]=[ResourceDirectory].[ResourceId]) As AdObjectId,[UserAtHost] FROM [rtc].[dbo].[Resource] where UserAtHost=$usersip;`”"
$query = $query.ToString()
Invoke-Command -ComputerName $servers { Invoke-Sqlcmd -Query $query -ServerInstance “$env:computername\RTCLOCAL” }

 

 

And the output is:

 

Cannot validate argument on parameter ‘Query’. The argument is null or empty. P
rovide an argument that is not null or empty, and then try the command again.

 

I checked the output of the $query and it looks okay. It is also a string type which is required by that parameter.

 

Thanks for your time :slight_smile:

2

The problem here, as I can see it, is that you’re setting $query in your local session and then not passing it through to the scriptblock you’re running on the remote machines. The remote machine can’t see the current scope, so $query is $null/unset.

To get around this, you should pass $query as an argument (with -ArgumentList) to the Invoke-Command:

$servers ="nl001s","nl0016"
$usersip = "'m.bdo@test.com'"

$query = "SELECT (SELECT [AdObjectId] FROM [rtc].[dbo].[ResourceDirectory] WHERE [Resource].[ResourceId]=[ResourceDirectory].[ResourceId]) As AdObjectId,[UserAtHost] FROM [rtc].[dbo].[Resource] where UserAtHost=$usersip;"

Invoke-Command -ComputerName $servers  -ArgumentList $Query -ScriptBlock {

param($Query)

Invoke-Sqlcmd -Query $query -ServerInstance "$env:computername\RTCLOCAL"

}
3

You have to pass arguments to the Invoke-Command PowerShell cmdlet when running it remotely.

For example:

[pre]Invoke-Command -ComputerName $servers { Invoke-Sqlcmd -Query $args[0] -ServerInstance “$env:computername\RTCLOCAL” } -ArgumentList $query[/pre]

4

Now it gives me another error

The identifier that starts with ‘SELECT (SELECT [AdObjectId] FROM [rtc].[dbo].[
ResourceDirectory] WHERE [Resource].[ResourceId]=[ResourceDirectory].[ResourceI
d])’ is too long. Maximum length is 128.

how to expand maximum lenght of this?

5

Can you explain exactly what you are trying to do? The logic isn’t quite making sense to me. You’re running the exact same SELECT on two servers, there is nothing being dynamically like resolving information on the local machine to generate a query. Is $usersip supposed to be resolved on the remote system? Do the servers have the SQL Management Tools installed to have the SQLPS module available (Invoke-SqlCmd)? If you explain what you are trying to do, it’s possible you don’t even need to using the Invoke-Command at all.

$servers ="nl001s","nl0016"
$usersip = 'm.bdo@test.com' #Same static query

$query = @"
SELECT (SELECT [AdObjectId]
        FROM [rtc].[dbo].[ResourceDirectory] 
        WHERE [Resource].[ResourceId]=[ResourceDirectory].[ResourceId]) As AdObjectId
       ,[UserAtHost] 
FROM [rtc].[dbo].[Resource] 
WHERE UserAtHost='$usersip';
"@

Invoke-Command -ComputerName $servers -ArgumentList $Query -ScriptBlock {
    param($Query)
    #All remote servers need to have SQL Management Tools installed for this to be available
    Invoke-Sqlcmd -Query $query -ServerInstance "$env:computername\RTCLOCAL"
}
6

Thanks, it worked!