TFS Top Tip #1 - WIQL Seperators

| 4 Comments

I thought I’d try and post some quick Top Tips for Team Foundation Server – in no particular order apart from as I think of them.  Today, this came up in the forums so I thought I’d elaborate.

WIQL (pronounced Wickle), stands for Work Item Query Language and is what is used when talking to the work item store in Team Foundation Server.  It has a SQL like construct and is used to pass queries to the server.  Visual Studio 2005 comes with a Query Editor that generate WIQL.  While the query editor is straightforward, it is pretty powerful and allows you to do most things.

However, the query editor is region sensitive which sometimes causes confusion.  Take the following example where I am using an “IN” statement to list a set of values for the work item status:-

An example of using the IN statement in Visual Studio 2005 Work Item Query Editor

Note that the values are separated by commas.  Those of you from a SQL background find this very sensible, but what the query editor is actually doing is taking the list of values and converting them into the following WIQL statement:-

SELECT   [System.Id], [System.WorkItemType], [Microsoft.VSTS.Common.Rank], [System.State], [System.AssignedTo], [System.Title] 
FROM     WorkItems 
WHERE    [System.TeamProject] = @project  
AND      [System.State] IN ('Active''Pending''Proposed''Requested'
ORDER BY [Microsoft.VSTS.Common.Rank], [System.WorkItemType], [System.Id]

(A top sub-tip is that it is possible to save WIQL files from Visual Studio by editing the query then selecting File, Save Query As.. and then select file.  To run a saved query from the file system double click the *.wiq file from explorer)

The comma separator used by the query editor is actually being picked up from the “List separator” of your regional settings (shown below) (Start, Control Panel, Regional Settings, Customize…)

Region_settings

If you are in one of the many regions of the world that use a different list separator then you have to use that in the Visual Studio 2005 Query Editor.  For example, if I change my list separator to be a semi-colon and then re-edit the query in the Visual Studio 2005 Query Editor I get the following:-

Same WIQL, different list seperator

This behaviour has some interesting side effects.  Remember when I said that the Visual Studio 2005 Query Editor “allows you to do most things”.  Well, one small problem is forcing the editor to take a character to say that you want the following to be treated as a string.  For example, if you have a comma in the text value you are trying to use in an “IN” statement then you are hosed because the query editor assumes that this is a new value in your list.  For example, if you try the following:

Query names

This actually gets translated by the query editor as the following:

SELECT   [System.Id], [System.WorkItemType], [Microsoft.VSTS.Common.Rank], [System.State], [System.AssignedTo], [System.Title] 
FROM     WorkItems 
WHERE    [System.TeamProject] = @project  
AND      [System.AssignedTo] IN ('''Woodward''Martin''''''Sell''Clark'''
ORDER BY [Microsoft.VSTS.Common.Rank], [System.WorkItemType], [System.Id]

As you can tell, it parses on the commas first, which is not want you wanted at all.  If you manually type in the WIQL correctly as IN (‘Woodward, Martin’, ‘Sell, Clark’) then the query editor will display this as Woodward, Martin, Sell, Clark – which in turn gets treated as IN (‘Woodward’,’Martin’,’Sell’,’Clark’) when the WIQL is generated by the editor. 

Hey ho – Clark Sell has a post about changing the regional settings to enable to to query assigned to names but be warned it may have nasty side effects in other programs on your machine.

Hmm.  When I thought about posting a TFS Tip a day for the next couple of weeks I didn’t intend them to be this long.  Expect the next one to be more concise…

Now playing: Carl Franklin - Avalon, AJAX, Vista, and more with Tim Huckaby

4 Comments

Hi,

I am creating a little application in Visual Studio 2005 .NET using C#, that based on server name, project name and changeset brings back all associated work items.

Using WIQL it was very easy to do this based on server and project name, but I have not found a way to search based on changeset ID.

Any ideas?

Many Thanks

Steve

Sorry for not really related question but I'm really stuck.
We had a developer who leaved the company and left some files locked in his workspace.
How can I make TFS 2008 forget about that lock?

Thanks a lot
Basil

Hello,

is it possible to enter a subselect statement in the Value field together with an IN operator.

I wonder why there is nowhere a statement about a subselect. Isn't it verry suggesting to use this instate of hardcoded values?

Do you know anywhy to fullfill this or is it impossible?

Hi ,
Is it possible to extract WorkItemTypes and Fields Selected from query , which is stored in QueryDefinition object.

As of now , I have written my own parser to extract above info.

Is there any other existing thing which I can use.

Thanx in advance.

Regards
Khalil J

Archives

Creative Commons License
This blog is licensed under a Creative Commons License.