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();
}
}
}
I love the idea of this utility! But I do not understand how to use this code. Where is this code to be transfered to and how is it executed? Thanks for the code!
Create a C# . net Project Using Visual Studio, add reference to the appropriate web service , and execute this code against any button click event
Sunil, is it possible to filter out ‘saved’ projects, i.e. those that are in draft/planning stage and are not ready to be distributed to the public?
Hi Maxim A
Yes Absolutely Possible, instead of getting all project list from Reporting DB use the below mentioned query to get saved projects 🙂
select p.WPROJ_LAST_PUB, p.PROJ_LAST_SAVED, p.PROJ_NAME, p.Proj_UID from MSP_PROJECTS p where p.WPROJ_LAST_PUB < p.PROJ_LAST_SAVED
Hope this helps
I’ve meant auto publish only published projects in the utility above.
Oh my apologies, missed the part i.e. only published project, yes thats possible, the query below would give all the projects which are not saved
Run this query against Draft DB, and pass the ProjectUID for publishing
SELECT p.WPROJ_LAST_PUB,
p.PROJ_LAST_SAVED,
p.PROJ_NAME,
p.Proj_UID FROM MSP_PROJECTS p
WHERE p.WPROJ_LAST_PUB >= p.PROJ_LAST_SAVED
Hope this helps
I hope you can help. I don’t quite understand what type of visual studio C#.net app to create a web or windows? Also does this get added as a server side event handler in PWA? Or is this a standalone app? Also how do I make it automated? Once it’s working do I just setup a task schedule to run it?
I copied the code to a C# windows form. Added the reference to the office.project.server.library.
However I get build errors on ProjectWebSvc and statusStrip1 and QueueSystemWebSvc.
Is there another reference I am supposed to add? I am by no means a programmer. Any help would be appreciated. If it matters this development of this code is NOT being created on the server running sharepoint its a workstation.
Hi Mike
I did used a Windows app to create this utility, it doesn’t gets added to server side event handler, rather its a stand alone application, which can be scheduled to execute at designated intervals
Status Strip code you can comment, as i am using a status strip to display the progress and status of publishing, for the other 2 errors ProjectWebSvc & QueueSystemWebSvc, you need to add the web reference to project server web service, to add refer to this article : http://msdn.microsoft.com/en-us/library/ms503195(v=office.12).aspx
Since this code is using web services hence can be executed from anywhere be it a workstation or a server
Let me know if this helps
Sunil,
Thanks so much for your quick response. It worked no more build errors! However now I have a new problem.
I see that the code is generated in a “Form Load” which is the equiv of the code running on Form being opened. But when it opens nothing happens, no publishes run. I did a db sql profiler and I dont see the form connections happening at all.
Here is what I changed – I added in teh myCredential fields with for the moment “administrator” along with its password and domain id. (this account for the moment also has admin rights to all of PWA and Sharepoint sites (again just for the sake of it not being a windows credential issue)
then I updated the GetProjectListFromSQL connection string to point to the db sever
called dbsvr01 the generic reporting catalog and the sa account.
I noticed that it doesn’t even created the log files or folders. so I manually creaeted the log directory and it doesn’t create the .txt file.
Do you have any idea’s? Thank you so much for the assistance thus far.
P.S.
For the sake of testing I turned OFF the servers hosting sharepoint ran the script and got back a cannot connect to remote server. Turned them back on the errors went away. So I now its TRYING to do something. But SQL profiler can’t pick up the traffic.
For the record as well the IIS and DB are on seperate VM boxes.
Hi Mike
Ideally it should work with the changes you have specified, however since its not working hence you might wanna put some breakpoints in visual studio code and check where its failing or skipping the records
else lets set up a time and we can work that out via a live meeting session 🙂
Looks like I got it working I guess MS visual studio 2008 still has some bugs – I guess because the code was copied and pasted it didn’t setup the correct button1_click events or Load event handlers correctly.
I changed the line Form1_Load to a button2_click to see what would happen if I attached it to a button. It ran! Published the files and all. But then I converted it back to a Form1_Load it didn’t do anything, but the button click still worked..
apparently In the public Form1()
{ Initialize Component(); } I needed to add this addition right belife the Initialize Component();
this.Load += new System.EventHandler(this.Form1_Load);
Viola it worked! Your script is going to pay huge divdends I appreciate all your help. I was looking around for something like this for awhile. Again thank you so much!
Hi Sunil,
I am trying to run your code, it is running but i didn’t see a button in the form to publish it.
Hi Jay
this code works on form load itself, so as soon as you press F5 it should launch the code & start publishing the project 🙂 Look for the code line
private void Form1_Load(object sender, EventArgs e)
Also for 2010 recently i pressed another post which uses powershell script to publish all projects, you might want to check this one too, it serves the same purpose… https://epmxperts.wordpress.com/2012/09/06/autopublish-all-projects-using-powershell/
Thanks Sunil. It is working.
Hi Sunil
I tried this utility for our 2013 environment .
I got one error that “The request failed with HTTP status 401: Unauthorized.” in QueueDataSet = QueueSystem.ReadAllJobStatusSimple …. step.
I tried the powershell script also but it is not publishing any projects.
Can you help me.