Feeds:
Posts
Comments

Archive for December, 2010


While blogging across social technet forum, i came to a question wherein a user didn’t wanted to use PSI (and infact there isn’t any way to save a file as “template” to project server using PSI or atleast i haven’t seen one)

Though the standard practice is to deploy MPT templates using MS Project itself, there isn’t any PSI for automating the same or at least i couldn’t find any documentation anywhere, someone else might want to comment, please feel free,
and since template deployment doesn’t happens very often, or i would say it’s not a very frequent process it doesn’t makes sense to have a code developed for it, however user’s client wanted it to be done using C# / .NET so he started exploring the opporutnities of using MS Project Interop Assembly, curiosly I started coding for the same and here is what I came up with try it, Viola its working J

Add Microsoft.Office.Interop.MSProject Assembly reference to your C# Project you can find it under COM tab

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.MSProject;
using System.Reflection;

namespace RnDProject
{
    public partial class InteropInvoke : Form
    {
        public InteropInvoke()
        {
            InitializeComponent();
        }
        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                string fileLoc = @”C:\sunil\Test Development Plan.mpp”;
             ApplicationClass ProjectApp = new ApplicationClass();
                // Open the file. There are a number of options in this  
                // constructor as you can see
                ProjectApp.FileOpen(fileLoc, true, Type.Missing, Type.Missing, Type.Missing,   Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, PjPoolOpen.pjDoNotOpenPool, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                ProjectApp.FileSaveAs(“<>\\CurrencyTest12”, PjFileFormat.pjMPT, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true, true, true, true, Type.Missing, true);
             // Get the active project
                Project proj = ProjectApp.ActiveProject;
            }
            catch (System.Exception Ex)
            {
                //catch Exception here to determine if file exists 🙂
            }
        }
    }
}

Read Full Post »


In case of orphan record, if you want to delete it, you might try the below mentioned script (at your own risk), would strongly recommend have a backup of all the DB’s before trying

Run the query to identify all orphan projects
—- Identifies Orphan Records———
SELECT PROJECTNAME, PROJECTUID FROM MSP_EPMPROJECT
WHERE  PROJECTUID NOT IN
(SELECT PROJ_UID
FROM <ProjectServer_Published>.DBO.MSP_PROJECTS)

Execute the below mentioned SP in RDB supplying the ProjectUID of error causing project 

EXEC MSP_Epm_DeleteResourcePlan <ProjectUID >
EXEC MSP_Epm_DeleteProject <ProjectUID>

Read Full Post »


Found an error, while previewing the Status Update, view wouldn’t load saying “Failed to load the view” after reviewing ULS log  found errors but still couldn’t figure out the exact issue, searching on GOOG led me to the resolution

“If you have summary task linked to a Milestone” remove that from the plan and re publish, this should do the trick 🙂

Read Full Post »


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Net;
using System.Threading;
using System.Web.Services.Protocols;
using System.Data.SqlClient;
using System.IO;
using PSLibrary = Microsoft.Office.Project.Server.Library; 

namespace AutoPublishUtility
{
    public partial class Form1 : Form
    {
        ///////////////////// DECLARE CONSTANTS//////////////////////////
        private const string PROJECTWEBSERVICE = “_vti_bin/PSI/Project.asmx”;
        private const string QUEUEWEBSERVICE = “_vti_bin/PSI/QueueSystem.asmx”;
        private string baseUrl = “http://ServerName/pwa/“;
        //Int16 PrgBarVal = 0;
        NetworkCredential myCredential = new NetworkCredential(“username”, “password”, “domain”);

        public Form1()
        {
            InitializeComponent();
        } 

