cleaning wrong PK FK

Hello

my 2 tables in MS SQL 2000

Report :
Report_id (PK)
name

Product :
Product_id (PK)
Report_id (FK)
name

the Foreign Key and Primary Key have been added later (when the tables were allready full)
product has a few millions of lines and report a few 10.thousand

now I want to clean the 2 tables and remove all the lines which are not conected by PK > FK or FK > PK

i am trying :

DELETE FROM Product WHERE (Product.Report_id NOT IN (SELECT Report.Report_id FROM Report))

DELETE FROM Report WHERE (Report.Report_id NOT IN (SELECT Product.Report_id FROM Product))

but the database crash : time overflow !

how can I do it ?

thank youHere are two alternate methods:--Method #1: EXISTS
delete
from Product
where not exists (select * from Report where Report.Report_id = Product.Report_id)

--Method #2: LEFT OUTER JOIN
delete
from Product
left outer join Report on Product.Report_id = Report.Report_id
where Report.Report_id is null
In either method, make sure Report_id is indexed in both tables.|||thank you BlindMan

on the 2nd method i am getting :
Incorrect syntax near the keyword 'left'.|||Post your code.|||that one

delete
from Product
left outer join Report on Product.Report_id = Report.Report_id
where Report.Report_id is null

Incorrect syntax near the keyword 'left'

you said : in either method, make sure Report_id is indexed in both tables.

they are PK to FK but they are not indexed
how can I do it when the tables are allready full

ALTER TABLE create index ?

thank's a lot|||I'm assuming that you defined the PK and FK in your head, but haven't done anything with the database. A PK that doesn't exist using a PRIMARY KEY definition is only a good intention from my perspective. ;) I would suggest using something like:CREATE INDEX dropme01 ON Report (Report_Id)
CREATE INDEX dropme02 ON Product (Report_Id)

DELETE FROM Report
WHERE NOT EXISTS (SELECT *
FROM Product
WHERE Product.Report_Id = Report.Report_Id)

DELETE FROM Product
WHERE NOT EXISTS (SELECT *
FROM Report
WHERE Report.Report_id = Product.Report_Id)

DROP INDEX Report.dropme01
DROP INDEX Product.dropme02

ALTER TABLE Report
ADD CONSTRAINT XPKReport
PRIMARY KEY (Report_Id)

ALTER TABLE Product
ADD CONSTRAINT XPKProduct
PRIMARY KEY (Product_Id)

ALTER TABLE Product
ADD CONSTRAINT XFK01Report
FOREIGN KEY (Report_Id)
REFERENCES Report (Report_Id)-PatP|||Pat FK and PK are allready in the tables|||Pat FK and PK are allready in the tables
Are you having any problem now?|||with your first method it works very well
i was just wondering why it doesnt with the second method

but it works ...

thanks a lot|||Public kya Time pass karne aati hai kya idhar?|||Public kya Time pass karne aati hai kya idhar?

No Hindi man, I think English would be more appropiate to express anything that you post here.

He was telling ," Do the people come here only to pass time?"|||No Hindi man, I think English would be more appropiate to express anything that you post here.

He was telling ," Do the people come here only to pass time?"Lol - well I think we all know the answer to that.

Joydeep - you are becoming the SQL Server Forum Official Translator (Asian Languages Division) :)|||Lol - well I think we all know the answer to that.

Joydeep - you are becoming the SQL Server Forum Official Translator (Asian Languages Division) :)
........;)|||Sorry. There was a syntax error in my second example. This should work:
delete Product
from Product
left outer join Report on Product.Report_id = Report.Report_id
where Report.Report_id is null|||i try it thank you

Circular FK Constraints

Hi.
My system includes Clients and Contacts in a many-to-many relationship
handled in the usual way using a link table with a primary key of Client key
plus Contact key and foreign key constraints against Client and Contact
tables. Each Client may have a Main Contact, which is handled at present wit
h
a 'Main Contact' column in the Client table, which for referential integrity
has a foreign key constraint against the link table. These circular FK
constraints are a nuisance when it comes to deleting a client. I suppose I
could use a trigger to enforce referential integrity, but I don't want to -
the Client table already has a rather complicated trigger.
Can anyone think of a better way?
Thanks.
--
Peter HyssettPeter Hyssett wrote:
> Hi.
> My system includes Clients and Contacts in a many-to-many relationship
> handled in the usual way using a link table with a primary key of Client k
ey
> plus Contact key and foreign key constraints against Client and Contact
> tables. Each Client may have a Main Contact, which is handled at present w
ith
> a 'Main Contact' column in the Client table, which for referential integri
ty
> has a foreign key constraint against the link table. These circular FK
> constraints are a nuisance when it comes to deleting a client. I suppose I
> could use a trigger to enforce referential integrity, but I don't want to
-
> the Client table already has a rather complicated trigger.
> Can anyone think of a better way?
> Thanks.
Well, in my mind, your main contact fk would be better off referencing
the contact table instead of the link table.
When deleting a contact, you would obviously need to either set the
client.main contact column to a valid id from the contact table first,
or set it to null whichever is appropriate.
Then you should be fine.
JB|||Thanks. I'm afraid the FK is against the link table because the main contact
must be a contact already linked to the client, which an FK against the
Contact table would not enforce. Nowadays I do set the Main Contact column t
o
NULL before deleting (the link table rows being deleted first), but I would
prefer not to.
--
Peter Hyssett
"John B" wrote:

> Peter Hyssett wrote:
> Well, in my mind, your main contact fk would be better off referencing
> the contact table instead of the link table.
> When deleting a contact, you would obviously need to either set the
> client.main contact column to a valid id from the contact table first,
> or set it to null whichever is appropriate.
> Then you should be fine.
> JB
>|||Hello, Peter
As I understand this, your DDL is (or should be) something like this:
CREATE TABLE Contacts (
ContactID int PRIMARY KEY,
FirstName varchar(30) NOT NULL,
LastName varchar(20) NOT NULL,
--other columns...
UNIQUE (FirstName, LastName)
)
CREATE TABLE Clients (
ClientID int PRIMARY KEY,
ClientName varchar(50) NOT NULL UNIQUE,
--other columns...
MainContactID int NULL
)
CREATE TABLE ClientContacts (
ClientID int REFERENCES Clients ON DELETE CASCADE,
ContactID int REFERENCES Contacts ON DELETE CASCADE,
PRIMARY KEY (ClientID, ContactID)
)
ALTER TABLE Clients ADD CONSTRAINT TheFK
FOREIGN KEY (ClientID, MainContactID)
REFERENCES ClientContacts (ClientID, ContactID)
Assuming the following sample data:
SET NOCOUNT ON
INSERT INTO Clients (ClientID, ClientName)
VALUES (100, 'Big Company, Inc.')
INSERT INTO Contacts VALUES (1, 'John', 'Smith')
INSERT INTO Contacts VALUES (2, 'Mary', 'Smith')
INSERT INTO ClientContacts VALUES (100, 1)
INSERT INTO ClientContacts VALUES (100, 2)
UPDATE Clients SET MainContactID=1 WHERE ClientID=100
INSERT INTO Clients (ClientID, ClientName)
VALUES (200, 'Another Company, Ltd.')
INSERT INTO Contacts VALUES (3, 'John', 'Doe')
INSERT INTO Contacts VALUES (4, 'Jane', 'Doe')
INSERT INTO ClientContacts VALUES (200, 2)
INSERT INTO ClientContacts VALUES (200, 3)
INSERT INTO ClientContacts VALUES (200, 4)
UPDATE Clients SET MainContactID=3 WHERE ClientID=200
SET NOCOUNT OFF
Let's suppose you want to delete the client with the ClientID=100.
Using a simple "DELETE Clients WHERE ClientID=100" works (without any
error): the client is deleted and it's links with the contacts, too
(but the contacts themselves remain). If you want to also delete the
contacts (the ones that are not linked with any other client), you can
use the following trigger:
CREATE TRIGGER Clients_DeleteContacts ON Clients
INSTEAD OF DELETE
AS
IF @.@.ROWCOUNT>0 BEGIN
SET NOCOUNT ON
UPDATE Clients SET MainContactID=NULL
WHERE ClientID IN (SELECT ClientID FROM deleted)
DELETE Contacts WHERE ContactID IN (
SELECT x.ContactID FROM ClientContacts x
WHERE x.ClientID IN (SELECT ClientID FROM deleted)
AND NOT EXISTS (
SELECT * FROM ClientContacts y
WHERE x.ContactID=y.ContactID
AND y.ClientID NOT IN (SELECT ClientID FROM deleted)
)
)
DELETE Clients
WHERE ClientID IN (SELECT ClientID FROM deleted)
END
Razvan|||On Thu, 16 Jun 2005 16:19:05 -0700, Peter Hyssett wrote:

>Hi.
>My system includes Clients and Contacts in a many-to-many relationship
>handled in the usual way using a link table with a primary key of Client ke
y
>plus Contact key and foreign key constraints against Client and Contact
>tables. Each Client may have a Main Contact, which is handled at present wi
th
>a 'Main Contact' column in the Client table, which for referential integrit
y
>has a foreign key constraint against the link table. These circular FK
>constraints are a nuisance when it comes to deleting a client. I suppose I
>could use a trigger to enforce referential integrity, but I don't want to -
>the Client table already has a rather complicated trigger.
>Can anyone think of a better way?
>Thanks.
Hi Peter,
I'd consider something like this (stealing lots from Razvan Socol's
post)
CREATE TABLE Contacts (
ContactID int NOT NULL PRIMARY KEY,
FirstName varchar(30) NOT NULL,
LastName varchar(20) NOT NULL,
--other columns--
UNIQUE (FirstName, LastName)
)
CREATE TABLE Clients (
ClientID int NOT NULL PRIMARY KEY,
ClientName varchar(50) NOT NULL UNIQUE,
--other columns--
)
CREATE TABLE ClientContacts (
ClientID int NOT NULL REFERENCES Clients ON DELETE CASCADE,
ContactID int NOT NULL REFERENCES Contacts ON DELETE CASCADE,
Priority smallint NOT NULL CHECK Priority > 0,
PRIMARY KEY (ClientID, ContactID),
UNIQUE (ClientID, Priority)
)
CREATE VIEW PrimaryContact
AS
SELECT cc.ClientID, cc.ContactID
FROM (SELECT ClientID, MIN(Priority) AS MinPrio
FROM ClientContacts
GROUP BY ClientID) AS d
INNER JOIN ClientContacts AS cc
ON cc.ClientID = d.ClientID
AND cc.Priority = d.MinPrio
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Peter Hyssett wrote:
> Thanks. I'm afraid the FK is against the link table because the main conta
ct
> must be a contact already linked to the client,
Ah, that makes sense
> which an FK against the
> Contact table would not enforce. Nowadays I do set the Main Contact column
to
> NULL before deleting (the link table rows being deleted first), but I woul
d
> prefer not to.
Why would you prefer not to?
Cascade delete _might_ set it to null but Im not sure.
If you wished to keep the MainContactId, then it would be a referential
break as the actual contact would have been deleted.
You could do this by removing the FK constraint though.
JB|||Thanks, Razvan. The ON DELETE CASCADE construct is what I wanted - I just
hadn't come across it before.
Cheers,
Peter.
Peter Hyssett
"Razvan Socol" wrote:

> Hello, Peter
> As I understand this, your DDL is (or should be) something like this:
> CREATE TABLE Contacts (
> ContactID int PRIMARY KEY,
> FirstName varchar(30) NOT NULL,
> LastName varchar(20) NOT NULL,
> --other columns...
> UNIQUE (FirstName, LastName)
> )
> CREATE TABLE Clients (
> ClientID int PRIMARY KEY,
> ClientName varchar(50) NOT NULL UNIQUE,
> --other columns...
> MainContactID int NULL
> )
> CREATE TABLE ClientContacts (
> ClientID int REFERENCES Clients ON DELETE CASCADE,
> ContactID int REFERENCES Contacts ON DELETE CASCADE,
> PRIMARY KEY (ClientID, ContactID)
> )
> ALTER TABLE Clients ADD CONSTRAINT TheFK
> FOREIGN KEY (ClientID, MainContactID)
> REFERENCES ClientContacts (ClientID, ContactID)
>
> Assuming the following sample data:
> SET NOCOUNT ON
> INSERT INTO Clients (ClientID, ClientName)
> VALUES (100, 'Big Company, Inc.')
> INSERT INTO Contacts VALUES (1, 'John', 'Smith')
> INSERT INTO Contacts VALUES (2, 'Mary', 'Smith')
> INSERT INTO ClientContacts VALUES (100, 1)
> INSERT INTO ClientContacts VALUES (100, 2)
> UPDATE Clients SET MainContactID=1 WHERE ClientID=100
>
> INSERT INTO Clients (ClientID, ClientName)
> VALUES (200, 'Another Company, Ltd.')
> INSERT INTO Contacts VALUES (3, 'John', 'Doe')
> INSERT INTO Contacts VALUES (4, 'Jane', 'Doe')
> INSERT INTO ClientContacts VALUES (200, 2)
> INSERT INTO ClientContacts VALUES (200, 3)
> INSERT INTO ClientContacts VALUES (200, 4)
> UPDATE Clients SET MainContactID=3 WHERE ClientID=200
> SET NOCOUNT OFF
>
> Let's suppose you want to delete the client with the ClientID=100.
> Using a simple "DELETE Clients WHERE ClientID=100" works (without any
> error): the client is deleted and it's links with the contacts, too
> (but the contacts themselves remain). If you want to also delete the
> contacts (the ones that are not linked with any other client), you can
> use the following trigger:
> CREATE TRIGGER Clients_DeleteContacts ON Clients
> INSTEAD OF DELETE
> AS
> IF @.@.ROWCOUNT>0 BEGIN
> SET NOCOUNT ON
> UPDATE Clients SET MainContactID=NULL
> WHERE ClientID IN (SELECT ClientID FROM deleted)
> DELETE Contacts WHERE ContactID IN (
> SELECT x.ContactID FROM ClientContacts x
> WHERE x.ClientID IN (SELECT ClientID FROM deleted)
> AND NOT EXISTS (
> SELECT * FROM ClientContacts y
> WHERE x.ContactID=y.ContactID
> AND y.ClientID NOT IN (SELECT ClientID FROM deleted)
> )
> )
> DELETE Clients
> WHERE ClientID IN (SELECT ClientID FROM deleted)
> END
> Razvan
>

Circular FK Constraints

