Welcome to deBUG.to Community where you can ask questions and receive answers from Microsoft MVPs and other experts in our community.
0 like 0 dislike
in Project Server - EPM by 11 11 18

I did SQL server query to retrieve tasks list data but the field AssignedTo not retrieved i don't know why?

My SQL query :

SELECT W.Title [webTitle],
    AD.tp_ID [Task Id],
    tp_columnset.value('(nvarchar1)[1]','nvarchar(200)') as [Task Name],
    tp_columnset.value('(nvarchar5)[1]','nvarchar(50)') as [Task Outcome],
    tp_columnset.value('(nvarchar4)[1]','nvarchar(50)') as [Task Status],
    tp_columnset.value('(ntext1)[1]','nvarchar(200)') as [user comment],
     FROM [WSS_Content].[dbo].[AllUserData] AD 
     INNER JOIN [WSS_Content].[dbo].[AllLists] AL ON AD.[tp_ListId]=AL.tp_ID 
     INNER JOIN [WSS_Content].[dbo].[Webs] W ON W.Id = AL.tp_WebId 
     WHERE AL.tp_Title = 'TasksListName' AND AD.tp_DeleteTransactionId = 0x

The output of the column tp_columnset :

<nvarchar1>task name</nvarchar1>
<nvarchar3>(2) Normal</nvarchar3>
<nvarchar4>Not Started</nvarchar4>

1 Answer

0 like 0 dislike
by 11 11 18
edited by

I am able to populate the Assigned To column from SharePoint List using REST API instead of using SQL Query through the below code

HTML code:

<table id="tasksTable" >
                            <th>Task title</th>
                            <th>Assigned Date</th>

Script code:

      function getTasks(url, success) {
                type: "GET",
                contentType: "application/json; odata=verbose",
                url: url,
                headers: {
                    "accept": "application/json;odata=verbose"
                dataType: "json",
                success: function (response) {
                error: function (err) {
                    console.log(" error!");

$(document).ready(function () {

    var currentUserId;
    getTasks("http://localhost/subsite/_api/Web/CurrentUser?$select=Id", function (data) {
       currentUserId = data.d.Id;

     getTasks("http://localhost/subsite/_api/Web/lists/getbytitle('Workflow Tasks')/items? 
$filter=AssignedTo/ID eq " + currentUserId + " and PercentComplete eq 0 ", function (data) 
              $.each(data.d.results, function (i, v) {
                            if (data.d.results.length) {
                                var r = $('<tr><td>' + v.Title + '</td><td>' + v.Created + 

Hope it helps!

If you don’t ask, the answer is always NO!