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

Viewing 5 reply threads
  • Author
    Posts
    • #118815
      Participant
      Topics: 1
      Replies: 2
      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
      Participant
      Topics: 0
      Replies: 42
      Points: 403
      Team MemberHelping Hand
      Rank: Contributor

      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
      Topics: 1
      Replies: 23
      Points: 164
      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
      Topics: 1
      Replies: 2
      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
      Topics: 11
      Replies: 1439
      Points: 1,756
      Helping Hand
      Rank: Community Hero

      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
      Topics: 1
      Replies: 2
      Points: -4
      Rank: Member

      Thanks, it worked!

Viewing 5 reply threads
  • The topic ‘Invoke-sqlcmd -qeury parameter does not accept variable – says it's empty’ is closed to new replies.