none
Query does not work on sharepoint 2007.

    Question

  • Hello,

    I am facing problems retrieving correct number of records when I am performing a people search on SP 2007.

    Following is the query that I had originally written:

    Select Title,Path,PreferredName,WorkPhone,MobilePhone,WorkEmail,JobTitle,OfficeNumber,PictureURL,Responsibilities,Skills,PastProjects,SipAddress,EmployeeLocation,Manager,Department,Assistant,EmployeeType,AccountName,UserProfile_GUID from Scope() WHERE ("Scope"='People') AND ( FirstName LIKE '%Keyword%' OR LastName LIKE '%Keyword%' OR OfficeNumber LIKE '%Keyword%' ) ORDER BY Title ASC

    But, there is a problem here. Those records where the condition "FirstName LIKE '%Keyword%'" is satisfied, does not display if there is no value in OfficeNumber. This is giving incorrect number of records.

    To correct this, I have changed the query and am using UNION in my query. See the following query:

    (Select Title,Path,PreferredName,WorkPhone,MobilePhone,WorkEmail,JobTitle,OfficeNumber,PictureURL,Responsibilities,Skills,PastProjects,SipAddress,EmployeeLocation,Manager,Department,Assistant,EmployeeType,AccountName,UserProfile_GUID from Scope() WHERE ("Scope"='People') and (EmployeeType = 'Contingent Worker' or EmployeeType = 'Employee') AND (FirstName LIKE '%Keyword%'))
    UNION
    (Select Title,Path,PreferredName,WorkPhone,MobilePhone,WorkEmail,JobTitle,OfficeNumber,PictureURL,Responsibilities,Skills,PastProjects,SipAddress,EmployeeLocation,Manager,Department,Assistant,EmployeeType,AccountName,UserProfile_GUID from Scope() WHERE ("Scope"='People') and (EmployeeType = 'Contingent Worker' or EmployeeType = 'Employee') AND (LastName LIKE '%Keyword%'))
    UNION
    (Select Title,Path,PreferredName,WorkPhone,MobilePhone,WorkEmail,JobTitle,OfficeNumber,PictureURL,Responsibilities,Skills,PastProjects,SipAddress,EmployeeLocation,Manager,Department,Assistant,EmployeeType,AccountName,UserProfile_GUID from Scope() WHERE ("Scope"='People') and (EmployeeType = 'Contingent Worker' or EmployeeType = 'Employee') AND (OfficeNumber LIKE '%Keyword%'))

     When I use the above query in my code, I get the following the following error:

    Error in sqlQueryExecute
    Your query is malformed. Please rephrase your query.
    One cause could be the security of one search property. Please modify the security of them. Do a full crawl after that

    Please can you help me resolve this.

    Regards,

    Reshma

     

    • Moved by Bob BeaucheminMVP Friday, November 19, 2010 7:54 PM Moved to a more relevent forum (From:.NET Framework inside SQL Server)
    • Edited by Mike Walsh FIN Tuesday, November 23, 2010 6:26 AM No point in writing "Gives Error" in Title
    • Moved by Mike Walsh FIN Tuesday, November 23, 2010 6:26 AM Search question (From:SharePoint - Development and Programming (pre-SharePoint 2010))
    Friday, November 19, 2010 1:39 PM

Answers

  • I have never seen UNION used in a SharePoint FullTextSQLQuery. To my knowledge it does not exist and this is why you query il malformed.

    As R.Mani said use a tool to test search queries to simplify the testing purpose (either the tool he suggested or SharePoint Search bench on codeplex).

    I have also seen problems when columns are NULL all your conditions in the OR will become false because the LIKE will fail for the null column. You could try using CONTAINS or "=" instead of LIKE. At least for OfficeNumber if it's really a number I don't see why you would like to use LIKE.

    When I saw the NULL column problems, if I remember correctly I managed to solve them by addind a specific test for NULL (I can't remember exactly). Try this in you initial search query (it could work):

    OR (OfficeNumber LIKE '%Keyword%' OR OfficeNumber IS NULL)


    Florin DUCA - MCSE 2003 +Sec,MCTS conf/dev WSS3/MOSS, MCITP/MCPD SP 2010, MCPD ASP.Net 3.5, MCTS ISA 2006 - Logica Business Consulting, France
    Tuesday, November 23, 2010 6:34 AM

All replies

  • Try using this tool for generating and testing Search Query

    MOSS Search Query Tool
    Tool to run MOSS search queries (Keyword or SQL)
    Download Location: http://www.wssdemo.com/Lists/Resources/DispForm.aspx?ID=893

     


    Mani
    Tuesday, November 23, 2010 5:47 AM
  • I have never seen UNION used in a SharePoint FullTextSQLQuery. To my knowledge it does not exist and this is why you query il malformed.

    As R.Mani said use a tool to test search queries to simplify the testing purpose (either the tool he suggested or SharePoint Search bench on codeplex).

    I have also seen problems when columns are NULL all your conditions in the OR will become false because the LIKE will fail for the null column. You could try using CONTAINS or "=" instead of LIKE. At least for OfficeNumber if it's really a number I don't see why you would like to use LIKE.

    When I saw the NULL column problems, if I remember correctly I managed to solve them by addind a specific test for NULL (I can't remember exactly). Try this in you initial search query (it could work):

    OR (OfficeNumber LIKE '%Keyword%' OR OfficeNumber IS NULL)


    Florin DUCA - MCSE 2003 +Sec,MCTS conf/dev WSS3/MOSS, MCITP/MCPD SP 2010, MCPD ASP.Net 3.5, MCTS ISA 2006 - Logica Business Consulting, France
    Tuesday, November 23, 2010 6:34 AM
  • Thank you Mani and Florin.

    Florin, I tried to use OR (OfficeNumber LIKE '%Keyword%' OR OfficeNumber IS NULL)... and I tried the "Contains" and "=" instead of "LIKE", However, it still did not help. When I used "CONTAINS" it again gave me the same error "Your query is malformed. Please rephrase your query.". When  I tried using OR (OfficeNumber LIKE '%Keyword%' OR OfficeNumber IS NULL), it did not give me any error, but it still skipped the records with OfficeNumber as NULL. Also, OfficeNumber is a string. It is the Office location name something like 'A-12 -H01'.

    Do let me know if you can think of any ideas.

    Also, I tried to install the tool that Mani has mentioned. However, it gives the queries in an XML format. I need the queries in SQL format. Is there any settings we need to change in the tool so that it will give me the query in SQL format?

    Thank you once again for all your help!

     

    Regards,

    Reshma

    Thursday, November 25, 2010 7:58 AM
  • Here is a tool where you can test queries in SQL format http://spsearchbench.codeplex.com/.
    Florin DUCA - MCSE 2003 +Sec,MCTS conf/dev WSS3/MOSS, MCITP/MCPD SP 2010, MCPD ASP.Net 3.5, MCTS ISA 2006 - Logica Business Consulting, France
    Thursday, November 25, 2010 9:51 AM
  • Thank you Florin. I tried the query again using CONTAINS and my problem has been fixed now! I had used incorrect syntax for CONTAINS. I fixed the syntax and that resolved my problem.

    Thanks for all your help!

     

    Regards,

    Reshma

     

    Thursday, November 25, 2010 10:57 AM
  • You can also use LIKE operator in the query, but make sure also add the command that column name is not null. The given example query works well.

    for example:

    SELECT AccountName, FirstName, LastName, PictureURL, Title, JobTitle, WorkPhone, WorkEmail FROM scope() WHERE  ( (""SCOPE"" = 'People') and ( (FirstName IS NOT NULL AND FirstName LIKE '%" + keyword  + "%') OR (LastName IS NOT NULL AND LastName LIKE '%" + keyword  + "%') OR (WorkEmail IS NOT NULL AND WorkEmail LIKE'%" + keyword + "%') )) ORDER BY FirstName ASC

     

    Best Regards,

    Usman Zafar Malik

    Thursday, December 09, 2010 6:12 AM
  • Thank you Usman! I will try this and let you know.
    Regards, Reshma Karunakaran
    Thursday, December 09, 2010 7:10 AM