Recalculate Project from SQL Server

[Sue]

Situation: User enters actual finish etc using a Web interface. Information
is stored in a SQL Server database.

Goal: MS Project recalculates so that new schedule information is sent back
to the user.

Environment: Project 2000, SQL Server 2003, Windows Server 2000, IIS.

The short question is: What is the best way to do this?

One way that works: Using PHP as the Web Language, I have successfully done
the recalc as follows: PHP calls rick.bat which calls CalculateProject.exe
(a VB Program) which uses OLE Objects to start project, open, recalculate,
save, and close the file, then end project. Until recently when this
mysteriously quit working. I am checking out that avenue, but am also
exploring better options since this is VERY SLOW (not to mention ugly)

Other things I have considered (but haven't figured out how to make it work)
are:
- Call MS Project directly from PHP (PHP developer says it can't be done)
- Switch to ASP.net and call MS Project directly (Working on this one, but
need a short term fix for production right now)
- Use a trigger and stored procedure in SQL Server (I like this option but
don't know how to do it.)
- Use a Job in SQL Server (not so crazy about this, and don't know how to
do it)

other related information: the update to the database is fine. I can
manually open and save the project from/to the database.

secondary question: Must I upgrade Project to do this 'correctly' (speed is
important)

Thank you for your help!

 

[Ed Morrison]

Since the project plan needs to recalculate the tasks, you must open MS
Project directly or use its object model. It can be a painfully slow
operation. In order to speed it up, try the following:
- Place the program that opens project closer to the data source so it
reduces network latency
- Make sure you have only one project open at a time to conserve memory
- Add more memory if necessary
- Don't save it twice. The project is saved on the publish operation, so it
does not need to be saved again.

Please let us know how this works for you.

 

[JackD]

I can't address most of your questions except to tell you that upgrading
project to any current version is unlikely to increase the performance. I
also do not believe that a stored procedure is going to give you the results
you are looking for. The way project works it can only recalculate if the
file is loaded into the application. There is no way to do calculations on
the database alone.

Presumably you could keep project open all the time and that would solve the
start and shutdown project time lag, but the file still needs to be opened
by project and that takes some time.

Realtime updating of a project is not an easy thing to do.

 

[JackD]

Ed,

Just a reminder. The poster is using Project 2000.
Project 2000 is pre-project server. It does not "publish". It just saves.

 

[Sue]

Thank you so much for your help. Now I need help with the syntax...

From VB, I use the command
bfopen = objMspApp.FileOpen(<DB_Name>\Project_name, False, , , , , ,
"username", "password", "MSProject.ODBC")

So translating that to a Stored Procedure, I tried:

EXEC @hr = sp_OAMethod @object, 'FileOpen', NULL, '<DB_Name>\Project_name',
False, , , , , , 'username, 'password' , 'MSProject.ODBC'

This gives me a syntax error. Can you help me?

Sue

 

[JackD]

No, sorry. I'm not much good at stored procedures. You might try asking in
one of the SQL server groups.

 

[Sue]

Just in case anyone else needs this information... this is the correct SQL
Server Stored Procedure syntax to call a MSProject method: (after you have
already created the MSProject.Application object as @object) Note that in
order for this to run, the user must have sysadmin permissions.

EXEC @hr = sp_OAMethod @object, 'FileOpen(<DB_Name>\Project_name,False, , ,
, , ,username, password, MSProject.ODBC)'

Sue

 

[JackD]

Thanks for posting the solution back here. I'm sure someone will be looking
for it in the future.

 

[Ed Morrison]

Thanks Jack. I should have read her email more carefully.

--
Ed Morrison
msProjectExperts
"We wrote the books on Project Server"
http://www.msprojectexperts.com
FAQ - http://www.projectserverexperts.com

 

[Zac Maclean]

Yeah.. Me.

Now.. Is that "CalculateProject.exe" VB program proprietary? I need the
functions you mentioned, plus saving a main baseline, and a secondary
baseline that is overwritten regularly. I can muddle together what I need
much easier if I have a little code to go by. And I try to post my fixes
when I can.

My other modification is I will be calling this as part of a "Windows
Service", as I have 200+ (and growing) projects to parse. I'll be testing
on 20 -30 at first, but (again) will post finidings when I can.

Z