        private void Form1_Load(object sender, EventArgs e)
        {
            DataSet ProjectData = new DataSet();

            //////////CREATE LOGFILE FOR LOGENTRY//////////////
            DateTime currentDateTime = DateTime.Now;
            String dateStr = currentDateTime.ToString(“yyyy_MM_dd HH_mm_ss”);
            string FilePathAndName = “C:\\ProjectServerReportingDBSync\\Log\\” + “ProjectServerAutoPublishLogEntry_” + dateStr + “.txt”;
            StreamWriter sw;
            sw = File.CreateText(FilePathAndName);
            sw.Close();                      

            ///////GETTING LIST OF ALL PROJECTS WHICH ARE NOT CALCELED OR CLOSED FROM REPORTING DB//////////
            ProjectData = GetProjectListFromSql();                       

            ///////////////////// PASSING PROJECT UID TO “PUBLISH PROJECT” METHOD FOR PUBLISHING////////

            for (int x = 0; x < ProjectData.Tables[0].Rows.Count; x++)
            {
                Guid PrjId = new Guid(ProjectData.Tables[0].Rows[x][0].ToString());
                ////////////////CHECK IF QUEUE HAS LESS THAN 5 PUBLISHING JOBS ALREADY IN QUEUE, IF YES ADD ANOTHER PROJECT TO IT ELSE SLEEP////////
                while (1==1)
                {
                    int QueueWaitTimeCounter = 0;
                    if (GetQueueStatus() < 5)
                    {
                        PublishProjects(PrjId);
                        this.statusStrip1.TabIndex = 1;
                        this.statusStrip1.Text = ” ‘” + ProjectData.Tables[0].Rows[x][1].ToString() + “‘ ” + “is Getting Processed”;

                        //////////////////// WRITE TO LOG FILE /////////////////////
                        WriteLog(ProjectData.Tables[0].Rows[x][1].ToString(),FilePathAndName);
                        QueueWaitTimeCounter = 0;
                        break;  
                    }
                    else
                    {
                        if(QueueWaitTimeCounter < 600) // CHECK WETHER QUEUE PROCESS IS NOT WAITING FOR MORE THAN 30 MIN
                        {
                            System.Threading.Thread.Sleep(3000); // 3 SECONDS SLEEP TIME
                            QueueWaitTimeCounter += 1;
                        }
                        else
                        {
                            WriteLog(“AutoPublish Utility Terminated Due to Queue Stall at time – “+DateTime.Now, FilePathAndName);
                            CloseApplication();
                        }
                    }
                }           

            }
            CloseApplication();
        }
        private DataSet GetProjectListFromSql()
        {
            /////////////////RETRIEVE ALL PROJECTS LIST WHICH ARE NOT CANCELLED AND COMPLETED
           /////////////////FROM PROJECT SERVER REPORTING DB //////////////////////////////
            this.statusStrip1.Text = “Loading Project Data……….”;
            string ConnStr = “Data Source=ServerName;Initial Catalog=ProjectServer_Reporting;User Id=usrname;Password=password”;
            SqlConnection Conn = new SqlConnection(ConnStr);
            SqlCommand Cmd = new SqlCommand(“SELECT [ProjectUID],[ProjectName],[ProjectCondition] FROM [ProjectServer_Reporting].[dbo].[MSP_EpmProject_UserView] WHERE [ProjectCondition] <> ‘Completed’ AND [ProjectCondition] <> ‘Canceled’ “);
            Cmd.Connection = Conn;
            SqlDataAdapter Da = new SqlDataAdapter(Cmd);
            DataSet Ds = new DataSet();
            Da.Fill(Ds);
            return Ds;
        }
        private void PublishProjects(Guid PrjID)
        {
            try
            {   //////////// PUSHING PROJECT TO QUEUE FOR PUBLISHING///////////////
                ProjectWebSvc.Project PrjWebSvc = new AutoPublishUtility.ProjectWebSvc.Project();
                PrjWebSvc.Url = baseUrl + PROJECTWEBSERVICE;
                //PrjWebSvc.Credentials = CredentialCache.DefaultCredentials
                //CredentialCache myCache = new CredentialCache();
                PrjWebSvc.Credentials = myCredential;
                Guid QueueJobId = Guid.NewGuid();
                PrjWebSvc.QueuePublish(QueueJobId, PrjID, false, “”);
                System.Threading.Thread.Sleep(10000);

            }
            catch (System.Web.Services.Protocols.SoapException ex)
            {
                string errMess = “”;
                PSLibrary.PSClientError error = new PSLibrary.PSClientError(ex);
                PSLibrary.PSErrorInfo[] errors = error.GetAllErrors();
                for (int j = 0; j < errors.Length; j++)
                    errMess = errMess + errors[j].ErrId.ToString() + “\n”;
                errMess = errMess + “\n” + ex.Message.ToString();
               MessageBox.Show(errMess, “Error”, MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            catch (System.Net.WebException ex)
            {
                MessageBox.Show(ex.Message.ToString(), “Logon Error”,
                    MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
        private void WriteLog(string PrjName, string FilePathAndName)
        { 
            StreamWriter sw;
            sw = File.AppendText(FilePathAndName);
            string ConstructStringforLogFile = PrjName + ” — “+ ” Added to Queue For Publishing at Time – ” + System.DateTime.Now.ToString() ;
            sw.WriteLine(ConstructStringforLogFile);
            sw.Close();
        }
        private int GetQueueStatus()
        {
            QueueSystemWebSvc.QueueSystem QueueSystem = new AutoPublishUtility.QueueSystemWebSvc.QueueSystem();
            QueueSystem.Url = baseUrl + QUEUEWEBSERVICE;
            QueueSystem.Credentials = myCredential;
            QueueSystemWebSvc.QueueStatusDataSet QueueDataSet = new AutoPublishUtility.QueueSystemWebSvc.QueueStatusDataSet();
            QueueDataSet = QueueSystem.ReadAllJobStatusSimple(DateTime.Today.AddDays(-1), System.DateTime.Now, 100, true, QueueSystemWebSvc.SortColumn.QueueProcessingTime, QueueSystemWebSvc.SortOrder.Ascending);
           // this.dataGridView1.DataSource = QueueDataSet.Tables[0];
            int QueueJobsPublishing = 0;
            for (int x = 0; x < QueueDataSet.Tables[0].Rows.Count; x++)
            {
                if (QueueDataSet.Tables[0].Rows[x][2].ToString() == “3”)
                {QueueJobsPublishing += 1;}
            }
            return QueueJobsPublishing;
        }
        private void CloseApplication()
        {
            this.Close();
        }
        private void button1_Click(object sender, EventArgs e)
        {
            GetQueueStatus();
        }
    }
}

Read Full Post »

« Newer Posts