Hi,

I'm trying to get all tasks of the current logged-in user from a site collection using SPSiteDataQuery in SP 2013,but its showing no results.Instead , the results are shown for other list templates like 105(Contacts List) , 101 (Document Lib) etc. but whenever I give servertemplate as 107(Tasks list) , nothing is returned. The tasks list is not having any multiple or Lookup columns and the tasks list is having sufficient data/items to display. I'm not getting any error though , but no data/success. I have tested in visual web-part & in console application but same result.

using(SPSite site = new SPSite("<Server URL>"))
                {
                    using (SPWeb web = site.OpenWeb())
                    {
                        SPSiteDataQuery query = new SPSiteDataQuery();
                        query.Webs = "<Webs Scope=\"SiteCollection\" />";
                        query.Lists = "<Lists ServerTemplate=\"107\" />";
                       
                        //query.ViewFields = "<FieldRef Name='AssignedTo' />";
                                                            
                        query.Query = "<Where><Gt><FieldRef Name='ID' /><Value Type='Counter'>0</Value></Gt></Where>";
                        DataTable dtTasks = new DataTable();
                        dtTasks = web.GetSiteData(query);
                        LogMessage("Row Count := " + dtTasks.Rows.Count,"");
                        grdTasks.DataSource = dtTasks;
                        grdTasks.DataBind();
                    }
                }

