locked
Inactivate the Users in bulk RRS feed

  • Question

  • i am trying to inactivate the users who are not assigned to any tasks , last connected date as 2006,2008 and created in 2009. for this action how to get the users from resource table and how to inactive the users in bulk using sql query.

    actually i have 5000 users to inactivate those status. for this i need a sql query. please provide a suggestion ASAP.

     

    Thanks in Advance..!

     

    Sunday, July 17, 2011 11:08 AM

Answers

  • Hello Ggopinath,

    SQL query on Project server published database to update/delete records is NOT recommanded & Supported. Please use PSI (project Server Interface ) for any data add/update instead of SQL update/delete.
    You can use Resource Web service which has a method to deactivate resource. You can use resource class or dataset & loop through the records to (LINQ to get Lastconnected date) based on that get the all resource records & deactivate them,

    Here is the ref:
    http://msdn.microsoft.com/en-us/library/websvcresource.resource_di_pj14mref_methods.aspx
    http://msdn.microsoft.com/en-us/library/gg208103.aspx

    Code:

    using System;
    using System.Collections.Generic;
    using System.Text;
    using System.Net;
    using System.Data;
    using System.Web.Services.Protocols;
    using System.Threading;
    using PSLibrary = Microsoft.Office.Project.Server.Library;

    namespace Microsoft.SDK.Project.Samples.DeactivateResources

    { class Program
       {
          [STAThread]
          static void Main()
          {
             try
             {
                const string PROJECT_SERVER_URI = "http://ServerName/ProjectServerName/";
                const string RESOURCE_SERVICE_PATH = "_vti_bin/psi/resource.asmx";

                ResourceWebSvc.ResourceDataSet resourceDs;
                PSLibrary.Filter resourceFilter;

                // Set up the Web service objects
                ResourceWebSvc.Resource resourceSvc = new ResourceWebSvc.Resource();

                resourceSvc.Url = PROJECT_SERVER_URI + RESOURCE_SERVICE_PATH;
                resourceSvc.Credentials = CredentialCache.DefaultCredentials;

                // Get the resources
                Console.WriteLine("Getting/Creating resources");
                Guid[] resources = EnsureEnterpriseResources(resourceSvc);

                // Deactivate resources.
                Console.WriteLine("Deactivating resources");
                resourceSvc.DeactivateResources(resources, false);

                resourceFilter = GetResourceFilter(resources);
                resourceDs = resourceSvc.ReadResources(resourceFilter.GetXml(), false);

                WriteResourceState(resourceDs);

                // Activate Resources so we can use them later if we want
                Console.WriteLine("Activating resources");
                resourceSvc.ActivateResources(resources, false);

                resourceDs = resourceSvc.ReadResources(resourceFilter.GetXml(), false);
                WriteResourceState(resourceDs);
             }
             catch (SoapException ex)
             {
                PSLibrary.PSClientError error = new PSLibrary.PSClientError(ex);
                PSLibrary.PSErrorInfo[] errors = error.GetAllErrors();
                string errMess = "==============================\r\nError: \r\n";
                for (int i = 0; i < errors.Length; i++)
                {
                   errMess += "\n" + ex.Message.ToString() + "\r\n";
                   errMess += "".PadRight(30, '=') + "\r\nPSCLientError Output:\r\n \r\n";
                   errMess += errors[i].ErrId.ToString() + "\n";

                   for (int j = 0; j < errors[i].ErrorAttributes.Length; j++)
                   {
                      errMess += "\r\n\t" + errors[i].ErrorAttributeNames()[j] + ": " + errors[i].ErrorAttributes[j];
                   }
                   errMess += "\r\n".PadRight(30, '=');
                }
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine(errMess);
             }
             catch (WebException ex)
             {
                string errMess = ex.Message.ToString() +
                   "\n\nLog on, or check the Project Server Queuing Service";
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine("Error: " + errMess);
             }
             catch (Exception ex)
             {
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine("Error: " + ex.Message);
             }
             finally
             {
                Console.ResetColor();
                Console.WriteLine("\r\n\r\nPress any key...");
                Console.ReadKey();
             }
          }


     Hope that helps.


    Thanks, Amit Khare |EPM Consultant| Blog: http://amitkhare82.blogspot.com http://www.linkedin.com/in/amitkhare82
    Monday, July 18, 2011 4:42 AM

All replies

  • Hello Ggopinath,

    SQL query on Project server published database to update/delete records is NOT recommanded & Supported. Please use PSI (project Server Interface ) for any data add/update instead of SQL update/delete.
    You can use Resource Web service which has a method to deactivate resource. You can use resource class or dataset & loop through the records to (LINQ to get Lastconnected date) based on that get the all resource records & deactivate them,

    Here is the ref:
    http://msdn.microsoft.com/en-us/library/websvcresource.resource_di_pj14mref_methods.aspx
    http://msdn.microsoft.com/en-us/library/gg208103.aspx

    Code:

    using System;
    using System.Collections.Generic;
    using System.Text;
    using System.Net;
    using System.Data;
    using System.Web.Services.Protocols;
    using System.Threading;
    using PSLibrary = Microsoft.Office.Project.Server.Library;

    namespace Microsoft.SDK.Project.Samples.DeactivateResources

    { class Program
       {
          [STAThread]
          static void Main()
          {
             try
             {
                const string PROJECT_SERVER_URI = "http://ServerName/ProjectServerName/";
                const string RESOURCE_SERVICE_PATH = "_vti_bin/psi/resource.asmx";

                ResourceWebSvc.ResourceDataSet resourceDs;
                PSLibrary.Filter resourceFilter;

                // Set up the Web service objects
                ResourceWebSvc.Resource resourceSvc = new ResourceWebSvc.Resource();

                resourceSvc.Url = PROJECT_SERVER_URI + RESOURCE_SERVICE_PATH;
                resourceSvc.Credentials = CredentialCache.DefaultCredentials;

                // Get the resources
                Console.WriteLine("Getting/Creating resources");
                Guid[] resources = EnsureEnterpriseResources(resourceSvc);

                // Deactivate resources.
                Console.WriteLine("Deactivating resources");
                resourceSvc.DeactivateResources(resources, false);

                resourceFilter = GetResourceFilter(resources);
                resourceDs = resourceSvc.ReadResources(resourceFilter.GetXml(), false);

                WriteResourceState(resourceDs);

                // Activate Resources so we can use them later if we want
                Console.WriteLine("Activating resources");
                resourceSvc.ActivateResources(resources, false);

                resourceDs = resourceSvc.ReadResources(resourceFilter.GetXml(), false);
                WriteResourceState(resourceDs);
             }
             catch (SoapException ex)
             {
                PSLibrary.PSClientError error = new PSLibrary.PSClientError(ex);
                PSLibrary.PSErrorInfo[] errors = error.GetAllErrors();
                string errMess = "==============================\r\nError: \r\n";
                for (int i = 0; i < errors.Length; i++)
                {
                   errMess += "\n" + ex.Message.ToString() + "\r\n";
                   errMess += "".PadRight(30, '=') + "\r\nPSCLientError Output:\r\n \r\n";
                   errMess += errors[i].ErrId.ToString() + "\n";

                   for (int j = 0; j < errors[i].ErrorAttributes.Length; j++)
                   {
                      errMess += "\r\n\t" + errors[i].ErrorAttributeNames()[j] + ": " + errors[i].ErrorAttributes[j];
                   }
                   errMess += "\r\n".PadRight(30, '=');
                }
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine(errMess);
             }
             catch (WebException ex)
             {
                string errMess = ex.Message.ToString() +
                   "\n\nLog on, or check the Project Server Queuing Service";
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine("Error: " + errMess);
             }
             catch (Exception ex)
             {
                Console.ForegroundColor = ConsoleColor.Red;
                Console.WriteLine("Error: " + ex.Message);
             }
             finally
             {
                Console.ResetColor();
                Console.WriteLine("\r\n\r\nPress any key...");
                Console.ReadKey();
             }
          }


     Hope that helps.


    Thanks, Amit Khare |EPM Consultant| Blog: http://amitkhare82.blogspot.com http://www.linkedin.com/in/amitkhare82
    Monday, July 18, 2011 4:42 AM
  • HI Amit,

    Thanks a lot. you have given great articles. Thank you so much.

    Monday, July 18, 2011 2:17 PM