Hi.
My system includes Clients and Contacts in a many-to-many relationship
handled in the usual way using a link table with a primary key of Client key
plus Contact key and foreign key constraints against Client and Contact
tables. Each Client may have a Main Contact, which is handled at present with
a 'Main Contact' column in the Client table, which for referential integrity
has a foreign key constraint against the link table. These circular FK
constraints are a nuisance when it comes to deleting a client. I suppose I
could use a trigger to enforce referential integrity, but I don't want to -
the Client table already has a rather complicated trigger.
Can anyone think of a better way?
Thanks.
--
Peter HyssettPeter Hyssett wrote:
> Hi.
> My system includes Clients and Contacts in a many-to-many relationship
> handled in the usual way using a link table with a primary key of Client key
> plus Contact key and foreign key constraints against Client and Contact
> tables. Each Client may have a Main Contact, which is handled at present with
> a 'Main Contact' column in the Client table, which for referential integrity
> has a foreign key constraint against the link table. These circular FK
> constraints are a nuisance when it comes to deleting a client. I suppose I
> could use a trigger to enforce referential integrity, but I don't want to -
> the Client table already has a rather complicated trigger.
> Can anyone think of a better way?
> Thanks.
Well, in my mind, your main contact fk would be better off referencing
the contact table instead of the link table.
When deleting a contact, you would obviously need to either set the
client.main contact column to a valid id from the contact table first,
or set it to null whichever is appropriate.
Then you should be fine.
JB|||Thanks. I'm afraid the FK is against the link table because the main contact
must be a contact already linked to the client, which an FK against the
Contact table would not enforce. Nowadays I do set the Main Contact column to
NULL before deleting (the link table rows being deleted first), but I would
prefer not to.
--
Peter Hyssett
"John B" wrote:
> Peter Hyssett wrote:
> > Hi.
> > My system includes Clients and Contacts in a many-to-many relationship
> > handled in the usual way using a link table with a primary key of Client key
> > plus Contact key and foreign key constraints against Client and Contact
> > tables. Each Client may have a Main Contact, which is handled at present with
> > a 'Main Contact' column in the Client table, which for referential integrity
> > has a foreign key constraint against the link table. These circular FK
> > constraints are a nuisance when it comes to deleting a client. I suppose I
> > could use a trigger to enforce referential integrity, but I don't want to -
> > the Client table already has a rather complicated trigger.
> >
> > Can anyone think of a better way?
> >
> > Thanks.
> Well, in my mind, your main contact fk would be better off referencing
> the contact table instead of the link table.
> When deleting a contact, you would obviously need to either set the
> client.main contact column to a valid id from the contact table first,
> or set it to null whichever is appropriate.
> Then you should be fine.
> JB
>|||Hello, Peter
As I understand this, your DDL is (or should be) something like this:
CREATE TABLE Contacts (
ContactID int PRIMARY KEY,
FirstName varchar(30) NOT NULL,
LastName varchar(20) NOT NULL,
--other columns...
UNIQUE (FirstName, LastName)
)
CREATE TABLE Clients (
ClientID int PRIMARY KEY,
ClientName varchar(50) NOT NULL UNIQUE,
--other columns...
MainContactID int NULL
)
CREATE TABLE ClientContacts (
ClientID int REFERENCES Clients ON DELETE CASCADE,
ContactID int REFERENCES Contacts ON DELETE CASCADE,
PRIMARY KEY (ClientID, ContactID)
)
ALTER TABLE Clients ADD CONSTRAINT TheFK
FOREIGN KEY (ClientID, MainContactID)
REFERENCES ClientContacts (ClientID, ContactID)
Assuming the following sample data:
SET NOCOUNT ON
INSERT INTO Clients (ClientID, ClientName)
VALUES (100, 'Big Company, Inc.')
INSERT INTO Contacts VALUES (1, 'John', 'Smith')
INSERT INTO Contacts VALUES (2, 'Mary', 'Smith')
INSERT INTO ClientContacts VALUES (100, 1)
INSERT INTO ClientContacts VALUES (100, 2)
UPDATE Clients SET MainContactID=1 WHERE ClientID=100
INSERT INTO Clients (ClientID, ClientName)
VALUES (200, 'Another Company, Ltd.')
INSERT INTO Contacts VALUES (3, 'John', 'Doe')
INSERT INTO Contacts VALUES (4, 'Jane', 'Doe')
INSERT INTO ClientContacts VALUES (200, 2)
INSERT INTO ClientContacts VALUES (200, 3)
INSERT INTO ClientContacts VALUES (200, 4)
UPDATE Clients SET MainContactID=3 WHERE ClientID=200
SET NOCOUNT OFF
Let's suppose you want to delete the client with the ClientID=100.
Using a simple "DELETE Clients WHERE ClientID=100" works (without any
error): the client is deleted and it's links with the contacts, too
(but the contacts themselves remain). If you want to also delete the
contacts (the ones that are not linked with any other client), you can
use the following trigger:
CREATE TRIGGER Clients_DeleteContacts ON Clients
INSTEAD OF DELETE
AS
IF @.@.ROWCOUNT>0 BEGIN
SET NOCOUNT ON
UPDATE Clients SET MainContactID=NULL
WHERE ClientID IN (SELECT ClientID FROM deleted)
DELETE Contacts WHERE ContactID IN (
SELECT x.ContactID FROM ClientContacts x
WHERE x.ClientID IN (SELECT ClientID FROM deleted)
AND NOT EXISTS (
SELECT * FROM ClientContacts y
WHERE x.ContactID=y.ContactID
AND y.ClientID NOT IN (SELECT ClientID FROM deleted)
)
)
DELETE Clients
WHERE ClientID IN (SELECT ClientID FROM deleted)
END
Razvan|||On Thu, 16 Jun 2005 16:19:05 -0700, Peter Hyssett wrote:
>Hi.
>My system includes Clients and Contacts in a many-to-many relationship
>handled in the usual way using a link table with a primary key of Client key
>plus Contact key and foreign key constraints against Client and Contact
>tables. Each Client may have a Main Contact, which is handled at present with
>a 'Main Contact' column in the Client table, which for referential integrity
>has a foreign key constraint against the link table. These circular FK
>constraints are a nuisance when it comes to deleting a client. I suppose I
>could use a trigger to enforce referential integrity, but I don't want to -
>the Client table already has a rather complicated trigger.
>Can anyone think of a better way?
>Thanks.
Hi Peter,
I'd consider something like this (stealing lots from Razvan Socol's
post)
CREATE TABLE Contacts (
ContactID int NOT NULL PRIMARY KEY,
FirstName varchar(30) NOT NULL,
LastName varchar(20) NOT NULL,
--other columns--
UNIQUE (FirstName, LastName)
)
CREATE TABLE Clients (
ClientID int NOT NULL PRIMARY KEY,
ClientName varchar(50) NOT NULL UNIQUE,
--other columns--
)
CREATE TABLE ClientContacts (
ClientID int NOT NULL REFERENCES Clients ON DELETE CASCADE,
ContactID int NOT NULL REFERENCES Contacts ON DELETE CASCADE,
Priority smallint NOT NULL CHECK Priority > 0,
PRIMARY KEY (ClientID, ContactID),
UNIQUE (ClientID, Priority)
)
CREATE VIEW PrimaryContact
AS
SELECT cc.ClientID, cc.ContactID
FROM (SELECT ClientID, MIN(Priority) AS MinPrio
FROM ClientContacts
GROUP BY ClientID) AS d
INNER JOIN ClientContacts AS cc
ON cc.ClientID = d.ClientID
AND cc.Priority = d.MinPrio
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Peter Hyssett wrote:
> Thanks. I'm afraid the FK is against the link table because the main contact
> must be a contact already linked to the client,
Ah, that makes sense
> which an FK against the
> Contact table would not enforce. Nowadays I do set the Main Contact column to
> NULL before deleting (the link table rows being deleted first), but I would
> prefer not to.
Why would you prefer not to?
Cascade delete _might_ set it to null but Im not sure.
If you wished to keep the MainContactId, then it would be a referential
break as the actual contact would have been deleted.
You could do this by removing the FK constraint though.
JB|||Thanks, Razvan. The ON DELETE CASCADE construct is what I wanted - I just
hadn't come across it before.
Cheers,
Peter.
--
Peter Hyssett
"Razvan Socol" wrote:
> Hello, Peter
> As I understand this, your DDL is (or should be) something like this:
> CREATE TABLE Contacts (
> ContactID int PRIMARY KEY,
> FirstName varchar(30) NOT NULL,
> LastName varchar(20) NOT NULL,
> --other columns...
> UNIQUE (FirstName, LastName)
> )
> CREATE TABLE Clients (
> ClientID int PRIMARY KEY,
> ClientName varchar(50) NOT NULL UNIQUE,
> --other columns...
> MainContactID int NULL
> )
> CREATE TABLE ClientContacts (
> ClientID int REFERENCES Clients ON DELETE CASCADE,
> ContactID int REFERENCES Contacts ON DELETE CASCADE,
> PRIMARY KEY (ClientID, ContactID)
> )
> ALTER TABLE Clients ADD CONSTRAINT TheFK
> FOREIGN KEY (ClientID, MainContactID)
> REFERENCES ClientContacts (ClientID, ContactID)
>
> Assuming the following sample data:
> SET NOCOUNT ON
> INSERT INTO Clients (ClientID, ClientName)
> VALUES (100, 'Big Company, Inc.')
> INSERT INTO Contacts VALUES (1, 'John', 'Smith')
> INSERT INTO Contacts VALUES (2, 'Mary', 'Smith')
> INSERT INTO ClientContacts VALUES (100, 1)
> INSERT INTO ClientContacts VALUES (100, 2)
> UPDATE Clients SET MainContactID=1 WHERE ClientID=100
>
> INSERT INTO Clients (ClientID, ClientName)
> VALUES (200, 'Another Company, Ltd.')
> INSERT INTO Contacts VALUES (3, 'John', 'Doe')
> INSERT INTO Contacts VALUES (4, 'Jane', 'Doe')
> INSERT INTO ClientContacts VALUES (200, 2)
> INSERT INTO ClientContacts VALUES (200, 3)
> INSERT INTO ClientContacts VALUES (200, 4)
> UPDATE Clients SET MainContactID=3 WHERE ClientID=200
> SET NOCOUNT OFF
>
> Let's suppose you want to delete the client with the ClientID=100.
> Using a simple "DELETE Clients WHERE ClientID=100" works (without any
> error): the client is deleted and it's links with the contacts, too
> (but the contacts themselves remain). If you want to also delete the
> contacts (the ones that are not linked with any other client), you can
> use the following trigger:
> CREATE TRIGGER Clients_DeleteContacts ON Clients
> INSTEAD OF DELETE
> AS
> IF @.@.ROWCOUNT>0 BEGIN
> SET NOCOUNT ON
> UPDATE Clients SET MainContactID=NULL
> WHERE ClientID IN (SELECT ClientID FROM deleted)
> DELETE Contacts WHERE ContactID IN (
> SELECT x.ContactID FROM ClientContacts x
> WHERE x.ClientID IN (SELECT ClientID FROM deleted)
> AND NOT EXISTS (
> SELECT * FROM ClientContacts y
> WHERE x.ContactID=y.ContactID
> AND y.ClientID NOT IN (SELECT ClientID FROM deleted)
> )
> )
> DELETE Clients
> WHERE ClientID IN (SELECT ClientID FROM deleted)
> END
> Razvan
>

Circular FK Constraints

Hi.
My system includes Clients and Contacts in a many-to-many relationship
handled in the usual way using a link table with a primary key of Client key
plus Contact key and foreign key constraints against Client and Contact
tables. Each Client may have a Main Contact, which is handled at present with
a 'Main Contact' column in the Client table, which for referential integrity
has a foreign key constraint against the link table. These circular FK
constraints are a nuisance when it comes to deleting a client. I suppose I
could use a trigger to enforce referential integrity, but I don't want to -
the Client table already has a rather complicated trigger.
Can anyone think of a better way?
Thanks.
Peter Hyssett
Peter Hyssett wrote:
> Hi.
> My system includes Clients and Contacts in a many-to-many relationship
> handled in the usual way using a link table with a primary key of Client key
> plus Contact key and foreign key constraints against Client and Contact
> tables. Each Client may have a Main Contact, which is handled at present with
> a 'Main Contact' column in the Client table, which for referential integrity
> has a foreign key constraint against the link table. These circular FK
> constraints are a nuisance when it comes to deleting a client. I suppose I
> could use a trigger to enforce referential integrity, but I don't want to -
> the Client table already has a rather complicated trigger.
> Can anyone think of a better way?
> Thanks.
Well, in my mind, your main contact fk would be better off referencing
the contact table instead of the link table.
When deleting a contact, you would obviously need to either set the
client.main contact column to a valid id from the contact table first,
or set it to null whichever is appropriate.
Then you should be fine.
JB
|||Thanks. I'm afraid the FK is against the link table because the main contact
must be a contact already linked to the client, which an FK against the
Contact table would not enforce. Nowadays I do set the Main Contact column to
NULL before deleting (the link table rows being deleted first), but I would
prefer not to.
Peter Hyssett
"John B" wrote:

> Peter Hyssett wrote:
> Well, in my mind, your main contact fk would be better off referencing
> the contact table instead of the link table.
> When deleting a contact, you would obviously need to either set the
> client.main contact column to a valid id from the contact table first,
> or set it to null whichever is appropriate.
> Then you should be fine.
> JB
>
|||Hello, Peter
As I understand this, your DDL is (or should be) something like this:
CREATE TABLE Contacts (
ContactID int PRIMARY KEY,
FirstName varchar(30) NOT NULL,
LastName varchar(20) NOT NULL,
--other columns...
UNIQUE (FirstName, LastName)
)
CREATE TABLE Clients (
ClientID int PRIMARY KEY,
ClientName varchar(50) NOT NULL UNIQUE,
--other columns...
MainContactID int NULL
)
CREATE TABLE ClientContacts (
ClientID int REFERENCES Clients ON DELETE CASCADE,
ContactID int REFERENCES Contacts ON DELETE CASCADE,
PRIMARY KEY (ClientID, ContactID)
)
ALTER TABLE Clients ADD CONSTRAINT TheFK
FOREIGN KEY (ClientID, MainContactID)
REFERENCES ClientContacts (ClientID, ContactID)
Assuming the following sample data:
SET NOCOUNT ON
INSERT INTO Clients (ClientID, ClientName)
VALUES (100, 'Big Company, Inc.')
INSERT INTO Contacts VALUES (1, 'John', 'Smith')
INSERT INTO Contacts VALUES (2, 'Mary', 'Smith')
INSERT INTO ClientContacts VALUES (100, 1)
INSERT INTO ClientContacts VALUES (100, 2)
UPDATE Clients SET MainContactID=1 WHERE ClientID=100
INSERT INTO Clients (ClientID, ClientName)
VALUES (200, 'Another Company, Ltd.')
INSERT INTO Contacts VALUES (3, 'John', 'Doe')
INSERT INTO Contacts VALUES (4, 'Jane', 'Doe')
INSERT INTO ClientContacts VALUES (200, 2)
INSERT INTO ClientContacts VALUES (200, 3)
INSERT INTO ClientContacts VALUES (200, 4)
UPDATE Clients SET MainContactID=3 WHERE ClientID=200
SET NOCOUNT OFF
Let's suppose you want to delete the client with the ClientID=100.
Using a simple "DELETE Clients WHERE ClientID=100" works (without any
error): the client is deleted and it's links with the contacts, too
(but the contacts themselves remain). If you want to also delete the
contacts (the ones that are not linked with any other client), you can
use the following trigger:
CREATE TRIGGER Clients_DeleteContacts ON Clients
INSTEAD OF DELETE
AS
IF @.@.ROWCOUNT>0 BEGIN
SET NOCOUNT ON
UPDATE Clients SET MainContactID=NULL
WHERE ClientID IN (SELECT ClientID FROM deleted)
DELETE Contacts WHERE ContactID IN (
SELECT x.ContactID FROM ClientContacts x
WHERE x.ClientID IN (SELECT ClientID FROM deleted)
AND NOT EXISTS (
SELECT * FROM ClientContacts y
WHERE x.ContactID=y.ContactID
AND y.ClientID NOT IN (SELECT ClientID FROM deleted)
)
)
DELETE Clients
WHERE ClientID IN (SELECT ClientID FROM deleted)
END
Razvan
|||On Thu, 16 Jun 2005 16:19:05 -0700, Peter Hyssett wrote:

>Hi.
>My system includes Clients and Contacts in a many-to-many relationship
>handled in the usual way using a link table with a primary key of Client key
>plus Contact key and foreign key constraints against Client and Contact
>tables. Each Client may have a Main Contact, which is handled at present with
>a 'Main Contact' column in the Client table, which for referential integrity
>has a foreign key constraint against the link table. These circular FK
>constraints are a nuisance when it comes to deleting a client. I suppose I
>could use a trigger to enforce referential integrity, but I don't want to -
>the Client table already has a rather complicated trigger.
>Can anyone think of a better way?
>Thanks.
Hi Peter,
I'd consider something like this (stealing lots from Razvan Socol's
post)
CREATE TABLE Contacts (
ContactID int NOT NULL PRIMARY KEY,
FirstName varchar(30) NOT NULL,
LastName varchar(20) NOT NULL,
--other columns--
UNIQUE (FirstName, LastName)
)
CREATE TABLE Clients (
ClientID int NOT NULL PRIMARY KEY,
ClientName varchar(50) NOT NULL UNIQUE,
--other columns--
)
CREATE TABLE ClientContacts (
ClientID int NOT NULL REFERENCES Clients ON DELETE CASCADE,
ContactID int NOT NULL REFERENCES Contacts ON DELETE CASCADE,
Priority smallint NOT NULL CHECK Priority > 0,
PRIMARY KEY (ClientID, ContactID),
UNIQUE (ClientID, Priority)
)
CREATE VIEW PrimaryContact
AS
SELECT cc.ClientID, cc.ContactID
FROM (SELECT ClientID, MIN(Priority) AS MinPrio
FROM ClientContacts
GROUP BY ClientID) AS d
INNER JOIN ClientContacts AS cc
ON cc.ClientID = d.ClientID
AND cc.Priority = d.MinPrio
go
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Peter Hyssett wrote:
> Thanks. I'm afraid the FK is against the link table because the main contact
> must be a contact already linked to the client,
Ah, that makes sense
> which an FK against the
> Contact table would not enforce. Nowadays I do set the Main Contact column to
> NULL before deleting (the link table rows being deleted first), but I would
> prefer not to.
Why would you prefer not to?
Cascade delete _might_ set it to null but Im not sure.
If you wished to keep the MainContactId, then it would be a referential
break as the actual contact would have been deleted.
You could do this by removing the FK constraint though.
JB
|||Thanks, Razvan. The ON DELETE CASCADE construct is what I wanted - I just
hadn't come across it before.
Cheers,
Peter.
Peter Hyssett
"Razvan Socol" wrote:

> Hello, Peter
> As I understand this, your DDL is (or should be) something like this:
> CREATE TABLE Contacts (
> ContactID int PRIMARY KEY,
> FirstName varchar(30) NOT NULL,
> LastName varchar(20) NOT NULL,
> --other columns...
> UNIQUE (FirstName, LastName)
> )
> CREATE TABLE Clients (
> ClientID int PRIMARY KEY,
> ClientName varchar(50) NOT NULL UNIQUE,
> --other columns...
> MainContactID int NULL
> )
> CREATE TABLE ClientContacts (
> ClientID int REFERENCES Clients ON DELETE CASCADE,
> ContactID int REFERENCES Contacts ON DELETE CASCADE,
> PRIMARY KEY (ClientID, ContactID)
> )
> ALTER TABLE Clients ADD CONSTRAINT TheFK
> FOREIGN KEY (ClientID, MainContactID)
> REFERENCES ClientContacts (ClientID, ContactID)
>
> Assuming the following sample data:
> SET NOCOUNT ON
> INSERT INTO Clients (ClientID, ClientName)
> VALUES (100, 'Big Company, Inc.')
> INSERT INTO Contacts VALUES (1, 'John', 'Smith')
> INSERT INTO Contacts VALUES (2, 'Mary', 'Smith')
> INSERT INTO ClientContacts VALUES (100, 1)
> INSERT INTO ClientContacts VALUES (100, 2)
> UPDATE Clients SET MainContactID=1 WHERE ClientID=100
>
> INSERT INTO Clients (ClientID, ClientName)
> VALUES (200, 'Another Company, Ltd.')
> INSERT INTO Contacts VALUES (3, 'John', 'Doe')
> INSERT INTO Contacts VALUES (4, 'Jane', 'Doe')
> INSERT INTO ClientContacts VALUES (200, 2)
> INSERT INTO ClientContacts VALUES (200, 3)
> INSERT INTO ClientContacts VALUES (200, 4)
> UPDATE Clients SET MainContactID=3 WHERE ClientID=200
> SET NOCOUNT OFF
>
> Let's suppose you want to delete the client with the ClientID=100.
> Using a simple "DELETE Clients WHERE ClientID=100" works (without any
> error): the client is deleted and it's links with the contacts, too
> (but the contacts themselves remain). If you want to also delete the
> contacts (the ones that are not linked with any other client), you can
> use the following trigger:
> CREATE TRIGGER Clients_DeleteContacts ON Clients
> INSTEAD OF DELETE
> AS
> IF @.@.ROWCOUNT>0 BEGIN
> SET NOCOUNT ON
> UPDATE Clients SET MainContactID=NULL
> WHERE ClientID IN (SELECT ClientID FROM deleted)
> DELETE Contacts WHERE ContactID IN (
> SELECT x.ContactID FROM ClientContacts x
> WHERE x.ClientID IN (SELECT ClientID FROM deleted)
> AND NOT EXISTS (
> SELECT * FROM ClientContacts y
> WHERE x.ContactID=y.ContactID
> AND y.ClientID NOT IN (SELECT ClientID FROM deleted)
> )
> )
> DELETE Clients
> WHERE ClientID IN (SELECT ClientID FROM deleted)
> END
> Razvan
>

Chosen datatype for Primary key field and performance questions?

Hi there,

I have been hired for a couple of weeks to investigate the performance of a sql server 2000 system.

One of the things that strikes me is that all the Primary key (identity field) fileds uses an decimal(18,0) as it's datatype.

An decimal with a precision of 18,0 takes 9 bytes for each column, while an int takes only 4 bytes and and bigint 8 bytes.

Many tables aren't that big, so the values will fit in an int datatype.

1. Is iot a good option to change the decimals columns to an int column ?

2. Many of these columns are indexed by a clustered index. Can the decimal datatype be a performance issue ?

3. sometimes they have deadlocks due page splits. Can this by reduced by changing the data types, while more data fit's into an page?

Thanks in advance,

Greetz,

Patrick de Jong

To me decimal (18,0) does not make sense at all... if at all u need to store larger number u could have gone for BigInt. Ofcourse the index size increases. To reduce the page split the change of datatype may not be sufficient. you many need to re-look your fillfactor for the index.

Madhu

|||

Using int for an indentity column is the most common solution, but just be aware of the roughly 2.1 billion upper limit for that data type. If 2.1 billion is not large enough, most people use bigint.

Changing your fillfactor can help minimize page splits, at the cost of making your index larger. Your deadlocks are probably not caused by page splits. Its more likely that they are caused by lack of, or improper indexes. You might try running this query to see if you are seeing any blocking:

-- Detect blocking

SELECT blocked_query.session_id AS blocked_session_id,

blocking_query.session_id AS blocking_session_id,

sql_text.text AS blocked_text,

sql_btext.text AS blocking_text,

waits.wait_type AS blocking_resource

FROM sys.dm_exec_requests AS blocked_query

INNER JOIN sys.dm_exec_requests AS blocking_query

ON blocked_query.blocking_session_id = blocking_query.session_id

CROSS APPLY

(SELECT *

FROM sys.dm_exec_sql_text(blocking_query.sql_handle)

) sql_btext

CROSS APPLY

(SELECT *

FROM sys.dm_exec_sql_text(blocked_query.sql_handle)

) sql_text

INNER JOIN sys.dm_os_waiting_tasks AS waits

ON waits.session_id = blocking_query.session_id

Choosing values for primary keys

Hello group:

I've done alot of reading on this subject somewhat and have found that
many people have many different opinions on this subject. My question
centers mainly around using a lookup table to enable users to select a
pre-defined list of values.

I have developed a practice myself of avoiding AutoNumber type data
fields for primary keys where the primary key will be related to a
child table. Nevertheless, what do most users do with lookup tables?
My thoughts are to create a small key value for each value in the
lookup table. For example:

I might have a Carriers table which shows a list of carriers that I
might ship an order by. One of the entries may be 'Air Freight -
Overnight', or 'Air Freight - 2nd Day Air'. I've seen a few examples
where the primary key field for each entry like these would be
autonumber, or at least, a numeric value. What I like to do is create
my own key, like for 'Air Freight - Overnight', I might use 'AFO' for
the key, and for 'Air Freight - 2nd Day Air', I might use 'AF2'. Any
thoughts on this? Mine are that even tho the users may never see this
value - I, as the developer will see it and I tend to prefer a key
value based on real data that means something other than an
auto-incremented number. In referencing the well-known Northwind.mdb
database, I noticed their Categories table used a number field value,
like 1, 2, 3...etc, but their customers table used values like
'ALFKI' to represent their key values.

What are some other thoughts out there? I'm working with Access
currently, but this project is about to move to SQL Server.

JamesI can't speak from much experience (only actually created a few small
tables...) but in large tables, you'll save space using a numeric value
I think. A 32 bit value will give you LOTS of unique numbers for rows.
In your example, 3 ascii characters is still shorter (24 bits.)
However if you end up using lots of long-ish keys, you'll eat up lots of
extra bits.

However, you can see that I use lots of letters to say very little, so
who am I to comment on space?! :)

Just my $.02...trying not to lurk so much!

-gabe

James wrote:
> Hello group:
> I've done alot of reading on this subject somewhat and have found that
> many people have many different opinions on this subject. My question
> centers mainly around using a lookup table to enable users to select a
> pre-defined list of values.
> I have developed a practice myself of avoiding AutoNumber type data
> fields for primary keys where the primary key will be related to a
> child table. Nevertheless, what do most users do with lookup tables?
> My thoughts are to create a small key value for each value in the
> lookup table. For example:
> I might have a Carriers table which shows a list of carriers that I
> might ship an order by. One of the entries may be 'Air Freight -
> Overnight', or 'Air Freight - 2nd Day Air'. I've seen a few examples
> where the primary key field for each entry like these would be
> autonumber, or at least, a numeric value. What I like to do is create
> my own key, like for 'Air Freight - Overnight', I might use 'AFO' for
> the key, and for 'Air Freight - 2nd Day Air', I might use 'AF2'. Any
> thoughts on this? Mine are that even tho the users may never see this
> value - I, as the developer will see it and I tend to prefer a key
> value based on real data that means something other than an
> auto-incremented number. In referencing the well-known Northwind.mdb
> database, I noticed their Categories table used a number field value,
> like 1, 2, 3...etc, but their customers table used values like
> 'ALFKI' to represent their key values.
> What are some other thoughts out there? I'm working with Access
> currently, but this project is about to move to SQL Server.
>
> James|||[posted and mailed, please reply in news]

James (dragonzfang@.hotmail.com) writes:
> I might have a Carriers table which shows a list of carriers that I
> might ship an order by. One of the entries may be 'Air Freight -
> Overnight', or 'Air Freight - 2nd Day Air'. I've seen a few examples
> where the primary key field for each entry like these would be
> autonumber, or at least, a numeric value. What I like to do is create
> my own key, like for 'Air Freight - Overnight', I might use 'AFO' for
> the key, and for 'Air Freight - 2nd Day Air', I might use 'AF2'. Any
> thoughts on this? Mine are that even tho the users may never see this
> value - I, as the developer will see it and I tend to prefer a key
> value based on real data that means something other than an
> auto-incremented number. In referencing the well-known Northwind.mdb
> database, I noticed their Categories table used a number field value,
> like 1, 2, 3...etc, but their customers table used values like
> 'ALFKI' to represent their key values.

In the system I work, we use both mnemonic codes and numeric keys
(which rarely are IDENTITY values, but we generate them ourselves).
But we do not pick them at random.

Basically, if the table is pre-loaded, that is we define the data in
the table, the key is a good. This is because we may have to refer to
the key value in our SQL code (or client code), and using numeric values
may easily cause errors.

On the other hand, if the data in the table is user-entered, the key is
numeric. Because who would generate the codes in this case? There are a
few tables with user-entered data where the key is actually a code,
but this is when there is a natural code to pick. Prime examples are
countries and currencies.

(There are also pre-loaded tables with numeric keys. But I didn't
design them. Or they were accidents. :-)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||James Lankford (dragonzfang@.hotmail.com) writes:
> In my Carriers table example, this is mainly just a lookup table, values
> are not likely to change often. If a new code needs to be defined, then
> the administrator can simply create his/her own unique key for the new
> entry.

We usually have a GUI for this sort of thing, but as you say, a lot this
data is highly static once it is in place.

> In the case of header/detail, parent to child table examples, I can see
> where having an autonumber generated key value is very beneficial. The
> two tables would still be linked via an invoice number, for example -
> but yet the autonumber key ID would serve as the unique identifer for
> the row. If the table becomes corrupted and needs to be rebuilt, or
> exported to another table, then it doesn't matter if the ID #'s change -
> nothing else is really "depending" upon it, and it still serves to
> uniquely identify that row.

For this kind of example, I prefer to have (InvoiceNo, RowNo) as the
key for the child table.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

Choosing correct high availability setup

I have a client that wants me to start hosting their SQL and IIS for them.
They want to have the ability to have a "hot spare" in case the primary SQL
server fails. I have read and read until my eyes hurt about clustering and
mirroring and I am getting more confused on how to proceed.
My first question is, should I even be using IIS on the SQL box at all?
They have a web interface that gets it data from SQL.
My second question is, if I host their web site on a separate IIS box is
there any reason that I shouldn't go with database mirroring instead of any
other option?
Thanks for any help you can provide.
Marty
Never do both on the same box, read this -
http://msmvps.com/blogs/clusterhelp/archive/2006/02/17/84035.aspx.
Server Clustering with make the entire node HA, SQL mirroring will make that
database HA. Which does your customer require?
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Marty Shifflett" <MartyShifflett@.discussions.microsoft.com> wrote in
message news:F03EC295-F9C8-4602-B787-BFBCC5841E48@.microsoft.com...
>I have a client that wants me to start hosting their SQL and IIS for them.
> They want to have the ability to have a "hot spare" in case the primary
> SQL
> server fails. I have read and read until my eyes hurt about clustering
> and
> mirroring and I am getting more confused on how to proceed.
> My first question is, should I even be using IIS on the SQL box at all?
> They have a web interface that gets it data from SQL.
> My second question is, if I host their web site on a separate IIS box is
> there any reason that I shouldn't go with database mirroring instead of
> any
> other option?
> Thanks for any help you can provide.
> Marty
|||Well that is what I had always thought, but you see more and more people
consolidating these functions.
Basically there will be an Access database that arrives via FTP at the web
server, the SQL server will pick it up from a shared drive and import it.
That will happen about every 15 minutes or so. The same web server will host
a site that the client can access to see real time data that it pulls from
the SQL server. The only snag is that if the primary SQL server goes down
for some reason, IIS will not know that it has to get that data from the
backup server if I am using only mirroring.
"Rodney R. Fournier [MVP]" wrote:

> Never do both on the same box, read this -
> http://msmvps.com/blogs/clusterhelp/archive/2006/02/17/84035.aspx.
> Server Clustering with make the entire node HA, SQL mirroring will make that
> database HA. Which does your customer require?
> Cheers,
> Rodney R. Fournier
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://msmvps.com/clustering - Blog
> http://www.clusterhelp.com - Cluster Training
> ClusterHelp.com is a Microsoft Certified Gold Partner
>
> "Marty Shifflett" <MartyShifflett@.discussions.microsoft.com> wrote in
> message news:F03EC295-F9C8-4602-B787-BFBCC5841E48@.microsoft.com...
>
>
|||Got it, use NLB for the IIS servers, Server Clustering for the backend
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Marty Shifflett" <MartyShifflett@.discussions.microsoft.com> wrote in
message news:ECAE918E-4B1E-4339-A4CA-7F5752DC6768@.microsoft.com...[vbcol=seagreen]
> Well that is what I had always thought, but you see more and more people
> consolidating these functions.
> Basically there will be an Access database that arrives via FTP at the web
> server, the SQL server will pick it up from a shared drive and import it.
> That will happen about every 15 minutes or so. The same web server will
> host
> a site that the client can access to see real time data that it pulls from
> the SQL server. The only snag is that if the primary SQL server goes down
> for some reason, IIS will not know that it has to get that data from the
> backup server if I am using only mirroring.
>
> "Rodney R. Fournier [MVP]" wrote:
|||NLB?
If I use clustering for the backend, will the web server be able to keep
serving the data from the SQL database to the web interface in the case of a
disaster? The web site will be looking for a specific instance of SQL
correct?
"Rodney R. Fournier [MVP]" wrote:

> Got it, use NLB for the IIS servers, Server Clustering for the backend
> Cheers,
> Rodney R. Fournier
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://msmvps.com/clustering - Blog
> http://www.clusterhelp.com - Cluster Training
> ClusterHelp.com is a Microsoft Certified Gold Partner
>
> "Marty Shifflett" <MartyShifflett@.discussions.microsoft.com> wrote in
> message news:ECAE918E-4B1E-4339-A4CA-7F5752DC6768@.microsoft.com...
>
>
|||Yes NLB (Network Load Balancing), IIS is not made for Server Clustering, and
won't be supported/allowed with Windows Server 2008 Failover Clustering.
No matter how you do your SQL, IIS will need to know the instance name to
connect.
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Marty Shifflett" <MartyShifflett@.discussions.microsoft.com> wrote in
message news:1429702D-83EF-4E83-B2DD-0C127B41DAC8@.microsoft.com...[vbcol=seagreen]
> NLB?
> If I use clustering for the backend, will the web server be able to keep
> serving the data from the SQL database to the web interface in the case of
> a
> disaster? The web site will be looking for a specific instance of SQL
> correct?
> "Rodney R. Fournier [MVP]" wrote:
|||Okay I will go with NLB for IIS. As far as the clustering for SQL goes, I am
very new on this subject. If one SQL box in the cluster fails, will the
other SQL box in that cluster pick up where the first left off and assume the
failed box's identity as far as IP address, DNS name, etc...?
Thanks for all of your help Rodney. I am glad there is a forum where people
like me can get real world help from people like yourself. Pat yourself on
the back man!!!
"Rodney R. Fournier [MVP]" wrote:

> Yes NLB (Network Load Balancing), IIS is not made for Server Clustering, and
> won't be supported/allowed with Windows Server 2008 Failover Clustering.
> No matter how you do your SQL, IIS will need to know the instance name to
> connect.
> Cheers,
> Rodney R. Fournier
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://msmvps.com/clustering - Blog
> http://www.clusterhelp.com - Cluster Training
> ClusterHelp.com is a Microsoft Certified Gold Partner
>
> "Marty Shifflett" <MartyShifflett@.discussions.microsoft.com> wrote in
> message news:1429702D-83EF-4E83-B2DD-0C127B41DAC8@.microsoft.com...
>
>
|||It will failover to another node and continue on, but at a cost. SQL not
running on the node until the failover, so the databases goes through the
normal SQL startup, DB integrity check, roll back non-committed
transactions, roll forward committed ones, etc. Your application(s) have to
be cluster aware to handle the failover. Ping tests will fail during the
process, though maybe only 4-5 depending on the network, hardware, DB sizes,
etc.
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Marty Shifflett" <MartyShifflett@.discussions.microsoft.com> wrote in
message news:20D5F814-D38D-40B0-8BDE-2C50893F6509@.microsoft.com...[vbcol=seagreen]
> Okay I will go with NLB for IIS. As far as the clustering for SQL goes, I
> am
> very new on this subject. If one SQL box in the cluster fails, will the
> other SQL box in that cluster pick up where the first left off and assume
> the
> failed box's identity as far as IP address, DNS name, etc...?
> Thanks for all of your help Rodney. I am glad there is a forum where
> people
> like me can get real world help from people like yourself. Pat yourself
> on
> the back man!!!
> "Rodney R. Fournier [MVP]" wrote:
|||The only application that will depend on the SQL server would be IIS. How is
IIS going to react if one of the cluster nodes fail? I guess I would need to
point it to the alternate node's instance.
"Rodney R. Fournier [MVP]" wrote:

> It will failover to another node and continue on, but at a cost. SQL not
> running on the node until the failover, so the databases goes through the
> normal SQL startup, DB integrity check, roll back non-committed
> transactions, roll forward committed ones, etc. Your application(s) have to
> be cluster aware to handle the failover. Ping tests will fail during the
> process, though maybe only 4-5 depending on the network, hardware, DB sizes,
> etc.
> Cheers,
> Rodney R. Fournier
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://msmvps.com/clustering - Blog
> http://www.clusterhelp.com - Cluster Training
> ClusterHelp.com is a Microsoft Certified Gold Partner
>
> "Marty Shifflett" <MartyShifflett@.discussions.microsoft.com> wrote in
> message news:20D5F814-D38D-40B0-8BDE-2C50893F6509@.microsoft.com...
>
>
|||Correct as Geoff "the SQL God and good buddy of mine" already stated.
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Marty Shifflett" <MartyShifflett@.discussions.microsoft.com> wrote in
message news:7871ABB2-D689-41A6-B9F7-30C5482A4EE3@.microsoft.com...[vbcol=seagreen]
> So the node that picks up truly is a "clone" of the failed one? Well then
> it
> sounds like I may be better off going with clustering than mirroring in my
> situation wouldn't you say?
> "Rodney R. Fournier [MVP]" wrote:

Checking for Primary Key Dependencies

Anybody know if there is a system function that can be used from a
stored procedure that determines if a given primary key has existing
dependencies? I want to make a check for this and if there are none, I
will delete the record. If there are, I will change a field called
bitStatus from 1 to 0. Enterprise Mgr. does something like this under
All Tasks, Display Dependencies. The normal way I do it is to manually
check for the existance of the primary key in every dependent table.

SQL 2000 serverI don't know about dependancies, but iof you use
EXEC sp_primarykeys (check books for parameter)
This will give you a list of the PK

--
__________________________________________________ _________________
Remotely manage MS SQL db with SQLdirector - www.ciquery.com/tools/sqldirector/

"Dan Hartshorn" <dharts@.yahoo.com> wrote in message news:8ce6b687.0308281418.12df77dc@.posting.google.c om...
> Anybody know if there is a system function that can be used from a
> stored procedure that determines if a given primary key has existing
> dependencies? I want to make a check for this and if there are none, I
> will delete the record. If there are, I will change a field called
> bitStatus from 1 to 0. Enterprise Mgr. does something like this under
> All Tasks, Display Dependencies. The normal way I do it is to manually
> check for the existance of the primary key in every dependent table.
>
> SQL 2000 server|||John Bell (jbellnewsposts@.hotmail.com) writes:
> How about sp_depends?
> If you want different output look at the source in master.

Not sure what you are thinking of, but it does not seem to me that
SQL Server saves any of this kind of information on sysdepends. It
saves CHECK constraints, but not FOREIGN KEY constraints. Presumably,
because CHECK constraints have code, while FK constraints have not.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Dan,

It sounds like you are asking this:

How can I find out if a particular primary key value
exists in the foreign key column of any dependent table?

Or for example, does customer number #324989 have any
entries in the Orders table, the BadChecks table, or the
ReturnedMerchandise table?

The dependencies displayed in Enterprise Manager are
dependencies of database objects (tables, columns,
views, etc.), not of single rows of data, so I don't think
sp_depends or Enterprise Manager is going to have anything.

And I don't think there is simple function to do this,
but there are solutions to the problem. This
is something like a garbage collection problem for
pointers in C - how do we know we've deleted the last pointer
to allocated data?

In the context of PK/FK relationships, you could
keep a master count of referring rows. Keep an integer,
in the main table - not just a bit, and that count will
be at zero when there are no references. It would have to
be updated by triggers on the dependent tables.

Let us know if this is what you are asking about.

-- Steve Kass
-- Drew University
-- Ref: 5D4089BF-0B8A-4D45-B820-923A2D6886EE

Dan Hartshorn wrote:
> Anybody know if there is a system function that can be used from a
> stored procedure that determines if a given primary key has existing
> dependencies? I want to make a check for this and if there are none, I
> will delete the record. If there are, I will change a field called
> bitStatus from 1 to 0. Enterprise Mgr. does something like this under
> All Tasks, Display Dependencies. The normal way I do it is to manually
> check for the existance of the primary key in every dependent table.
> SQL 2000 server

Checking for duplicates

Hi,
I have to run through a list of tables and check for duplicates. The
tables have no primary key setup but we expect that there should be only
1 record for a combination of fields.
So for example if I keep it simple and imagine there is a table called
customers with loads of fields and in here the uniqueness of each row is
defined by the fields salesrepid and customerid so there should be only
1 occurrence of eg salesrep xyz123 and customer abc123
What I need to do is somehow look at the table and check to make sure
that the above combination of salesrep xyz123 and customer abc123 only
occurs once and if it does oocur more than once then note it / report on it.
Any ideas on how to approach it? I imagine I could have a static table
containing the tables to be checked plus the fields of each table that
make up the uniquness. So if the tables for checking where in a table
called checkthese then in there I could have a field called tablename,
and then further rows called pkf1 (primary key field 1), pkf2, pkf3 and
so on and for the customers table the pkf1 would have value salesrepid
and pkf2 would have value of customerid.
I've probably not made much sense up there but basically what I need to
do is read a table that contains table names and fields that make up the
uniqueness of the table and then go and check those tables to make sure
that there are no more than one of each record that makes up the uniqueness.
any ideas?
tia,
toeYou could store the table names and column names in a table...or you could
just put them within a script or stored procedure.
To check for uniqueness on one table you could do something like this:
SELECT TheFirstCol, TheSecondCol.... (repeat as needed),
COUNT(*) AS TheDuplicateCount
FROM YourTable
GROUP BY TheFirstCol, TheSecondCol.... (repeat as needed)
HAVING COUNT(*) > 1
You could also do something like this
IF EXISTS (
SELECT TheFirstCol, TheSecondCol.... (repeat as needed),
COUNT(*) AS TheDuplicateCount
FROM YourTable
GROUP BY TheFirstCol, TheSecondCol.... (repeat as needed)
HAVING COUNT(*) > 1)
BEGIN
PRINT 'Duplicate found in table xyz'
END
...repeat
--
Keith Kratochvil
"toedipper" <send_rubbish_here734@.hotmail.com> wrote in message
news:0z0fg.5150$O7.3856@.newsfe5-win.ntli.net...
> Hi,
> I have to run through a list of tables and check for duplicates. The
> tables have no primary key setup but we expect that there should be only 1
> record for a combination of fields.
> So for example if I keep it simple and imagine there is a table called
> customers with loads of fields and in here the uniqueness of each row is
> defined by the fields salesrepid and customerid so there should be only 1
> occurrence of eg salesrep xyz123 and customer abc123
> What I need to do is somehow look at the table and check to make sure that
> the above combination of salesrep xyz123 and customer abc123 only occurs
> once and if it does oocur more than once then note it / report on it.
> Any ideas on how to approach it? I imagine I could have a static table
> containing the tables to be checked plus the fields of each table that
> make up the uniquness. So if the tables for checking where in a table
> called checkthese then in there I could have a field called tablename, and
> then further rows called pkf1 (primary key field 1), pkf2, pkf3 and so on
> and for the customers table the pkf1 would have value salesrepid and pkf2
> would have value of customerid.
> I've probably not made much sense up there but basically what I need to do
> is read a table that contains table names and fields that make up the
> uniqueness of the table and then go and check those tables to make sure
> that there are no more than one of each record that makes up the
> uniqueness.
> any ideas?
> tia,
> toe|||Thanks Keith.
If I where to store the table name and columns to check in a table then
have you any pointers On how I would the go to the table(s) and check
that there is only 1 occurrence for each set of columns?
So if I have the table called 'checkthese' with the fields and data below
Fields Data
tablename: customers
pkf1: salesrepid
pkf2: customerid
How would I actaully look that up and then go to the customers table to
make sure there is only one row of each?
Cheers,
toe
Keith Kratochvil wrote:
> You could store the table names and column names in a table...or you could
> just put them within a script or stored procedure.
> To check for uniqueness on one table you could do something like this:
> SELECT TheFirstCol, TheSecondCol.... (repeat as needed),
> COUNT(*) AS TheDuplicateCount
> FROM YourTable
> GROUP BY TheFirstCol, TheSecondCol.... (repeat as needed)
> HAVING COUNT(*) > 1
> You could also do something like this
> IF EXISTS (
> SELECT TheFirstCol, TheSecondCol.... (repeat as needed),
> COUNT(*) AS TheDuplicateCount
> FROM YourTable
> GROUP BY TheFirstCol, TheSecondCol.... (repeat as needed)
> HAVING COUNT(*) > 1)
> BEGIN
> PRINT 'Duplicate found in table xyz'
> END
> ...repeat|||One method would be to cursor through the data within checkthese and build
and execute the appropriate sql statement.
You could also write a sql statement that would create the appropriate T-SQL
commands that you would have to execute on your own.
I will let you explore the cursor option a bit. The other option would look
something like this:
--your table
create table #checkthese (TableName varchar(128), PKcols varchar(2000))
insert into #checkthese (TableName, PKcols) VALUES ('customers',
'salesrepid, customerid')
insert into #checkthese (TableName, PKcols) VALUES ('SalesRep',
'salesrepid')
GO
--the select (run the output)
SELECT 'IF EXISTS (SELECT ' + PKcols + ' , COUNT(*) FROM ' + TableName + '
GROUP BY ' + PKcols + ' HAVING COUNT(*) > 1 )
BEGIN
PRINT ''Duplicates found within '' + TableName
END' + char(13) + char(10) + 'GO'
from #checkthese
Keith Kratochvil
"toedipper" <send_rubbish_here734@.hotmail.com> wrote in message
news:447CBDE8.9030902@.hotmail.com...
> Thanks Keith.
> If I where to store the table name and columns to check in a table then
> have you any pointers On how I would the go to the table(s) and check that
> there is only 1 occurrence for each set of columns?
> So if I have the table called 'checkthese' with the fields and data below
> Fields Data tablename: customers
> pkf1: salesrepid
> pkf2: customerid
> How would I actaully look that up and then go to the customers table to
> make sure there is only one row of each?
> Cheers,
> toe
>
> Keith Kratochvil wrote:

CHECKDB failed with DB ONLINE and filegroup read-only

Hello everybody,

I have a very stranger problem that I need to understand...

I have one DB with 3 files and 2 filegroups (primary and FGTESTE). After to place FGTESTE filegroup as read-only, DBCC CHECKDB (DBTESTE3) failed with error:

Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.

I noticed that if I kill all connections of the database DBCC work fine, but if a have any connections on DB, DBCC failed.

Some idea of the why DBCC do not work with database online?

Steps to Reproduce

1. Open new query (conn1) and create new database
CREATE DATABASE DBTESTE3
GO
-- Add new filegroup
ALTER DATABASE DBTESTE3 ADD FILEGROUP FGTESTE
GO
-- Add file to new filegroup
ALTER DATABASE DBTESTE3 ADD FILE (NAME=DBTESTE3_Data2, FILENAME='C:\DBTESTE3_Data2.ndf')
TO FILEGROUP FGTESTE
GO
-- Alter filegroup to readonly
ALTER DATABASE DBTESTE3 MODIFY FILEGROUP FGTESTE READONLY
GO
2. Run DBCC in conn1
-- Here DBCC run OK
DBCC CHECKDB (DBTESTE3)
3. Open new query window (conn2) and set database as DBTESTE3. This open a connection to DBTESTE3.
4. Go to conn1 and run DBCC again
-- Now I get Dbcc error
DBCC CHECKDB (DBTESTE3)

