How to delete a column in database table

M

mizerajkula

I found information on the Internet that says "Check the msp_resources
table and check
the res_checkedoutby column. If it has "0000-00000-0000...." in it,
delete it"

How can I acces msp_resources table and then delete res_checkedoutby
column column?

Thank you.
 
M

mizerajkula

I don't think they meant delete the column, but delete the cell's contents
or replace with NULL.

--

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

---------------------------------------------------------------------------­-------------------------








- Show quoted text -
Click to expand...

Ok, but I still don't know how to delete the cell's contents
or replace with NULL. Can you please tell me how can I access the
table and delete the cell's content?
 
M

Mike

Ok, but I still don't know how to delete the cell's contents
or replace with NULL. Can you please tell me how can I access the
table and delete the cell's content?- Hide quoted text -

- Show quoted text -
Click to expand...

You'll need to have access to the SQL databases directly to make the
recommended fix. Ask a DBA or someone with access to assist you.
Messing with these tables isn't recommended and can be very dangerous,
however, in this case, I don't know of another solution. Know that
these queries should be ran against your "published" database. Here
are a couple of queries that may help:

To query for those resources you're having issues with (this only lets
you see which users will be affected by the update query):
SELECT * FROM MSP_RESOURCES WHERE RES_CHECKOUTBY =
'00000000-0000-0000-0000-000000000000'

To update the RES_CHECKOUTBY column to NULL for those users:
UPDATE MSP_RESOURCES SET RES_CHECKOUTBY = null WHERE RES_CHECKOUTBY =
'00000000-0000-0000-0000-000000000000'

Hope this helps!
-- Mike
 
M

mizerajkula

You'll need to have access to the SQL databases directly to make the
recommended fix.  Ask a DBA or someone with access to assist you.
Messing with these tables isn't recommended and can be very dangerous,
however, in this case, I don't know of another solution.  Know that
these queries should be ran against your "published" database.  Here
are a couple of queries that may help:

To query for those resources you're having issues with (this only lets
you see which users will be affected by the update query):
SELECT * FROM MSP_RESOURCES WHERE RES_CHECKOUTBY =
'00000000-0000-0000-0000-000000000000'

To update the RES_CHECKOUTBY column to NULL for those users:
UPDATE MSP_RESOURCES SET RES_CHECKOUTBY = null WHERE RES_CHECKOUTBY =
'00000000-0000-0000-0000-000000000000'

Hope this helps!
-- Mike- Hide quoted text -

- Show quoted text -
Click to expand...

Thank you Mike. I have expirience with SQL servers. I'm not DBA but I
have been working on SQL 2000 and 2003 databases and I would use SQL
Enterprise Manager to access tables or I would run Query Analyzer for
queries.

When I installed MS Project 2007 it installed SQL 2005 server but only
admin tools I see are "SQL Server Configuration Manager","SQL server
error and usage reporting", and "SQL Server surface Area
Configuration". I don't see anything similar to Enterprise Manager
that I used in previous versions of SQL.
Can anybody tell me how I can run queries against MS Project database
and is there any tool I can use to look at the tables and manage them?
 
J

James Fraser

Thank you Mike. I have expirience with SQL servers. I'm not DBA but I
have been working on SQL 2000 and 2003 databases and I would use SQL
Enterprise Manager to access tables or I would run Query Analyzer for
queries.

When I installed MS Project 2007 it installed SQL 2005 server but only
admin tools I see are "SQL Server Configuration Manager","SQL server
error and usage reporting", and "SQL Server surface Area
Configuration". I don't see anything similar to Enterprise Manager
that I used in previous versions of SQL.
Can anybody tell me how I can run queries against MS Project database
and is there any tool I can use to look at the tables and manage them?
Click to expand...

Sounds like you installed the SQL Express Edition, which does not
include many of the more powerful tools. Normally in SQL 2005 you
could use "SQL Server Management Studio." Here's one option for using
a command line client:
http://support.microsoft.com/kb/325003


Hope this helps...
James Fraser
 
M

mizerajkula

Sounds like you installed the SQL Express Edition, which does not
include many of the more powerful tools. Normally in SQL 2005 you
could use "SQL Server Management Studio." Here's one option for using
a command line client:http://support.microsoft.com/kb/325003

Hope this helps...
James Fraser- Hide quoted text -

- Show quoted text -
Click to expand...

Thank you James. This Project server was instlled by somebody else. It
is a stand alone installation and I think the only option with stand
alone installtions is SQL 2005 express (I can be wrong). But since
that is what I am dealing with it seems like my only option is to use
command line client. Thank you for supplying that microsoft article
for me.
 
Top