Unable to open entire project via ODBC to SQL server

[Maurice Birchler]

The workstation that created the Project has no problem, however another
workstation running a slightly older version of the ODBC driver does not get
full access to the project tables using same version of MS-Project 2000 to
SQL 7.0

Yet MS-Query has no problem querying and updating the tables in the
database. Both users have ownership rights to the SQL database.

What is it in MS-Project that appears to lock a database down to the name of
the SQL Database owner and excludes other users with same rights?

 

[Rod Gill]

When a project is opened from a database, Project sets a flag that says "Hey
this user has this project open, so you can only have read only access". The
flag is reset when the project is closed. Sometimes if Project crashes, this
flag gets left set and needs manual clearing: it's in the MSP_Projects
table. Read projdb.htm for details (It's in one of project's program
folders).

As a general point for data integrity and reliability, all pc's should be on
the same MDAC release: the latest is 2.8 This isn't essential, but can
avoid some issues.

 

[Maurice Birchler]

Thanks, but it is not that. It is something to do with the Workstation ID
that stops MS-Project from being able to open the entire project, restricting
it to an import via a map. It seems that only the workstation that created
the project database via MS-Project SaveAs can open it completely.

 

[Rod Gill]

Failing a rights issue it looks like a corruption of some sort. In the
original workstation open the project, save to .mpp, delete the project in
SQL Server then open the .mpp file and re-save to SQL Server. I would also
get all workstations onto the same MDAC release.

 

[Maurice Birchler]

Thanks Rod, I finally figured it out. The connection to the SQL database must
be by the same SQL login as the login that actually created the Project.
Having SQL 'owner' access rights to the database is not enough.

The reason being that hidden SQL syntax is DATABASE.Owner.TABLE.COLUMN etc.
and only when the SQL login to the database is the actual owner who 'created'
the TABLES in the first instance, can the DATABASE.Owner prefix be omitted
from the syntax. This is what is required for MS-Project to open the entire
Project.

Which is a pity because this forces multiple users (Windows users) to use
unsecured connections to the Project SQL Database.