Table Corruption?

[Damien]

I have a table in my database called Users:

CREATE TABLE [Users] (
[UserID] [uniqueidentifie r] NOT NULL CONSTRAINT [DF_Users_UserID]
DEFAULT (newid()),
[UserName] [nvarchar] (20) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT
NULL ,
[password] [nvarchar] (40) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT
NULL ,
[EmailAddress] [nvarchar] (100) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NOT NULL ,
[ValidFrom] [datetime] NOT NULL ,
[ValidTo] [datetime] NULL ,
[passwordSalt] [bigint] NOT NULL ,
[FullName] [nvarchar] (200) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT
NULL ,
[CreatorUserID] [uniqueidentifie r] NOT NULL ,
[DeletorUserID] [uniqueidentifie r] NULL ,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[UserID],
[]
) ON [PRIMARY] ,
CONSTRAINT [UX_Users_UserNa me] UNIQUE NONCLUSTERED
(
[UserName]
) ON [PRIMARY] ,
CONSTRAINT [FK_Users_UsersC reator] FOREIGN KEY
(
[CreatorUserID]
) REFERENCES [Users] (
[UserID]
),
CONSTRAINT [FK_Users_UsersD eletor] FOREIGN KEY
(
[DeletorUserID]
) REFERENCES [Users] (
[UserID]
),
CONSTRAINT [CK_Users_Finish ed] CHECK ([ValidTo] is null and
[DeletorUserID] is null or ((not([ValidTo] is null))) and
((not([DeletorUserID] is null)))),
CONSTRAINT [CK_Users_ValidD ates] CHECK ([ValidFrom] <= [ValidTo])
) ON [PRIMARY]
GO

If you're looking at the Primary Key constraint above, you can probably
already see the problem (this script was produced by Query Analyzer).
If I try to get query analyzer to just script the PK, I get the
following error message:

[SQL-DMO]The name '' is not a valid object identifier, or is not a
valid format for this property or method.

I've googled for this phrase, and cannot locate it. If I drop all of
the constraints in my database, and reapply the constraints to Users
using the following script:

alter table Users add constraint
DF_Users_UserID DEFAULT NEWID() FOR UserID
go
alter table Users add constraint
PK_Users PRIMARY KEY
(
UserID
) on [PRIMARY]
go
alter table Users add constraint
UX_Users_UserNa me UNIQUE
(
UserName
)
go
alter table Users add constraint
CK_Users_ValidD ates CHECK
(
ValidFrom <= ValidTo
)
go
alter table Users add constraint
CK_Users_Finish ed CHECK
(
(
ValidTo is null and
DeletorUserID is null
)
or
(
not ValidTo is null and
not DeletorUserID is null
)
)
go
alter table Users add constraint
FK_Users_UsersC reator FOREIGN KEY
(
CreatorUserID
)
REFERENCES Users
(
UserID
)
go
alter table Users add constraint
FK_Users_UsersD eletor FOREIGN KEY
(
DeletorUserID
)
REFERENCES Users
(
UserID
)
go

I *still* have this wierd PK. I've checked through both the
INFORMATION_SCH EMA views and directly through the system tables, and so
far as they are concerned there is only a single column (UserID) acting
as part of the PK.

So, any ideas? It appears to be preventing us from setting up
replication. Thankfully, it also occurs in a restored backup of the DB,
so I'll be able to reproduce/attempt fixes to my hearts content.

Thanks,

Damien

[Erland Sommarskog]

Damien (Da************ *******@hotmail .com) writes:

I *still* have this wierd PK. I've checked through both the
INFORMATION_SCH EMA views and directly through the system tables, and so
far as they are concerned there is only a single column (UserID) acting
as part of the PK.
Exactly which system table did you check? sysindexkeys should be the one.

If you do

SELECT * FROM syscolumns WHERE id = object_id('User s') ORDER BY colid

Are the colid sequential from 1 and up? (My guess is that there is a
history of a dropped column involved.)
So, any ideas? It appears to be preventing us from setting up
replication. Thankfully, it also occurs in a restored backup of the DB,
so I'll be able to reproduce/attempt fixes to my hearts content.

DBCC CHECKCATALOG could be worth trying.

As simple fix may be to rename the table, create a new and move data
over.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Damien]

Sorry, posting through new Google, can't seem to find an option to
include the previous replies, so this will seem a little
discontinuous.. .

I did indeed check sysindexkeys (and the relevant INFORMATION_SCH EMA
view - can't remember which it is. I'm kind of naughty, I always head
straight for the tables)

Checking syscolumns the colids are, indeed, strictly increasing and
continuous. The database design is only a few months old, I'm the only
developer of it, and although it has evolved (checking history
here...), I've never dropped any columns from it.

One thing I've started wondering... There is another table in the
database with the following:

CREATE TABLE [CreditorService s].[Users] (
[UserID] [uniqueidentifie r] NOT NULL ,
[AbstractCredito rID] [uniqueidentifie r] NOT NULL ,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[UserID],
[AbstractCredito rID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_Users_Abstra ctCreditors] FOREIGN KEY
(
[AbstractCredito rID]
) REFERENCES [AbstractCredito rs] (
[AbstractCredito rID]
),
CONSTRAINT [FK_Users_Users] FOREIGN KEY
(
[UserID]
) REFERENCES [Users] (
[UserID]
)
) ON [PRIMARY]
GO

SQL Server shouldn't get confused with having two tables with the same
name, should it? They do have separate owners (the original being under
the dbo role).

[Damien]

Doh! The Primary Keys have the same name, and it's this that seems to
be confusing Query Analyzer. Hopefully, that was what was confusing
replication as well

[Erland Sommarskog]

Damien (Da************ *******@hotmail .com) writes:

Doh! The Primary Keys have the same name, and it's this that seems to
be confusing Query Analyzer. Hopefully, that was what was confusing
replication as well

Interesting. As far as QA is concerned I guess it is beyond hope, since
it replaced with a new tool in SQL 2005. The same applies to DMO which is
the real culprit.

Then again, when I come home I will test the case in SQL 2005, to see that
the problem does not reappear there.

If this a problem for replication as well, it would be interesting to see a
repro. The problem in this case is that I'm not into replicaiton myself, so
I would need some more instructions.

Since SQL 2005 introduces "real" schemas, these sort of collisions are
much more likely to appear in SQL 2005.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Damien]

Erland Sommarskog wrote:

Damien (Da************ *******@hotmail .com) writes:

Doh! The Primary Keys have the same name, and it's this that seems to be confusing Query Analyzer. Hopefully, that was what was confusing
replication as well
Interesting. As far as QA is concerned I guess it is beyond hope,

since it replaced with a new tool in SQL 2005. The same applies to DMO which is the real culprit.

Then again, when I come home I will test the case in SQL 2005, to see that the problem does not reappear there.

If this a problem for replication as well, it would be interesting to see a repro. The problem in this case is that I'm not into replicaiton myself, so I would need some more instructions.

Since SQL 2005 introduces "real" schemas, these sort of collisions are much more likely to appear in SQL 2005.

Well, we've tried and failed to get replication working - it's
confusing itself over the table owners (of which there are a large
number), so we're going to have to cobble together our own solution.
Yippee.

<gripe not-aimed-at="Erland">Is it too much to ask that Microsoft
deliver a replication solution that actually works in slightly unusual
situations? I wouldn't have thought having alternate owners would be
*that* uncommon.</gripe>

[Erland Sommarskog]

Damien (Da************ *******@hotmail .com) writes:

Well, we've tried and failed to get replication working - it's
confusing itself over the table owners (of which there are a large
number), so we're going to have to cobble together our own solution.
Yippee.
I have been trying to recreate the scripting error, using the table
scripts you have posted, but I have not been successful. That is, the
tables are scripted correctly. There could be three reasons for this:

1) I had to omit FK constraints to tables that I have don't have.
2) I am running SP4 Beta of SQL Server, and the bug has actually been
fixed.
3) My scripting options in QA are not the default ones.

The second seem to be the most likely to me. I would encourage you to get
access to the beta, and see if helps your problem. See
http://support.microsoft.com/kb/290211 for information about the SP4
beta.

If you get the problem with SP4 beta as well, I'd be interested to have a
complete script to build a database that demonstrates the problem. That
is, CREATE DATABASE, sp_addlogin, sp_adduser, CREATE TABLE etc. No, I
don't want your entire database. Just as much that the problem occurs.
In such case, I could bring it up with Microsoft. As I said, fixes to
DMO and Query Analyzer are unlikely, but replication bugs are more likely
to be fixed. And in any case, tests on SQL 2005 would be very interesting
to do.
<gripe not-aimed-at="Erland">Is it too much to ask that Microsoft
deliver a replication solution that actually works in slightly unusual
situations? I wouldn't have thought having alternate owners would be
*that* uncommon.</gripe>

Well, to be honest, I completly fail to see the point with having
objects owned by anyone than dbo in SQL 2000. Of course, you can have
dummy users for the schemas, but it's still a mess.

But in SQL 2005, where they have real schemas, it suddenly all make
sense to do things like this.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Damien]

Erland Sommarskog <es****@sommars kog.se> wrote in message news:<Xn******* *************** @127.0.0.1>...

Damien (Da************ *******@hotmail .com) writes:

Well, we've tried and failed to get replication working - it's
confusing itself over the table owners (of which there are a large
number), so we're going to have to cobble together our own solution.
Yippee.
I have been trying to recreate the scripting error, using the table
scripts you have posted, but I have not been successful. That is, the
tables are scripted correctly. There could be three reasons for this:

1) I had to omit FK constraints to tables that I have don't have.
2) I am running SP4 Beta of SQL Server, and the bug has actually been
fixed.
3) My scripting options in QA are not the default ones.

The second seem to be the most likely to me. I would encourage you to get
access to the beta, and see if helps your problem. See
http://support.microsoft.com/kb/290211 for information about the SP4
beta.

I'll attempt to get hold of it and see if I can reproduce again.

If you get the problem with SP4 beta as well, I'd be interested to have a
complete script to build a database that demonstrates the problem. That
is, CREATE DATABASE, sp_addlogin, sp_adduser, CREATE TABLE etc. No, I
don't want your entire database. Just as much that the problem occurs.
In such case, I could bring it up with Microsoft. As I said, fixes to
DMO and Query Analyzer are unlikely, but replication bugs are more likely
to be fixed. And in any case, tests on SQL 2005 would be very interesting
to do.
I'll try to get the service pack. If that clears it up, all well and
good. Otherwise, I'll try to strip my scripts down to the minimum that
exhibits the problem and post them back here.

<gripe not-aimed-at="Erland">Is it too much to ask that Microsoft
deliver a replication solution that actually works in slightly unusual
situations? I wouldn't have thought having alternate owners would be
*that* uncommon.</gripe>

Well, to be honest, I completly fail to see the point with having
objects owned by anyone than dbo in SQL 2000. Of course, you can have
dummy users for the schemas, but it's still a mess.

But in SQL 2005, where they have real schemas, it suddenly all make
sense to do things like this.

Dummy roles in order to get "schemas" is indeed what is being done
here. It's seemed to work nicely for all my needs up until now.
Looking forward to 2005, but don't think it'll get rolled out at work
for quite some time - we only just upgraded from 7 a couple of months
ago.

[Erland Sommarskog]

Damien (Da************ *******@hotmail .com) writes:

I'll try to get the service pack. If that clears it up, all well and
good. Otherwise, I'll try to strip my scripts down to the minimum that
exhibits the problem and post them back here.
It would great to know whether the service pack is the cure.
Dummy roles in order to get "schemas" is indeed what is being done
here. It's seemed to work nicely for all my needs up until now.
Looking forward to 2005, but don't think it'll get rolled out at work
for quite some time - we only just upgraded from 7 a couple of months
ago.

I know how it is. We were among those that never came around to upgrade
SQL 7. We went directly from 6.5 to SQL 2000.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Damien]

Erland Sommarskog <es****@sommars kog.se> wrote in message news:<Xn******* *************** @127.0.0.1>...

Damien (Da************ *******@hotmail .com) writes:

I'll try to get the service pack. If that clears it up, all well and
good. Otherwise, I'll try to strip my scripts down to the minimum that
exhibits the problem and post them back here.

It would great to know whether the service pack is the cure.

Dummy roles in order to get "schemas" is indeed what is being done
here. It's seemed to work nicely for all my needs up until now.
Looking forward to 2005, but don't think it'll get rolled out at work
for quite some time - we only just upgraded from 7 a couple of months
ago.

I know how it is. We were among those that never came around to upgrade
SQL 7. We went directly from 6.5 to SQL 2000.

Hi Erland,

The SP seems to fix the problem. I've tried both upgrading the server
with a duff database on it already, and also creating a fresh database
on an SP4 machine, and neither exhibits the bug. Makes me wonder
whether it was the MS03-031 hotfix that introduced the bug. Can anyone
reproduce this on a plain (version 760, I think, rather than 818 which
is an MS03-031 patched box) SP3 box?

create database PK_Problem
go
use PK_Problem
go
create table dbo.Table1 (
A uniqueidentifie r DEFAULT newid() not null
)
go
alter table dbo.Table1 add constraint PK_Table1 PRIMARY KEY (A)
go
create table dbo.Table2 (
B uniqueidentifie r DEFAULT newid() not null
)
go
alter table dbo.Table2 add constraint PK_Table2 PRIMARY KEY (B)
go
sp_addrole 'Boris','dbo'
go
create table Boris.Table1 (
A uniqueidentifie r not null,
B uniqueidentifie r not null
)
go
alter table Boris.Table1 add constraint FK_Table1_Table 1 FOREIGN KEY
(A) REFERENCES dbo.Table1 (A)
go
alter table Boris.Table1 add constraint FK_Table1_Table 2 FOREIGN KEY
(B) REFERENCES dbo.Table2 (B)
go
alter table Boris.Table1 add constraint PK_Table1 PRIMARY KEY (A,B)
go