sql server programming: Hello,

I have three tables in my existing database, (in a SQL Server 2000 server).
- a "Students" table,
- a "Classes" table, and
- a "StudentIsInClass" table.

The "StudentIsInClass" table is the relationship table linking a record in
the Students table to a record in the Classes table. This is effected with
two foreign keys:
FK_StudentIsInClass_Students
FK_StudentIsInClass_Classes

Recently, there was a problem in our application when changing the
"StudentNumber" for a Student. This is the primary key and so I get an error
because of the relationship. The fix I want to apply is to change the
foreign key FK_StudentIsInClass_Students. I need to turn on the function of
"Cascade Update Related Fields". I use the GUI functions in SQL Server
Enterprise Manager to do this but, when I try to save the change, I get an
error:

'Students' table
- Unable to delete relationship 'FK_StudentIsInClass_Students'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL
Server]'FK_StudentIsInClass_Students' ????????
[Microsoft][ODBC SQL Server Driver][SQL Server]??????????????????

(The ???? result because my SQL Server is running on a foreign language
server, so I can't read the exact error text.)

Does anyone have a clue why I would get this error? Any suggestions for
what to do about it? I appreciate any advice and help.

Many thanks in advance,

Justin Little (JustinLittle@discussions.microsoft.com) writes:
[quoted text, click to view]

It would certainly have helped to see the text of the error message.
I assume that the foreign language does not use the Latin script?

Anyway, rather than using Enterprise Manager for chaning tables, which
is dangerous as the table designer is buggy, use Query Analyzer and
type the DLL.


DECLARE @err int
BEGIN TRANSACTION

ALTER TABLE StudentsIsInClass DROP CONSTRAINT FK_StudentIsInClass_Students
SELECT @err = @@error

IF @err = 0
BEGIN
ALTER TABLE StudentsIsInClass ADD
CONSTRAINT FK_StudentIsInClass_Students FOREIGN KEY (StudentID)
REFERECENS Students(StudentID)
ON UPDATE CASCADE
ON DELETE CASCADE
SELECT @err = @@error
END

IF @err = 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION

It is not unlikely that that this will fail too, but at least in that
case you will see an error number. You can then look up that number in
SQL Server Books Online, provided that you have an English version of
Books Online installed on your computer.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

Hi Erland,

Many thanks for your advice on using Query Analyzer to get a readable error
message. (Thanks also for the tip about bugs in the Enterprise Manager GUI,
I'll bare that in mind in the future.)

I took your SQL procedure and used it in Query Analyzer. I find that there
is an error that occurs when I try to execute the line that reads "ALTER
TABLE StudentIsInClass DROP CONSTRAINT FK_StudentIsInClass_Students".

The error number I get is Msg 3728.
Server: Msg 3728, Level 16, State 1, Line 1
'dbo.FK_StudentIsInClass_Students' 並不是條件約束。

The server, as you guessed, does not use a Latin script. It's Chinese, so
the error message comes through in Chinese. On Query Analyzer I can see the
Chinese characters instead of a bunch of question marks. Books Online gives
the English message as:
'%.*ls' is not a constraint.

This seems strange to me. Also in Query Analyzer, in the Object Browser
I've expanded the tree beneath my "StudentIsInClass" table, and opened the
Constraints sub-section. There are three constraints listed there.
- PK_StudentIsInClass (StudentNumber, ClassID)
- FK_StudentIsInClass_Students (StudentNumber)
- FK_StudentIsInClass_Classes (ClassID)

If I try to right-click and delete any of these relationships I get the same
error message. (In fact, I can't delete any of constraints on any tables in
my entire database!)

Do you have any idea why I'm getting this confusing message?

Many thanks for your support.
Justin

PS: I'm connecting to the database using a user id called "bigbyte",
supplied to me by the ISP. The tables all belong to this user id (e.g.
"bigbyte.StudentIsInClass"), however I notice that the constraints are
members of the dbo group (e.g. "dbo.FK_StudentIsInClass_Students"). Do you

Justin Little (JustinLittle@discussions.microsoft.com) writes:
[quoted text, click to view]

Now, that was an interesting message...

[quoted text, click to view]

Most probably. I've tried to recreate this situation, but I was not
successful. When I create tables owned by a non-dbo user, and set up
a constraint, the constraint is owned by that user. The same happens
if I create the tables as owned by dbo, and then change ownership.
Maybe there was a bug in some previous version/service pack of SQL Server
that could cause this situation.

Since this is an abnormal situation, it is difficult to give sugestions
that I know work. I would try to change ownership of the table to
dbo, and then back to bigbyte.

You may have to talk to your ISP about this, as you may need someone
with dbo rights to perform this operation.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

Hi Erland,

I got dbo rights from the ISP for the day. I tried what you suggested about
changing the ownership of the table. Now I'm no SQL Server guru but I found
out that the stored procedure for doing this is called sp_changeobjectowner.

So I tried running the following command:
EXEC sp_changeobjectowner 'StudentIsInClass', 'dbo'

I get an error message saying:
Server: Msg 15505, Level 16, State 1, Procedure sp_changeobjectowner, Line 63
無法變更物件 'StudentIsInClass' 的擁有人或其所屬的子物件，因為新的擁有人 'dbo' 已有相同名稱的物件。
or in English:
Cannot change owner of object '%ls' or one of its child objects because the
new owner '%ls' already has an object with the same name.

HOWEVER, when I try to run the same sp on a table that has no constraints,
it runs through just fine and changes the ownership. Could this mean that
the database has some inconsistencies in the constraints that it is storing?

When I first created these tables (about a year or more ago) I used the
"bigbyte" user. Later, I noticed that all the ownerships had changed to
"dbo". I guessed that the ISP had been mucking around and changed it all for
reasons best known to them. (Like I said, I'm no guru so I don't know what
all this ownership stuff is really about anyway.) However, later I started
having trouble, particularly in using DTS scripts to export this live data to
my "dev" database and I asked them to change the ownership back to "bigbyte".
Now it seems all my tables are owned by "bigbyte" but all the constraints
are owned by "dbo".

Is there a stored procedure or something I can use to try to track down
multiple instances of the same constraint name, possibly owned by different
owners? Perhaps my original constraints are still bouncing around somewhere
inside the db's system tables, unknown to all of us?

Many thanks,
Justin





[quoted text, click to view]