Trying to set up Realtionships unique problem

[kmclane]

I have several tables I imported from Access 97 to SQL 2000. I am trying to set up relationships so Reporting services can do it's wonderful drill down thing, but I get the error "Primary Key or UNIQUE constraint must be defined for table 'ACH_LogMth' before it can participate in a relationship".

None of my tables has any column that can be a Primary key, I need to create a new column for each table and populate it with unique values. What would be the simplest way to go about this? Thanks for any suggestions, Ken.

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[kmclane]

Great, I was playing around trying to add a new column and now my table is empty! At least it is not one of the big ones, but it's gonna be two days rebuilding it anyway since all the original data is on encrypted cd's. I don't suppose there is a way to undo this? I just got this put together and don't even have a backup yet.
Thanks, Ken.

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[kmclane]

I found a quick and dirty method recommended by a user on a SBS group I participate in, in QA run this:

Select * into "Newtable" From "OldTable" Where 1=0

This will create an empty table with all your existing fields. It will not create constraints and such. You can then add the extra column you need then use DTS to import the data from the old table to the new table. Once you verify your data is good, you drop the old table and rename the new one and you're set.
Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg