Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

The following FullTextSQLQuery returns results:

SELECT PreferredName, Department, WorkEmail, HireDate 
FROM SCOPE() 
WHERE \"scope\" = 'People' 
ORDER BY HireDate DESC

The following FullTextSQLQuery does not return any results:

SELECT PreferredName, Department, WorkEmail, HireDate 
FROM SCOPE() 
WHERE \"scope\" = 'People' 
AND Department != '' 
ORDER BY HireDate DESC

The only difference is the addition of "Department != ''" in the second query. I know there are records in which Department is blank. Why am I not getting any results for the second query?

UPDATE

When I query for text within the Department column, I don't get any results, even though there is text in the column:

SELECT PreferredName, Department, WorkEmail, HireDate 
FROM SCOPE() 
WHERE \"scope\" = 'People' 
AND Department LIKE 'A%'
ORDER BY HireDate DESC
//no results!

If I do the same query on a different column, I get results:

SELECT PreferredName, Department, WorkEmail, HireDate 
FROM SCOPE() 
WHERE \"scope\" = 'People' 
AND PreferredName LIKE 'A%'
ORDER BY HireDate DESC
//results!

Also if I do a People Search through the UI for Department:"name of department" (using an actual department name), I get results, so I know for sure that Department is being crawled. Does anyone have some insight on this?

share|improve this question
add comment

1 Answer

Try using the following query:

SELECT PreferredName, Department, WorkEmail, HireDate
FROM SCOPE() 
WHERE \"scope\" = 'People' 
AND Department LIKE '_%'
ORDER BY HireDate DESC

Edit:

This actually looks like a problem with the Department field itself.

For one, "FullTextSQLQuery does not track whether a crawled property is null. Null values are not stored. Therefore if you OR two managed properties together and there is not a value for both, then the record will not be returned." FullTextSQLQuery tips

Also, I found a scraped forum post (that I can't link to, since it was scraped) that said that including the Department field (as well as OfficeNumber or JobTitle) always returned 0 results. This may be an issue with the property not being crawled.

You could try using MOSS Search Coder from CodePlex.

Good luck.

share|improve this answer
    
No results for that, either. –  LCountee Nov 19 '12 at 17:56
    
* is the wildcard character in SharePoint Search SQL (which is used with FullTextSqlQuery). I tried AND Department LIKE '*', which retured no results. –  LCountee Nov 19 '12 at 20:00
    
My mistake, I was thinking of the CONTAINS syntax, which uses *. But, using LIKE with _% or % yields zero results. I've also tried CONTAINS(Department, '*') which gives me an error: "Your query included only common words and / or characters, which were removed. No results are available. Try to add query terms." –  LCountee Nov 19 '12 at 20:20
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.