Hello Storage Team...

Please, Is this a normal issue ?

Nilton Pinheiro
SQL Server MVP

|||

This should work.

A couple of questions:

What version/SP of SQL are you using?

Does this scenario work if you do not set the filegroup to readonly?

|||

Hi Kevin....thanks for you help !!

Well, I have Windows Server 2003 Standard x64 SP1 + SQL 2005 Enterprise SP1 (I have machine with Windows Enterprise 2003 x64 or x32 with SQL 2005 SP1 and problem is show too).

This is my SELECT @.@.version output

Microsoft SQL Server 2005 - 9.00.2047.00 (X64)
Apr 14 2006 01:11:53
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)

This is my sp_helpfile after create DB:

DBTESTE3..sp_helpfile
DBTESTE3 1 E:\MSSQL.1\MSSQL\DATA\DBTESTE3.mdf
DBTESTE3_log 2 E:\MSSQL.1\MSSQL\DATA\DBTESTE3_log.LDF
DBTESTE3_Data2 3 E:\DBTESTE3_Data2.ndf

Where E:\ is a NTFS file ssytem.

Does this scenario work if you do not set the filegroup to readonly? Yes !!

thanks
Nilton Pinheiro

|||

I have reproduced this as well. It appears to be a bug, and I have filed it as such.

We will be working to get a fix for this out as soon as we can.

|||

very good Kevin...thanks for you help.

Nilton Pinheiro
SQL Server MVP

|||

Hello Kevin,

Do you have some information about this bug? Does SP2 fix it?

Thanks
Nilton Pinheiro
www.mcdbabrasil.com.br

|||

This turned out to be a design limitation that was not documented. We hope to address this in the next release of SQL Server and will document the limitation in the meantime.

There is a workaround of creating a database snapshot and running the DBCC CHECKDB against the snapshot for those Editions that support database snapshots.

|||

Hi Peter, thanks for attention and feedback.

I think that a KB would be very good :)

Thanks
Nilton Pinheiro
www.mcdbabrasil.com.br

|||It is my understanding that there is one in the works.

CHECKDB failed with DB ONLINE and filegroup read-only

Hello everybody,

I have a very stranger problem that I need to understand...

I have one DB with 3 files and 2 filegroups (primary and FGTESTE). After to place FGTESTE filegroup as read-only, DBCC CHECKDB (DBTESTE3) failed with error:

Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.

I noticed that if I kill all connections of the database DBCC work fine, but if a have any connections on DB, DBCC failed.

Some idea of the why DBCC do not work with database online?

Steps to Reproduce

1. Open new query (conn1) and create new database
CREATE DATABASE DBTESTE3
GO
-- Add new filegroup
ALTER DATABASE DBTESTE3 ADD FILEGROUP FGTESTE
GO
-- Add file to new filegroup
ALTER DATABASE DBTESTE3 ADD FILE (NAME=DBTESTE3_Data2, FILENAME='C:\DBTESTE3_Data2.ndf')
TO FILEGROUP FGTESTE
GO
-- Alter filegroup to readonly
ALTER DATABASE DBTESTE3 MODIFY FILEGROUP FGTESTE READONLY
GO
2. Run DBCC in conn1
-- Here DBCC run OK
DBCC CHECKDB (DBTESTE3)
3. Open new query window (conn2) and set database as DBTESTE3. This open a connection to DBTESTE3.
4. Go to conn1 and run DBCC again
-- Now I get Dbcc error
DBCC CHECKDB (DBTESTE3)

Hello Storage Team...

Please, Is this a normal issue ?

Nilton Pinheiro
SQL Server MVP

|||

This should work.

A couple of questions:

What version/SP of SQL are you using?

Does this scenario work if you do not set the filegroup to readonly?

|||

Hi Kevin....thanks for you help !!

Well, I have Windows Server 2003 Standard x64 SP1 + SQL 2005 Enterprise SP1 (I have machine with Windows Enterprise 2003 x64 or x32 with SQL 2005 SP1 and problem is show too).

This is my SELECT @.@.version output

Microsoft SQL Server 2005 - 9.00.2047.00 (X64)
Apr 14 2006 01:11:53
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)

This is my sp_helpfile after create DB:

DBTESTE3..sp_helpfile
DBTESTE3 1 E:\MSSQL.1\MSSQL\DATA\DBTESTE3.mdf
DBTESTE3_log 2 E:\MSSQL.1\MSSQL\DATA\DBTESTE3_log.LDF
DBTESTE3_Data2 3 E:\DBTESTE3_Data2.ndf

Where E:\ is a NTFS file ssytem.

Does this scenario work if you do not set the filegroup to readonly? Yes !!

thanks
Nilton Pinheiro

|||

I have reproduced this as well. It appears to be a bug, and I have filed it as such.

We will be working to get a fix for this out as soon as we can.

|||

very good Kevin...thanks for you help.

Nilton Pinheiro
SQL Server MVP

|||

Hello Kevin,

Do you have some information about this bug? Does SP2 fix it?

Thanks
Nilton Pinheiro
www.mcdbabrasil.com.br

|||

This turned out to be a design limitation that was not documented. We hope to address this in the next release of SQL Server and will document the limitation in the meantime.

There is a workaround of creating a database snapshot and running the DBCC CHECKDB against the snapshot for those Editions that support database snapshots.

|||

Hi Peter, thanks for attention and feedback.

I think that a KB would be very good :)

Thanks
Nilton Pinheiro
www.mcdbabrasil.com.br

|||It is my understanding that there is one in the works.

CHECKDB failed with DB ONLINE and filegroup read-only

Hello everybody,

I have a very stranger problem that I need to understand...

I have one DB with 3 files and 2 filegroups (primary and FGTESTE). After to place FGTESTE filegroup as read-only, DBCC CHECKDB (DBTESTE3) failed with error:

Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.

I noticed that if I kill all connections of the database DBCC work fine, but if a have any connections on DB, DBCC failed.

Some idea of the why DBCC do not work with database online?

Steps to Reproduce

1. Open new query (conn1) and create new database
CREATE DATABASE DBTESTE3
GO
-- Add new filegroup
ALTER DATABASE DBTESTE3 ADD FILEGROUP FGTESTE
GO
-- Add file to new filegroup
ALTER DATABASE DBTESTE3 ADD FILE (NAME=DBTESTE3_Data2, FILENAME='C:\DBTESTE3_Data2.ndf')
TO FILEGROUP FGTESTE
GO
-- Alter filegroup to readonly
ALTER DATABASE DBTESTE3 MODIFY FILEGROUP FGTESTE READONLY
GO
2. Run DBCC in conn1
-- Here DBCC run OK
DBCC CHECKDB (DBTESTE3)
3. Open new query window (conn2) and set database as DBTESTE3. This open a connection to DBTESTE3.
4. Go to conn1 and run DBCC again
-- Now I get Dbcc error
DBCC CHECKDB (DBTESTE3)

Hello Storage Team...

Please, Is this a normal issue ?

Nilton Pinheiro
SQL Server MVP

|||

This should work.

A couple of questions:

What version/SP of SQL are you using?

Does this scenario work if you do not set the filegroup to readonly?

|||

Hi Kevin....thanks for you help !!

Well, I have Windows Server 2003 Standard x64 SP1 + SQL 2005 Enterprise SP1 (I have machine with Windows Enterprise 2003 x64 or x32 with SQL 2005 SP1 and problem is show too).

This is my SELECT @.@.version output

Microsoft SQL Server 2005 - 9.00.2047.00 (X64)
Apr 14 2006 01:11:53
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)

This is my sp_helpfile after create DB:

DBTESTE3..sp_helpfile
DBTESTE3 1 E:\MSSQL.1\MSSQL\DATA\DBTESTE3.mdf
DBTESTE3_log 2 E:\MSSQL.1\MSSQL\DATA\DBTESTE3_log.LDF
DBTESTE3_Data2 3 E:\DBTESTE3_Data2.ndf

Where E:\ is a NTFS file ssytem.

Does this scenario work if you do not set the filegroup to readonly? Yes !!

thanks
Nilton Pinheiro

|||

I have reproduced this as well. It appears to be a bug, and I have filed it as such.

We will be working to get a fix for this out as soon as we can.

|||

very good Kevin...thanks for you help.

Nilton Pinheiro
SQL Server MVP

|||

Hello Kevin,

Do you have some information about this bug? Does SP2 fix it?

Thanks
Nilton Pinheiro
www.mcdbabrasil.com.br

|||

This turned out to be a design limitation that was not documented. We hope to address this in the next release of SQL Server and will document the limitation in the meantime.

There is a workaround of creating a database snapshot and running the DBCC CHECKDB against the snapshot for those Editions that support database snapshots.

|||

Hi Peter, thanks for attention and feedback.

I think that a KB would be very good :)

Thanks
Nilton Pinheiro
www.mcdbabrasil.com.br

|||It is my understanding that there is one in the works.