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

Welcome Forums General PowerShell Q&A Invoke-sqlcmd -qeury parameter does not accept variable - says it's empty

This topic contains 5 replies, has 4 voices, and was last updated by

 
Participant
1 month, 3 weeks ago.

  • Author
    Posts
  • #118815

    Participant
    Points: -4
    Rank: Member

    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 🙂

  • #118821

    Moderator
    Points: 103
    Team MemberHelping Hand
    Rank: Participant

    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"
    
    }
    
    
  • #118824

    Participant
    Points: 114
    Helping Hand
    Rank: Participant

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

    For example:

    Invoke-Command -ComputerName $servers { Invoke-Sqlcmd -Query $args[0] -ServerInstance "$env:computername\RTCLOCAL" } -ArgumentList $query
  • #118827

    Participant
    Points: -4
    Rank: Member

    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?

  • #118879

    Participant
    Points: 333
    Helping Hand
    Rank: Contributor

    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"
    }
    
  • #119008

    Participant
    Points: -4
    Rank: Member

    Thanks, it worked!

You must be logged in to reply to this topic.