using Project 200x OLEDB provider from SQL
Hi all!
I need to get some data from MS Project 2000 through its
OLE DB provider (the only source, since it's not exposed
in DB). Nature of the solution is asking that the code is
a SQL stored procedure. The environment has project plans
stored in a SQL DB (7 or 2000) on Win2000 server. Same
functionality is also tested and projected for Project
2002 beta 4 on SQL2000.
I'm trying to execute the following variations in Query
Analyzer (I even went down to a single-file example):
a) select * from openrowset('Microsoft.Project.OLEDB',
'DSN';'admin';'', Project)
b) select * from openrowset('Microsoft.Project.OLEDB',
'<DSN>\Project1';'admin';'', Project)
c) select * from openrowset('Microsoft.Project.OLEDB',
'User ID=admin;Data Source=DSN;Project
Name=Project1;Initial Catalog=Project1', Project)
d) select * from openrowset('Microsoft.Project.OLEDB',
'Z:\PATH\Project1.mpp';'admin';'', Project)
and get the following results:
a) Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Project.OLEDB' reported an
error.
[OLE/DB provider returned message: Cannot find
file '<DSN>\']
b) Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Project.OLEDB' reported an
error.
[OLE/DB provider returned message: Cannot find
file '<<DSN>\Project1>\']
c) Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Project.OLEDB' reported an
error.
[OLE/DB provider returned message: Cannot open project '']
d) Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Project.OLEDB' reported an
error.
[OLE/DB provider returned message: Cannot find
file '<Z:\PATH\Project1.mpp>\']
There're several things that are not clear:
The Net is full of examples of accessing OLE DB sources
like Access or Oracle. There's not a single one that I
could find showing the intricasies of Project provider.
Project's OLE DB provider exposes data through several
pseudo-tables (Project, Tasks, Resources, etc.) for only
one plan! You choose which one when you establish
connection. In VB code using ADO 2.5 I simply specify the
following connection string:
Provider=Microsoft.Project.OLEDB;User
ID=admin;Password=;Data source=DSN;Initial
Catalog=Project1;Project name=Project1;
and can execute 'select's from any of provider tables.
First I had to create a .udl file to figure out what to
use to specify the project plan I'm interested in, but
even here this value seems to be duplicated in 'Project
name' and 'Initial Catalog' properties.
According to documentation on OPENROWSET() you can
give 'catalog' and 'schema' to qualify referenced object.
In my case the object is one of the tables, say 'Project',
but what should I use for 'catalog' or 'schema'? I tried
feeding "Project1" (plan's name) as the catalog, since
I've no idea who should be the owner, I asked for
object "Project1..TableName". No matter what though I
still end up with same connection errors...
Has anyone done something similar, or have any knowledge
on Project OLE DB provider that could explain the errors
and help resolve it? I appreciate every effort!
Thanks,
Dmitri