I have also followed the msdn blog : http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spsitedataquery.query.aspx , but no success :(. Also I have tried different combinations in query.ViewFields , query.Lists , query.Query like ( Hidden="true" , type="User","Multi","UserMulti","MultiUser","Text" etc...scope=Sitecollection , recursive) . but nothing is working in my

Hi,                                                             

I have tested your code in a console application in my environment and it worked.

using (SPSite site = new SPSite("http://sp2013sps"))
{
    using (SPWeb web = site.OpenWeb())
    {
        SPSiteDataQuery query = new SPSiteDataQuery();
        query.Webs = "<Webs Scope=\"SiteCollection\" />";
        query.Lists = "<Lists ServerTemplate=\"107\" />";
        query.ViewFields = "<FieldRef Name='AssignedTo' />";
        query.Query = "<Where><Gt><FieldRef Name='ID' /><Value Type='Counter'>0</Value></Gt></Where>";
        DataTable dtTasks = new DataTable();
        dtTasks = web.GetSiteData(query);

        int i = dtTasks.Rows.Count;
        Console.WriteLine("count: " + i);

        foreach (DataRow row in dtTasks.Rows)
        {
            string name = row["AssignedTo"].ToString();
            Console.WriteLine("AssignedTo: " + name);
        }
    }
}

I suggest you to debug your code in Visual Studio to see what happens during the process.

Best regards

Hi Patrick,

I tried the code once again on some other server , but still it's showing no results i.e. row count=0 while debugging. 

Hi Vipul,

I had the same issue. You have to set "AllowMultipleValues" property of the of the assigned to field to false. Just for trial, run your code without the "Assigned To" in viewfields.

Also check the servertemplate number, It should be 171 for task list

 $spList = $spWeb.Lists.item(Tasks)
 $spField = $spList.Fields.GetField("AssignedTo")
 $spField.AllowMultipleValues=$false;
 $spField.Update()

Here is my code

using (SPSite oSite = new SPSite(@"http://<Server:<Port>>"))
            {
                using (SPWeb oWeb = oSite.OpenWeb())
                {
                    SPSiteDataQuery oQuery = new SPSiteDataQuery();
                    oQuery.Lists = "<Lists ServerTemplate=\"171\" />";

                    oQuery.Query = string.Concat("<Where>",
                                  "<Neq>",
                                     "<FieldRef Name='Status' />",
                                     "<Value Type='Choice'>Completed</Value>",
                                  "</Neq>",
                            "</Where>");


                    oQuery.ViewFields = "<FieldRef Name=\"Title\" /><FieldRef Name=\"StartDate\"/><FieldRef Name=\"DueDate\"/><FieldRef Name=\"Body\"/>";

                    oQuery.Webs = "<Webs Scope=\"Recursive\" />";
                    DataTable dtTasks = oWeb.GetSiteData(oQuery);
                    Console.WriteLine("Printing SPSiteDataQuery result...");
                    foreach (DataRow dr in dtTasks.Rows)
                    {
                        Console.WriteLine(dr["Title"]);
                    }
                    Console.WriteLine("Press any key to exit...");
                    Console.ReadLine();
                }
            }

Hi Amey0072,

According to msdn, the Task list template ID is 107 , but I still checked with your suggestions i.e 171 and allowmultiple values to be false , but it didn't worked. Also , run the code without "AssignedTo" in Viewfields  ,but no success. Still I'm getting row count to be zero. I have checked in 3-4 SP 2013 environments , but getting the same result.

Any other suggestion ?

Hi Vipul,

                       did you assign task to administrator user. because i thing currently its using administrator account (app-pool one ) to run vs code. can you share the screen short where you assign talk to which user.and print user name on console.  use this code share results. 

using System;
using System.Data;
using Microsoft.SharePoint;

namespace Test
{
   class ConsoleApp
   {
      static void Main(string[] args)
      {
         using (SPSite site = new SPSite("http://192.168.10.101"))
         {
            using (SPWeb web = site.OpenWeb())
            {
               SPSiteDataQuery query = new SPSiteDataQuery();

               // Query all Web sites in this site collection.
               query.Webs = "<Webs Scope='SiteCollection'>";

               // Ask for lists created from the Tasks template.
               query.Lists = "<Lists ServerTemplate='107'/>";

               // Specify the view fields.
               query.ViewFields = "<FieldRef Name='Title' Type='Text'/>";
               query.ViewFields += "<FieldRef Name='AssignedTo' Type='User' Nullable='TRUE' />";
               query.ViewFields += "<FieldRef Name='PercentComplete' Type='Number' Nullable='TRUE' />";

               // Run the query.
               DataTable results = web.GetSiteData(query);

               // Print the results.
               Console.WriteLine("{0, -30} {1, -30} {2}", "Task", "Assigned to", "% Complete");
               foreach (DataRow row in results.Rows)
               {
                  // Get the task name.
                  string task = row["Title"].ToString();

                  // Parse out the user's login name.
                  string loginName = String.Empty;
                  string[] str = row["AssignedTo"].ToString().Split('#');
                  if (str.Length > 1) loginName = str[1];

                  // Get the percent complete.
                  decimal percent;
                  bool hasValue = decimal.TryParse((string)row["PercentComplete"], out percent);
                  if (!hasValue) percent = 0;

                  Console.WriteLine("{0, -30} {1, -30} {2, 10:P0}", task, loginName, percent);
               }
            }
         }
         Console.ReadLine();
      }
   }
}

Hi Usama,

I am assigning task to a user who's having permissions on the site , not necessarily admin user. Yes , I tried to run the code with "SPSecurity.RunWithElevatedPrivileges" , but it didn't worked..The code which you have mentioned , I tried the same code at my end in VS 2012 , but its not working :(

HI vipul.

                can you check ULS logs. might be you find some thing. share you list template. 

Hi Usama,

I have checked ULS logs and event viewer too , but no as such specific message related to SPSiteDataQuery.

Hi Vipul,

FYI - in SP2013 - TasksWithTimelineAndHierarchy has template ID 171

http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.splisttemplatetype.aspx

Could you please try executing the code I have mentioned on top. Add some pending tasks in the default SP task list and also make sure "AllowMultipleValues" is set to false for assigned to field.

• Edited by Amey0072 Monday, November 25, 2013 8:05 AM
• Marked as answer by Vipul Jain 03 Wednesday, November 27, 2013 7:47 PM

Hi,

According a further step test, I found that the "107" seems only works to the "Tasks" lists which are used in workflows.
The two tasks below come from two different task lists which are used by two workflows, tasks in other task lists didn't show up by executing the code.

You can try to create tasks in the "Workflow Task" list and test the code again.

Best regards

Hi,

Can the "107" or "171" work in your environment? Would you mind sharing your progress with us?

Thanks 

Hi,

In SharePoint 2013 , the "TasksWithTimelineAndHierarchy" list template ID is 171 if you want to access the tasks assigned to a user. With this ID , it worked ..:).. Also , there is no need to make "AllowMultipleValues" of Assigned to field as 'No'..Its working with 'Yes' too.. :).  

Thanks for the useful inputs :)

Hi,

Glad to hear that you solve the issue, thanks for your sharing.

Best regards

This works perfect for me! Go with 171 for non-work flow task lists. Cheers!

• Edited by Rekha Gunathilake 3 hours 19 minutes ago

This works perfect for me! Go with 171 for non-work flow task lists. Cheers!

• Edited by Rekha Gunathilake 3 hours 11 minutes ago