Database Growth & Space Recovery Problem

I have done this experiment on one of the tables. There is table called
build havinf nvText Field with large no of records. I want to drop that
column and recover space. These are the results I got.
SP_SPACEUSED BUILD Results
name rows reserved data index_size unused
1. Before Deleting nvText Field
Build 663211 341440 KB 339464 KB 1944 KB 32 KB
2. After Deleting nvText Field
Build 663211 341440 KB 339464 KB 1944 KB 32 KB
3. After Executing the Shrink Database from Enterprise Manager.
Build 663211 608280 KB 604744 KB 3456 KB 80 K
4. After Executing DBCC DBReindex (build,'',70)
Build 663211 124096 KB 123392 KB 712 KB -8 KB
Can anyone please explain me after executing step 3 i.e shrink data
column as well as index_size shows an increased figure whereas logically
it should be a reduced figure.
Regards,
Harcharan
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
One thing is that you should be suspicious of the numbers , as you are..
SP_SPaceused reports information from Sysindexes, but the sysindexes info is
NOT updated with everyinsert update or delete...So the numbers may not
reflect the actual size..
For each sp_spaceused in your test , execute like this
exec sp_spaceused 'build', true
The second parameter tells sql to read through the table and update the
estimates, it runs longer but you will get tru results...If the numbers
still look funny, we'll try something else...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Harcharan Jassal" <hjjassal@.yahoo.com> wrote in message
news:uCtYWSBhEHA.3540@.TK2MSFTNGP10.phx.gbl...
> I have done this experiment on one of the tables. There is table called
> build havinf nvText Field with large no of records. I want to drop that
> column and recover space. These are the results I got.
> SP_SPACEUSED 'BUILD' Results
> name rows reserved data index_size unused
> 1. Before Deleting nvText Field
> Build 663211 341440 KB 339464 KB 1944 KB 32 KB
> 2. After Deleting nvText Field
> Build 663211 341440 KB 339464 KB 1944 KB 32 KB
> 3. After Executing the Shrink Database from Enterprise Manager.
> Build 663211 608280 KB 604744 KB 3456 KB 80 K
> 4. After Executing DBCC DBReindex (build,'',70)
> Build 663211 124096 KB 123392 KB 712 KB -8 KB
> Can anyone please explain me after executing step 3 i.e shrink data
> column as well as index_size shows an increased figure whereas logically
> it should be a reduced figure.
> Regards,
> Harcharan
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Database Growth & Space Recovery Problem

I have done this experiment on one of the tables. There is table called
build havinf nvText Field with large no of records. I want to drop that
column and recover space. These are the results I got.
SP_SPACEUSED BUILD Results
name rows reserved data index_size unused
1. Before Deleting nvText Field
Build 663211 341440 KB 339464 KB 1944 KB 32 KB
2. After Deleting nvText Field
Build 663211 341440 KB 339464 KB 1944 KB 32 KB
3. After Executing the Shrink Database from Enterprise Manager.
Build 663211 608280 KB 604744 KB 3456 KB 80 K
4. After Executing DBCC DBReindex (build,'',70)
Build 663211 124096 KB 123392 KB 712 KB -8 KB
Can anyone please explain me after executing step 3 i.e shrink data
column as well as index_size shows an increased figure whereas logically
it should be a reduced figure.
Regards,
Harcharan
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!One thing is that you should be suspicious of the numbers , as you are..
SP_SPaceused reports information from Sysindexes, but the sysindexes info is
NOT updated with everyinsert update or delete...So the numbers may not
reflect the actual size..
For each sp_spaceused in your test , execute like this
exec sp_spaceused 'build', true
The second parameter tells sql to read through the table and update the
estimates, it runs longer but you will get tru results...If the numbers
still look funny, we'll try something else...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Harcharan Jassal" <hjjassal@.yahoo.com> wrote in message
news:uCtYWSBhEHA.3540@.TK2MSFTNGP10.phx.gbl...
> I have done this experiment on one of the tables. There is table called
> build havinf nvText Field with large no of records. I want to drop that
> column and recover space. These are the results I got.
> SP_SPACEUSED 'BUILD' Results
> name rows reserved data index_size unused
> 1. Before Deleting nvText Field
> Build 663211 341440 KB 339464 KB 1944 KB 32 KB
> 2. After Deleting nvText Field
> Build 663211 341440 KB 339464 KB 1944 KB 32 KB
> 3. After Executing the Shrink Database from Enterprise Manager.
> Build 663211 608280 KB 604744 KB 3456 KB 80 K
> 4. After Executing DBCC DBReindex (build,'',70)
> Build 663211 124096 KB 123392 KB 712 KB -8 KB
> Can anyone please explain me after executing step 3 i.e shrink data
> column as well as index_size shows an increased figure whereas logically
> it should be a reduced figure.
> Regards,
> Harcharan
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!sql

