Extracting Data from the SQL database

L

Lee

Has anyone successfully extracted data directly or indirectly (using Excel or
any other tools) from the SQL database and imported it into another
application (accounting app).

Can any one suggest the best method for getting data out of PRoject Server
2003.

Thanks,

Lee
 
J

JackD

Lee said:
Has anyone successfully extracted data directly or indirectly (using Excel or
any other tools) from the SQL database and imported it into another
application (accounting app).

Can any one suggest the best method for getting data out of PRoject Server
2003.

Thanks,

Lee
Click to expand...

I've pulled things out into excel using the Query function.
I've made reports in Access also through writing queries.
The best method is

Select * from ....

-Jack
 
G

Gary L. Chefetz \(MVP\)

Lee:

Microsoft publishes a Software Developers Kit (SDK) for every product they
make. You can find these in the MSDN library at www.microsoft.com/msdn

Your first homework assignment is to locate and read through the SDK for
Project and Project Server. You should also locate the projdb.htm and
svrdb.htm files in the help directory of your Project Server installation.
These are the data dictionaries for both sides of the database.

--

Gary L. Chefetz, MVP
"We wrote the book on Project Server
http://www.msprojectexperts.com

-
 
C

Christopher Woods

JackD

Beware, this is quite easy but must be well tested as evryone (inc MS ) told us that there are a lot of grey areas within the Data. However we recently worked with a major Professional Srevices wing to allow the creation of reports through EXCEL, Access or any other app with a series of Custom built ASP brokers to query the data and present to any data consumer, with filtration and manipulation possible over Http.

Its not too difficult but ensure your test team is awake.

Chris

www.askfivewhys.com

ask why ...know how !
 
E

Earl Lewis

Lee,

There are several ways you can approach this but the bottom line is you need to be able to create a connection to the SQL server from the client machines that will do the extraction. As long as you have a cooperative and knowledgable SQL admin this shouldn't be a problem. They just need to set you up with appropriate permissions.

Most DBAs will do this in SQL server and give you permissions to a specific db and usually make those permissions read-only - so you don't compormise the data integrity of your production system.

I would suggest that you NOT use the MSProjectUser or MSProjectServerUser accounts that are setup during installation of project server. These accounts have broad persmissions to insert, update and delete. Very risky if someone doesn't know what they're doing.

After the account is setup on the SQL server you can create an ODBC connection from the client workstation to the SQL server. Once that is done you can use Access, Excel or whatever flavor of M$ tools you like to query the data.

Should also mention here, there is something called PDS (project data service) that allows you to query and even update/insert data into the project server database. This is a toolset designed around XML and SOAP so if that interests you you should look into the Project Server SDK on the M$ website.

Earl
Has anyone successfully extracted data directly or indirectly (using Excel or
any other tools) from the SQL database and imported it into another
application (accounting app).

Can any one suggest the best method for getting data out of PRoject Server
2003.

Thanks,

Lee
 
Top