Help with OPENROWSET statement (SQL Server 2005 Express)

[david amend]

I am trying to access data in an MPP file from SQL Server using OLE DB. My
statement is


SELECT * FROM
OPENROWSET('Microsoft.Project.OLEDB.11.0','PROJECT
NAME=C:\PSCRP.mpp','SELECT TaskUniqueID FROM TASKS')

which returns the following when executed:

Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider "Microsoft.Project.OLEDB.11.0"
for linked server "(null)".

Help to resolve this would be great as I have been trying to link up Project
2003 with SQL Server 2005 for quite a while now and just can't seem to get it
right even though it appears to be do-able.

Thanks

 

[Rod Gill]

The OLEDB driver for Project is limited so it may not support this type of
use. Full details of what it can do are in the pjoledb.htm file in one of
project's program folders.

FYI Project 2007 no longer supports OLEDB.

--

Rod Gill
Project MVP

Project VBA Book, for details visit:
http://www.projectvbabook.com

NEW!! Web based VBA training course delivered by me. For details visit:
http://projectservertraining.com/learning/index.aspx

 

[david amend]

Thanks Rod.

I suppose elaborating on what I am trying to accomplish will help get
better results. My main objective is to be able to extract timescale data for
Work and ActualWork from a resource pool MPP file. This is for reporting
purposes so that I can report actual work and forecasts using a fiscal
calendar (which Project does not support). In addition to the timescale data,
I need to be able to extract the resource details (e.g. name, initials, etc),
the task details (e.g. description), and the name of the project to which the
task belongs.

I have written a VBScript that opens this file and extracts the data using
the TimeScaleData method against a collection of assignments which inserts
the results directly into SQL Server but this approach takes too long.

I have also tried exporting the MPP to a SQL Server database but I am having
trouble understanding the relationships between the tables since the
msp_assignements table does not have records for the assignments from the
projects sharing resources from the resource pool.

To overcome this, I was interested in the OLEDB provider since it seemed to
be able to provide what I need however can't get past this issue.

Perhaps I am on the wrong track and there is a better way of doing this. Any
suggestions would appreciated but please note that for now I am limited to
Project 2003 (not server).

Thanks,
David.

 

[Rod Gill]

The pjdb.htm file in one of project's program folders describes the db
structure.

You can also try using vbscript to use oledb to read the data. I find
however that a VBA macro in project exporting data to SQL Server works well.
VBA is surprisingly fast.

--

Rod Gill
Project MVP

Project VBA Book, for details visit:
http://www.projectvbabook.com

NEW!! Web based VBA training course delivered by me. For details visit:
http://projectservertraining.com/learning/index.aspx