Database Diagrams: FOREIGN KEY constraint "fell off"; can't drop/recreate it...

Hello,

I have two tables Person & Location where Location has a primary key LocationId and Person has a foreign key LocationId.

Sometime ago I used the Database Diagrams visual tool of SQL Server Management Studio Express to create the foreign key relationship between the two tables - i.e. "visually" (drawing a line between the PK & FK LocationId elements of both tables).

Time has passed and I recently noticed that, upon retrieving my saved diagram, the foreign key relationship had "fallen off" (i.e. the many-to-one line was no longer showing in the diagram).

After recreating the relationship (redrawing the line) I find that I get an error message when I try to save the diagram:

Post-Save Notifications
[!] Errors were encountered during the save process. Some database objects were not saved.

'Location' table saved successfully
'Person' table
- Unable to create relationship 'FK_Person_Location'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Person_Location". The conflict occurred in database "mydb", table "dbo.Location", column 'LocationId'.

When I go back to the object explorer and view the dependencies for the two tables, there is no dependency (between these two tables) revealed. When I try to create the foreign key constraint manually (T-SQL) it again says can't add the constraint. It comes up with an error as follows:

ALTER TABLE Person
ADD FOREIGN KEY (LocationId) REFERENCES Location (LocationId)

Msg 547, Level 16, State 0, Line 2
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK__Person__LocationId__793DFFAF". The conflict occurred in database "mydb", table "dbo.Location", column 'LocationId'.

(Note: Each time I do this, the 8 hexadecimal character suffix changes.)

When I try to drop the foreign key:

alter table Person
drop constraint FK__Person__LocationId

it comes back with the error:

Msg 3728, Level 16, State 1, Line 2
'FK__Person__LocationId' is not a constraint.
Msg 3727, Level 16, State 0, Line 2
Could not drop constraint. See previous errors.

So it seems that there's some kind of goof up here. Can anybody shed light on this / tell me how to fix it?

