Loop through a query in powershell TFS

Question

I am new to TFS and powershell scripting.As a part of my assignment I got a task in which

I have an array with TFS workitem ID's and I need to loop through and get the details of those id's and display the result.

As a part of it I tried as shown below

$TFSSERVER = "https://server"
$WId = @("1", "2", "3", "4")
$arr = @("")
$i = 0

Function Get-WorkItemData($WId)
{
   foreach($Id in $WId) 
   {
       $i++
       $query = "SELECT [System.Id]  
          FROM WorkItemLinks " +
          "WHERE [Source].[System.id] = $Id" +  
          "AND [System.Links.LinkType] = 'Parent'" 

      if($WId.length -ge $i)
      {
         $arr+= $query
      }
   }

      $wiData = tfpt query /collection:$TFSSERVER /wiql:$arr
      $wiData | out-file "C:\Path"
 }

Here is the error i get when i run the script

TFPT.EXE : Expecting end of string. The error is caused by ½SELECT╗. At line:28 char:22 + $wiData = tfpt <<<< query /collection:$TFSSERVER /wiql:$b + CategoryInfo : NotSpecified: ( Expecting end ...ed by ½SELECT╗.:String) [], RemoteException + FullyQualifiedErrorId : NativeCommandError

Can anyone please help me to how to resolve the error and get list of all details. Please help me.

Your `$query = "SELECT` line does not have an end quote and + and the next line does not have a beginning quote. — TheMadTechnician, Mar 26 '15 at 03:42
Thank you for the response @TheMadTechnician can you please tell me how to fix that — user4705596, Mar 26 '15 at 10:31

score 0 · Answer 1 · answered Mar 26 '15 at 22:52

I do not know if this will resolve your error, but you at the very least have syntax issues on the line that starts with $query= as well as the following line. To fix this I would probably go with long single lined string (which in effect is what you are doing as is) or a HereString (if your executable allows for that).

A single lined string would work for you, and while it is long and a bit more troublesome to read it does avoid issues like what you are running into.

$query = "SELECT [System.Id] FROM WorkItemLinks WHERE [Source].[System.id] = $id AND [System.Links.LinkType] = 'Parent'"

A HereString is formatted similarly to what you have above, but may not be compatible with your application. It is formatted where the first line is @" with nothing after it, then however many lines of text that you want, then a line starting with "@. So in your case it would look like:

$query = @"
SELECT [System.Id]  
FROM WorkItemLinks
WHERE [Source].[System.id] = $Id
AND [System.Links.LinkType] = 'Parent'
"@

This may not work for your needs, as I don't know if your TFPT.EXE application will accept a multi-line string as input for the query (and have serious doubts about it allowing it).

If you really, really want to continue formatting it like you have you can continue to do so, you just need to fix the first and second lines of that section at the least:

$query = "SELECT [System.Id] " +
    "FROM WorkItemLinks " +
    "WHERE [Source].[System.id] = $Id " +  
    "AND [System.Links.LinkType] = 'Parent';"

Loop through a query in powershell TFS

1 Answers1