Invalid Primary Key error during table Linking

[lorirobn]

Hi,

Something strange has happened to my table. I used Enterprise Manager today to delete 3 columns. When I went to re-link the table using Access Linked Table Manager, it gave me an error. I then deleted the link to the table, and tried to Link it again using 'Get External Data---Link Tables'. I am getting an error (no surprise!):

" 'dbo.tblSpaceUse.PK_RoomID' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long".

When I go into Enterprise Manager to 'manage Indexes' on the table, it shows me that the existing index is in fact dbo.tblSpaceUse.PK_RoomID.

About a month ago, I had to rename the index, because it had been pointing to the wrong table. The SQL I used to rename it (in Query Analyzer) is:
EXEC sp_rename 'dbo.tblSpaceUse.PK_RoomID', 'tblSpaceUse.PK_RoomID', 'INDEX'

I have been using the table successfully since then, until today. I have not done anything with the index; the only change I attempted was to delete 3 columns (not related to the index).

I tried to run the rename SQL again (a desperate attempt!) and get the error message:
Server: Msg 15248, Level 11, State 1, Procedure sp_rename, Line 192
Either the parameter @objname is ambiguous or the claimed @objtype (INDEX) is wrong.

Any ideas on what went wrong and what I can do to fix it???

Thanks,
Lori

 

[icemel]

I have no idea what your problem is, but if I were you, I would drop and recreate the table along with the correct index definitions (saving the data in an interim table, of course) Then try to relink it.

 

[lorirobn]

That is a good idea, thanks. How do you save the data to an interim data in SQL Server? (I am researching that but it doesn't seem as easy as I would think...).

 

[icemel]

It's pretty easy. From BOL:

Inserting Rows Using SELECT INTO
The SELECT INTO statement creates a new table and populates it with the result set of the SELECT. The structure of the new table is defined by the attributes of the expressions in the select list, for example:

SELECT Shippers.*, Link.Address, Link.City,
Link.Region, Link.PostalCode
INTO NewShippers
FROM Shippers
JOIN LinkServer.DB.dbo.Shippers AS Link
ON (Shippers.ShipperID = Link.ShipperID)

p.s. Be sure to backup your original table before deleting it! Just re-name it to something else... you don't need to get rid of it completely, just in case.

You can use sp_rename to do this:

sp_rename [ @objname = ] 'object_name' ,
[ @newname = ] 'new_name'
[ , [ @objtype = ] 'object_type' ]

Good luck

 

[icemel]

Actually the selct into is VERY simple:

Select * from old_table
into new_table

It will create new_table for you automatically.

I don't know if it transfers over indexes, etc. Be sure to add them afterwards, if necessary.

 

[lorirobn]

Thank you for the great instructions! Did just as you said - created a new table, backed up the old, created new PK. And voila - it's working a-ok now. I have no idea what happened, but am greatly relieved that it's working now!!
Thanks for your help,
Lori

 

[icemel]

No problem, have a nice weekend