Best guess, you have data that will not pass the Foreign key constraint. As for why it "dropped off" that is a totally different question that can;t be answered here (do your developers have dbo access and possibly dropped the constraint so they could insert invalid data to make the UI work? (no offense to any developer who wouldn't do that :) )

create table person
(
personId int constraint PKperson primary key,
locationId int
)
insert into person
values (1,1)
insert into person
values (2,2)

create table location
(
locationId int constraint PKlocation primary key
)

ALTER TABLE Person
ADD FOREIGN KEY (LocationId) REFERENCES Location (LocationId)

Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK__person__location__4B0D20AB". The conflict occurred in database "master", table "dbo.location", column 'locationId'.

Better to name the constraint:
ALTER TABLE Person
ADD Constraint FKPerson_References_Location FOREIGN KEY (LocationId) REFERENCES Location (LocationId)

Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FKPerson_References_Location". The conflict occurred in database "master", table "dbo.location", column 'locationId'.

insert into location
values (1)
insert into location
values (2)

ALTER TABLE Person
ADD Constraint FKPerson_References_Location FOREIGN KEY (LocationId) REFERENCES Location (LocationId)

to see the constraints and the columns you can use this query:

select *
from information_schema.constraint_column_usage as ccu
join information_schema.referential_constraints as rc
on rc.constraint_schema = ccu.table_schema
and ccu.constraint_name = rc.constraint_name
where ccu.table_name = 'person'

Also, if you want to ignore the invalid data there is a NOCHECK clause when creating constraints, but it is highly advised to not use it.

|||

Thanks Louis for the useful info. It helped in examining the problem more closely.

In the end I decided just to save off my data from the Location table, and to drop / recreate it. Everything is working fine again. I really believe that an inconsistency got in the system tables somewhere - that was my main concern. By dropping / recreating the problem table I believe I eliminated the inconsistency.

|||I get the same error. I wish I could find the source of it in the system tables (or wherever the cause is located) and fix it - I do not have the luxury of merely trying to drop and recreata the tables.|||

Hi all,

I have the same error. But only on my table that I migrate from SQL 2000 to SQL 2005.

And I can't drop and recreate table.

How can I do ?

|||

The ALTER TABLE that you were using does not have a constraint name specified so SQL Server will generate a name automatically and since constraint names have to be unique SQL Server uses combination of table / column names / unique number to make it unique. But it looks like you never had the FK constraint defined so the tables would have contained invalid data. And when you tried to create the constraint from the diagram designer it failed and the constraint creation gets rolled back. You could have checked the data in the table like below to find the offending rows:

select *

from Person as p

where not exists(

select * from Location as l

where l.LocationId = p.LocationId

)

But you shouldn't use diagram designer as the modelling tool. It is not designed for that. It does lot of things behind the scenes which is unnecessary in some cases (like dropping & recreating tables to make certain schema changes). You could make simple schema changes but script out the schema definitions and store it in files.

Database Diagrams: FOREIGN KEY constraint "fell off"; can't drop/recreate it...

Hello,

I have two tables Person & Location where Location has a primary key LocationId and Person has a foreign key LocationId.

Sometime ago I used the Database Diagrams visual tool of SQL Server Management Studio Express to create the foreign key relationship between the two tables - i.e. "visually" (drawing a line between the PK & FK LocationId elements of both tables).

Time has passed and I recently noticed that, upon retrieving my saved diagram, the foreign key relationship had "fallen off" (i.e. the many-to-one line was no longer showing in the diagram).

After recreating the relationship (redrawing the line) I find that I get an error message when I try to save the diagram:

Post-Save Notifications
[!] Errors were encountered during the save process. Some database objects were not saved.

'Location' table saved successfully
'Person' table
- Unable to create relationship 'FK_Person_Location'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Person_Location". The conflict occurred in database "mydb", table "dbo.Location", column 'LocationId'.

When I go back to the object explorer and view the dependencies for the two tables, there is no dependency (between these two tables) revealed. When I try to create the foreign key constraint manually (T-SQL) it again says can't add the constraint. It comes up with an error as follows:

ALTER TABLE Person
ADD FOREIGN KEY (LocationId) REFERENCES Location (LocationId)

Msg 547, Level 16, State 0, Line 2
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK__Person__LocationId__793DFFAF". The conflict occurred in database "mydb", table "dbo.Location", column 'LocationId'.

(Note: Each time I do this, the 8 hexadecimal character suffix changes.)

When I try to drop the foreign key:

alter table Person
drop constraint FK__Person__LocationId

it comes back with the error:

Msg 3728, Level 16, State 1, Line 2
'FK__Person__LocationId' is not a constraint.
Msg 3727, Level 16, State 0, Line 2
Could not drop constraint. See previous errors.

So it seems that there's some kind of goof up here. Can anybody shed light on this / tell me how to fix it?

Best guess, you have data that will not pass the Foreign key constraint. As for why it "dropped off" that is a totally different question that can;t be answered here (do your developers have dbo access and possibly dropped the constraint so they could insert invalid data to make the UI work? (no offense to any developer who wouldn't do that :) )

create table person
(
personId int constraint PKperson primary key,
locationId int
)
insert into person
values (1,1)
insert into person
values (2,2)

create table location
(
locationId int constraint PKlocation primary key
)

ALTER TABLE Person
ADD FOREIGN KEY (LocationId) REFERENCES Location (LocationId)

Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK__person__location__4B0D20AB". The conflict occurred in database "master", table "dbo.location", column 'locationId'.

Better to name the constraint:
ALTER TABLE Person
ADD Constraint FKPerson_References_Location FOREIGN KEY (LocationId) REFERENCES Location (LocationId)

Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FKPerson_References_Location". The conflict occurred in database "master", table "dbo.location", column 'locationId'.

insert into location
values (1)
insert into location
values (2)

ALTER TABLE Person
ADD Constraint FKPerson_References_Location FOREIGN KEY (LocationId) REFERENCES Location (LocationId)

to see the constraints and the columns you can use this query:

select *
from information_schema.constraint_column_usage as ccu
join information_schema.referential_constraints as rc
on rc.constraint_schema = ccu.table_schema
and ccu.constraint_name = rc.constraint_name
where ccu.table_name = 'person'

Also, if you want to ignore the invalid data there is a NOCHECK clause when creating constraints, but it is highly advised to not use it.

|||

Thanks Louis for the useful info. It helped in examining the problem more closely.

In the end I decided just to save off my data from the Location table, and to drop / recreate it. Everything is working fine again. I really believe that an inconsistency got in the system tables somewhere - that was my main concern. By dropping / recreating the problem table I believe I eliminated the inconsistency.

|||I get the same error. I wish I could find the source of it in the system tables (or wherever the cause is located) and fix it - I do not have the luxury of merely trying to drop and recreata the tables.|||

Hi all,

I have the same error. But only on my table that I migrate from SQL 2000 to SQL 2005.

And I can't drop and recreate table.

How can I do ?

|||

The ALTER TABLE that you were using does not have a constraint name specified so SQL Server will generate a name automatically and since constraint names have to be unique SQL Server uses combination of table / column names / unique number to make it unique. But it looks like you never had the FK constraint defined so the tables would have contained invalid data. And when you tried to create the constraint from the diagram designer it failed and the constraint creation gets rolled back. You could have checked the data in the table like below to find the offending rows:

select *

from Person as p

where not exists(

select * from Location as l

where l.LocationId = p.LocationId

)

But you shouldn't use diagram designer as the modelling tool. It is not designed for that. It does lot of things behind the scenes which is unnecessary in some cases (like dropping & recreating tables to make certain schema changes). You could make simple schema changes but script out the schema definitions and store it in files.

Database Diagrams: FOREIGN KEY constraint "fell off"; can't drop/recreate it...

Hello,

I have two tables Person & Location where Location has a primary key LocationId and Person has a foreign key LocationId.

Sometime ago I used the Database Diagrams visual tool of SQL Server Management Studio Express to create the foreign key relationship between the two tables - i.e. "visually" (drawing a line between the PK & FK LocationId elements of both tables).

Time has passed and I recently noticed that, upon retrieving my saved diagram, the foreign key relationship had "fallen off" (i.e. the many-to-one line was no longer showing in the diagram).

After recreating the relationship (redrawing the line) I find that I get an error message when I try to save the diagram:

Post-Save Notifications
[!] Errors were encountered during the save process. Some database objects were not saved.

'Location' table saved successfully
'Person' table
- Unable to create relationship 'FK_Person_Location'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Person_Location". The conflict occurred in database "mydb", table "dbo.Location", column 'LocationId'.

When I go back to the object explorer and view the dependencies for the two tables, there is no dependency (between these two tables) revealed. When I try to create the foreign key constraint manually (T-SQL) it again says can't add the constraint. It comes up with an error as follows:

ALTER TABLE Person
ADD FOREIGN KEY (LocationId) REFERENCES Location (LocationId)

Msg 547, Level 16, State 0, Line 2
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK__Person__LocationId__793DFFAF". The conflict occurred in database "mydb", table "dbo.Location", column 'LocationId'.

(Note: Each time I do this, the 8 hexadecimal character suffix changes.)

When I try to drop the foreign key:

alter table Person
drop constraint FK__Person__LocationId

it comes back with the error:

Msg 3728, Level 16, State 1, Line 2
'FK__Person__LocationId' is not a constraint.
Msg 3727, Level 16, State 0, Line 2
Could not drop constraint. See previous errors.

So it seems that there's some kind of goof up here. Can anybody shed light on this / tell me how to fix it?

Best guess, you have data that will not pass the Foreign key constraint. As for why it "dropped off" that is a totally different question that can;t be answered here (do your developers have dbo access and possibly dropped the constraint so they could insert invalid data to make the UI work? (no offense to any developer who wouldn't do that :) )

create table person
(
personId int constraint PKperson primary key,
locationId int
)
insert into person
values (1,1)
insert into person
values (2,2)

create table location
(
locationId int constraint PKlocation primary key
)

ALTER TABLE Person
ADD FOREIGN KEY (LocationId) REFERENCES Location (LocationId)

Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK__person__location__4B0D20AB". The conflict occurred in database "master", table "dbo.location", column 'locationId'.

Better to name the constraint:
ALTER TABLE Person
ADD Constraint FKPerson_References_Location FOREIGN KEY (LocationId) REFERENCES Location (LocationId)

Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FKPerson_References_Location". The conflict occurred in database "master", table "dbo.location", column 'locationId'.

insert into location
values (1)
insert into location
values (2)

ALTER TABLE Person
ADD Constraint FKPerson_References_Location FOREIGN KEY (LocationId) REFERENCES Location (LocationId)

to see the constraints and the columns you can use this query:

select *
from information_schema.constraint_column_usage as ccu
join information_schema.referential_constraints as rc
on rc.constraint_schema = ccu.table_schema
and ccu.constraint_name = rc.constraint_name
where ccu.table_name = 'person'

Also, if you want to ignore the invalid data there is a NOCHECK clause when creating constraints, but it is highly advised to not use it.

|||

Thanks Louis for the useful info. It helped in examining the problem more closely.

In the end I decided just to save off my data from the Location table, and to drop / recreate it. Everything is working fine again. I really believe that an inconsistency got in the system tables somewhere - that was my main concern. By dropping / recreating the problem table I believe I eliminated the inconsistency.

|||I get the same error. I wish I could find the source of it in the system tables (or wherever the cause is located) and fix it - I do not have the luxury of merely trying to drop and recreata the tables.|||

Hi all,

I have the same error. But only on my table that I migrate from SQL 2000 to SQL 2005.

And I can't drop and recreate table.

How can I do ?

|||

The ALTER TABLE that you were using does not have a constraint name specified so SQL Server will generate a name automatically and since constraint names have to be unique SQL Server uses combination of table / column names / unique number to make it unique. But it looks like you never had the FK constraint defined so the tables would have contained invalid data. And when you tried to create the constraint from the diagram designer it failed and the constraint creation gets rolled back. You could have checked the data in the table like below to find the offending rows:

select *

from Person as p

where not exists(

select * from Location as l

where l.LocationId = p.LocationId

)

But you shouldn't use diagram designer as the modelling tool. It is not designed for that. It does lot of things behind the scenes which is unnecessary in some cases (like dropping & recreating tables to make certain schema changes). You could make simple schema changes but script out the schema definitions and store it in files.

Database Diagrams: FOREIGN KEY constraint "fell off"; can't drop/recreate it...

Hello,

I have two tables Person & Location where Location has a primary key LocationId and Person has a foreign key LocationId.

Sometime ago I used the Database Diagrams visual tool of SQL Server Management Studio Express to create the foreign key relationship between the two tables - i.e. "visually" (drawing a line between the PK & FK LocationId elements of both tables).

Time has passed and I recently noticed that, upon retrieving my saved diagram, the foreign key relationship had "fallen off" (i.e. the many-to-one line was no longer showing in the diagram).

After recreating the relationship (redrawing the line) I find that I get an error message when I try to save the diagram:

Post-Save Notifications
[!] Errors were encountered during the save process. Some database objects were not saved.

'Location' table saved successfully
'Person' table
- Unable to create relationship 'FK_Person_Location'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Person_Location". The conflict occurred in database "mydb", table "dbo.Location", column 'LocationId'.

When I go back to the object explorer and view the dependencies for the two tables, there is no dependency (between these two tables) revealed. When I try to create the foreign key constraint manually (T-SQL) it again says can't add the constraint. It comes up with an error as follows:

ALTER TABLE Person
ADD FOREIGN KEY (LocationId) REFERENCES Location (LocationId)

Msg 547, Level 16, State 0, Line 2
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK__Person__LocationId__793DFFAF". The conflict occurred in database "mydb", table "dbo.Location", column 'LocationId'.

(Note: Each time I do this, the 8 hexadecimal character suffix changes.)

When I try to drop the foreign key:

alter table Person
drop constraint FK__Person__LocationId

it comes back with the error:

Msg 3728, Level 16, State 1, Line 2
'FK__Person__LocationId' is not a constraint.
Msg 3727, Level 16, State 0, Line 2
Could not drop constraint. See previous errors.

So it seems that there's some kind of goof up here. Can anybody shed light on this / tell me how to fix it?

Best guess, you have data that will not pass the Foreign key constraint. As for why it "dropped off" that is a totally different question that can;t be answered here (do your developers have dbo access and possibly dropped the constraint so they could insert invalid data to make the UI work? (no offense to any developer who wouldn't do that :) )

create table person
(
personId int constraint PKperson primary key,
locationId int
)
insert into person
values (1,1)
insert into person
values (2,2)

create table location
(
locationId int constraint PKlocation primary key
)

ALTER TABLE Person
ADD FOREIGN KEY (LocationId) REFERENCES Location (LocationId)

Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK__person__location__4B0D20AB". The conflict occurred in database "master", table "dbo.location", column 'locationId'.

Better to name the constraint:
ALTER TABLE Person
ADD Constraint FKPerson_References_Location FOREIGN KEY (LocationId) REFERENCES Location (LocationId)

Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FKPerson_References_Location". The conflict occurred in database "master", table "dbo.location", column 'locationId'.

insert into location
values (1)
insert into location
values (2)

ALTER TABLE Person
ADD Constraint FKPerson_References_Location FOREIGN KEY (LocationId) REFERENCES Location (LocationId)

to see the constraints and the columns you can use this query:

select *
from information_schema.constraint_column_usage as ccu
join information_schema.referential_constraints as rc
on rc.constraint_schema = ccu.table_schema
and ccu.constraint_name = rc.constraint_name
where ccu.table_name = 'person'

Also, if you want to ignore the invalid data there is a NOCHECK clause when creating constraints, but it is highly advised to not use it.

|||

Thanks Louis for the useful info. It helped in examining the problem more closely.

In the end I decided just to save off my data from the Location table, and to drop / recreate it. Everything is working fine again. I really believe that an inconsistency got in the system tables somewhere - that was my main concern. By dropping / recreating the problem table I believe I eliminated the inconsistency.

|||I get the same error. I wish I could find the source of it in the system tables (or wherever the cause is located) and fix it - I do not have the luxury of merely trying to drop and recreata the tables.|||

Hi all,

I have the same error. But only on my table that I migrate from SQL 2000 to SQL 2005.

And I can't drop and recreate table.

How can I do ?

|||

The ALTER TABLE that you were using does not have a constraint name specified so SQL Server will generate a name automatically and since constraint names have to be unique SQL Server uses combination of table / column names / unique number to make it unique. But it looks like you never had the FK constraint defined so the tables would have contained invalid data. And when you tried to create the constraint from the diagram designer it failed and the constraint creation gets rolled back. You could have checked the data in the table like below to find the offending rows:

select *

from Person as p

where not exists(

select * from Location as l

where l.LocationId = p.LocationId

)

But you shouldn't use diagram designer as the modelling tool. It is not designed for that. It does lot of things behind the scenes which is unnecessary in some cases (like dropping & recreating tables to make certain schema changes). You could make simple schema changes but script out the schema definitions and store it in files.

Database Diagram

How to programmatically work with Database Diagram object:
create, drop, add/remove tables...

Thanks, Sergey.

Hi Sergey

Unfortunately with latest version of Database Diagrams you cannot programmatically call into the Database Diagrams objects. This is something that we are looking into for future releases. At this point your best option would be to generate a DDL script and manipulate the table (object) in that fashion

Regards

Mairad

|||

Is there any way of saving diagrams outside the database?

Every time I re-install my database, I lose the diagram and have to manually re-create it.

Even copying the entire database doesn't copy the diagram.

|||

The database diagrams are stored in 'dbo.sysdiagrams'. Each schema is stored in a single row with the internal diagram structure serialized in the 'definition' column i.e. blob.

While is would be very difficult modifying this data you can definitely save and restore the blob.

Database Diagram

How to programmatically work with Database Diagram object:
create, drop, add/remove tables...

Thanks, Sergey.

Hi Sergey

Unfortunately with latest version of Database Diagrams you cannot programmatically call into the Database Diagrams objects. This is something that we are looking into for future releases. At this point your best option would be to generate a DDL script and manipulate the table (object) in that fashion

Regards

Mairad

|||

Is there any way of saving diagrams outside the database?

Every time I re-install my database, I lose the diagram and have to manually re-create it.

Even copying the entire database doesn't copy the diagram.

|||

The database diagrams are stored in 'dbo.sysdiagrams'. Each schema is stored in a single row with the internal diagram structure serialized in the 'definition' column i.e. blob.

While is would be very difficult modifying this data you can definitely save and restore the blob.

Database Diagram

How to programmatically work with Database Diagram object:
create, drop, add/remove tables...

Thanks, Sergey.

Hi Sergey

Unfortunately with latest version of Database Diagrams you cannot programmatically call into the Database Diagrams objects. This is something that we are looking into for future releases. At this point your best option would be to generate a DDL script and manipulate the table (object) in that fashion

Regards

Mairad

|||

Is there any way of saving diagrams outside the database?

Every time I re-install my database, I lose the diagram and have to manually re-create it.

Even copying the entire database doesn't copy the diagram.

|||

The database diagrams are stored in 'dbo.sysdiagrams'. Each schema is stored in a single row with the internal diagram structure serialized in the 'definition' column i.e. blob.

While is would be very difficult modifying this data you can definitely save and restore the blob.

Database Diagram

How to programmatically work with Database Diagram object:
create, drop, add/remove tables...

Thanks, Sergey.

Hi Sergey

Unfortunately with latest version of Database Diagrams you cannot programmatically call into the Database Diagrams objects. This is something that we are looking into for future releases. At this point your best option would be to generate a DDL script and manipulate the table (object) in that fashion

Regards

Mairad

|||

Is there any way of saving diagrams outside the database?

Every time I re-install my database, I lose the diagram and have to manually re-create it.

Even copying the entire database doesn't copy the diagram.

|||

The database diagrams are stored in 'dbo.sysdiagrams'. Each schema is stored in a single row with the internal diagram structure serialized in the 'definition' column i.e. blob.

While is would be very difficult modifying this data you can definitely save and restore the blob.

database corrupt , I want delete some table

I have database currupt some table , I want to delete / drop table

Example

1. databaseTest Have --> table customer , table payment , table sales

2. table Payment can't select / drop / insert /rename (fail) show message error below

ERROR (Row 0);
Microsoft SQL Server 2000 Windows CE Edition:
"The database file is corrupted. (Database name: "" )"

3.How to drop table Payment (I want to drop and create table again and insert data to new table ) Or How to solution for repare table Payment

Thank You

Suwimol

You can try to recover the database file by using SqlCeEngine.Repair method or the native equivalent.

Reference: http://msdn2.microsoft.com/en-us/library/ms172411.aspx

Thanks

Pragya