data table foriegn key problem

I am just learning how to program in SQL, so please be patient with me

ok, here it is, i am getting the following errors with my script when i try to execute, and although i realize that this is not the best style to write my script in, i am trying to learn why this is erroring. the errors are:

Msg 1769, Level 16, State 1, Line 9

Foreign key 'Employee2Job_Title' references invalid column 'Title' in referencing table 'Employee'.

Msg 1750, Level 16, State 0, Line 9

Could not create constraint. See previous errors.

now i realize that the second error is because of the first, and that if i can fix the first, the second will go away. Thanks for the help.

Here is my script

use inventory

go

CREATE TABLE Job_Title

(Job_Title_Title char(25) NOT NULL ,

Job_Title_EEO1_Classification char(25) ,

Job_Title_Job_Description Varchar(45) ,

Job_Title_Exempt_Status Varchar(15)

, PRIMARY KEY (Job_Title_Title)

);

CREATE TABLE Employee

(Employee_Emp_ID integer NOT NULL ,

Employee_last_name varchar(15) ,

Employee_first_name varchar(15) ,

Employee_address varchar(30) ,

Employee_city varchar(15) ,

Employee_state char(2) ,

Employee_Telephone_area_code char(3) ,

Employee_Telephone_number char(8) ,

Employee_EEO1_Classification char(25) ,

Employee_Hire_Date char(8) ,

Employee_Salary char(6) ,

Employee_Gender Varchar(1) ,

Employee_Age char(2) ,

Employee_Title char(25)

, PRIMARY KEY (Employee_Emp_ID)

, constraint Employee2Job_Title FOREIGN KEY (Title

) REFERENCES Job_Title

);

Insert INTO Employee (Emp_ID, Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, EEO1_Classification, Hire_date, Salary, Gender, Age, Title) Values

(1, 'Edelman', 'Glenn', '175 Bishop Lane', 'La Jolla', 'CA', 619, '555-0199', 'Sales Workers', 10/7/2003, 21500.00, 'M', 64, 'Cashier')

Insert INTO Employee (Emp_ID, Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, EEO1_Classification, Hire_date, Salary, Gender, Age, Title) Values

(2, 'McMullen', 'Eric', '762 Church Street', 'Lemon Grove', 'CA', 619, '555-0133', 'Sales Workers', 11/1/2002, 13500.00, 'M', 20, 'Bagger')

Insert INTO Employee (Emp_ID, Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, EEO1_Classification, Hire_date, Salary, Gender, Age, Title) Values

(3, 'Slenj', 'Raj', '123 Torrey Drive', 'North Clairmont', 'CA', 619, '555-0123', 'Officials & Managers', 6/1/2000, 48000.00, 'M', 34, 'Assistant Manager')

Insert INTO Employee (Emp_ID, Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, EEO1_Classification, Hire_date, Salary, Gender, Age, Title) Values

(4, 'Broun', 'Erin', '2045 Parkway Apt 2b', 'Encinitas', 'CA', 760, '555-0100', 'Sales Workers', 3/12/2003, 10530.00, 'F', 24, 'Bagger - 30 hours/wk')

Insert INTO Employee (Emp_ID, Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, EEO1_Classification, Hire_date, Salary, Gender, Age, Title) Values

(5, 'Carpenter', 'Donald', '927 Second Street', 'Encinitas', 'CA', 619, '555-0154', 'Office/Clerical', 11/1/2003, 15000.00, 'M', 18, 'Stocker')

Insert INTO Employee (Emp_ID, Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, EEO1_Classification, Hire_date, Salary, Gender, Age, Title) Values

(6, 'Esquivez', 'David', '10983 North Coast Highway Apt 902', 'Encinitas', 'CA', 760, '555-0108', 'Operatives (Semi skilled)', 7/25/2003, 18500.00, 'M',25, 'Asst. - Butchers & Seafood Specialists')

Insert INTO Employee (Emp_ID, Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, EEO1_Classification, Hire_date, Salary, Gender, Age, Title) Values

(7, 'Sharp', 'Nancy', '10793 Montecino Road', 'Ramona', 'CA', 858, '555-0135', 'Sales Workers', 7/12/2003, 21000.00, 'F', 24, 'Cashier')

Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values

('Accounting Clerk', 'Office/Clerical', 'Computes, Classifies, records, and verifies numerical data for use in maintaining accounting records.', 'Non-Exempt')

Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values

('Assistant store manager', 'Officials & Mangers', 'Supervises and coordintes activities of workers in department of food store. Assist store manager in daily operations of store.', 'Exempt')

Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values

('Bagger', 'Sales Worker', 'Places customer orders in bags. Performs carry out duties for customers.', 'Non-Exempt')

Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values

('Cashier', 'Sales Worker', 'Operates Cash register to itemize and total customers purchases in grocercy store', 'Non-Exempt')

Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values

('Computer Support Specialist', 'Technician', 'Installs, Modifies, and makes minor repairs to personal computer hardware and software systems and provides technical assistance and training to system users.', 'Non-Exempt')

Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values

('Director of Finance & Accounting', 'Officials & Mangers', 'Plans and directs finance and accounting activites for Kudlser Fine Foods.', 'Non-Exempt')

Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values

('Retail Asst. Bakery & Pastry', 'Craft Workers (Skilled)', 'Obtains or prepares Bakery and Pastry items requested by customers in retail food store.', 'Non-Exempt')

Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values

('Retail Asst. Butchers and Seafood Specialists', 'Operatives (Semi skilled)', 'Obtains or prepares Meat and Seafood items requested by customers in retail food store.', 'Non-Exempt')

Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values

('Stocker', 'Office/Clerical', 'Stores, prices, and restocks merchandise displays in store.', 'Non-Exempt')

In your foreign key that you are creating with:

constraint Employee2Job_Title FOREIGN KEY (Title) REFERENCES Job_Title

The column named Title needs to exist in the Employee table on which you are creating the constraint. Then for the column it points to with the REFERENCES part, the needs to be in the format of ReferenceTable(ReferenceTableColumnName)

If I'm guessing right at your tables, I think what you are looking for is more along the lines of

constraint Employee2Job_Title FOREIGN KEY (Employee_Title) REFERENCES Job_Title(Job_Title_Title)

-Sue

|||ok, here is the code i ended up with that cured that problem

CREATE TABLE Job_Title
(Job_Title_Title char(25) NOT NULL ,
Job_Title_EEO1_Classification char(25) ,
Job_Title_Job_Description Varchar(45) ,
Job_Title_Exempt_Status Varchar(15)
, PRIMARY KEY (Job_Title_Title)
);

CREATE TABLE Employee
(Employee_Emp_ID integer NOT NULL ,
Employee_last_name varchar(15) ,
Employee_first_name varchar(15) ,
Employee_address varchar(30) ,
Employee_city varchar(15) ,
Employee_state char(2) ,
Employee_Telephone_area_code char(3) ,
Employee_Telephone_number char(8) ,
Employee_EEO1_Classification char(25) ,
Employee_Hire_Date char(8) ,
Employee_Salary char(6) ,
Employee_Gender Varchar(1) ,
Employee_Age char(2) ,
Employee_Title char(25)
, PRIMARY KEY (Employee_Emp_ID)
, constraint Employee2Job_Title FOREIGN KEY (Employee_Title
) REFERENCES Job_Title
);

but now i am on to new and even more frustrating errors, and i will do some work on them myself, on the morrow, then if i am still having problems, i will again avail myself of this resource.

thanks much sue, your assistance has been invaluable!sql

Data SWITCH partition fails with primary key constraint Error

Hi champs,

I am trying to use SWITCH partitions from one fact table out to another identical table. On some tables this does not work as I get an ERROR conserning primary key constraints; is there a way around this without deleting the primary key constraint?

ERROR:

"SWITCH PARTITION 1 TO my_switch_out_table PARTITION 1 " failed with the following error: "ALTER TABLE SWITCH statement failed. SWITCH is not allowed because source table 'my_fact_table' contains primary key for constraint "

/Many thanks

Please post some sample DDL that demonstrates the problem. It will be easier to suggest the solution. You should also take a look at the BOL topic below:

http://msdn2.microsoft.com/en-gb/library/ms191160.aspx

It lists the table, index and constraint requirements for the switch to work.

|||

I have one table that has a two colums as a PK and this table has a PK constraint to one other table and other constraints to 5 other tables.

I've constructed the "OLD_DATA" table as a exact duplicate, including index, of the source table.

However I cannot create the exact same constraints on the destination table, as these already exists in the database.

when I run the following SWITCH, I get an error that

ALTER TABLE dbo.source_table_fact
SWITCH PARTITION 1
TO dbo.OLD_DATA_source_table_fact
PARTITION 1
go

[Execute SQL Task] Error: Executing the query "ALTER TABLE dbo.Ordination_fact SWITCH PARTITION 1 TO dbo.OLD_DATA_MYtable_fact PARTITION 1 " failed with the following error: "ALTER TABLE SWITCH statement failed. SWITCH is not allowed because source table 'dbo.MYtable_fact' contains primary key for constraint 'FK_fact_Mytable_fact'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

This works fine on most of my fact tables but some SWITCHES will not work.

/Many thanks

|||

Here is the script
/* script start */
USE master
go
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'testdb')
BEGIN
ALTER DATABASE [testdb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE [testdb]
END
go
CREATE DATABASE [testdb]
GO
USE [testdb]
GO

CREATE PARTITION FUNCTION [RangeMonth] (datetime)
AS RANGE RIGHT FOR VALUES (
N'2006-12-01 00:00:00',
N'2007-01-01 00:00:00',
N'2007-02-01 00:00:00',
N'2007-03-01 00:00:00',
N'2007-04-01 00:00:00',
N'2007-05-01 00:00:00'
);
GO

CREATE PARTITION SCHEME [RangeM]
AS PARTITION [RangeMonth] all
TO ([Primary]);

CREATE TABLE dbo.test1
(
A uniqueidentifier NOT NULL,
B uniqueidentifier NULL,
C uniqueidentifier NULL,
ST Datetime NOT NULL,

CONSTRAINT test1_pk
PRIMARY KEY NONCLUSTERED (A ,ST) ON [RangeM] (ST),
) on [RangeM] (ST)
;

CREATE TABLE dbo.Lefttest1
(
A uniqueidentifier NOT NULL,
B uniqueidentifier NULL,
C uniqueidentifier NULL,
ST Datetime NOT NULL,

CONSTRAINT Lefttest1_pk
PRIMARY KEY NONCLUSTERED (A,ST) ON [Primary]
) ON [Primary]
;

CREATE TABLE dbo.test2
(
D uniqueidentifier NOT NULL,
E uniqueidentifier NOT NULL,
F uniqueidentifier NOT NULL,
PartKey Datetime NOT NULL

CONSTRAINT test2_pk
PRIMARY KEY NONCLUSTERED (D,PartKey) ON [RangeM] ([PartKey]),
) ON [RangeM] ([PartKey])
go

CREATE TABLE dbo.Lefttest2
(
D uniqueidentifier NOT NULL,
E uniqueidentifier NOT NULL,
F uniqueidentifier NOT NULL,
PartKey Datetime NOT NULL

CONSTRAINT Lefttest2_pk
PRIMARY KEY NONCLUSTERED (D,PartKey) ON [Primary],
) ON [Primary]

;

CREATE TABLE dbo.test3 (
G uniqueidentifier NOT NULL,
D uniqueidentifier NOT NULL,
PartKey Datetime NOT NULL,
AnotherTime Datetime NOT NULL,

constraint test3_pk
primary key nonclustered (G),

constraint test3_D_PartKey_ref
foreign key (D,PartKey)
references test2(D,PartKey)
on delete cascade
)
;
GO
CREATE TABLE dbo.Lefttest3 (
G uniqueidentifier NOT NULL,
D uniqueidentifier NOT NULL,
PartKey Datetime NOT NULL,
AnotherTime Datetime NOT NULL,

constraint Lefttest3_pk
primary key nonclustered (G) ON [Primary],

constraint Lefttest3_D_PartKey_ref
foreign key (D,PartKey)
references Lefttest2(D,PartKey)
on delete cascade
) ON [Primary]
;
GO

-- Try to switch out the first partition
-- on test1

ALTER TABLE test1
SWITCH PARTITION 1
TO Lefttest1;

-- Try to switch out the first partition
-- on test2
-- Fails with
-- Msg 4967, Level 16, State 1, Line 1
-- ALTER TABLE SWITCH statement failed.
-- SWITCH is not allowed because source table 'testdb.dbo.test2'
-- contains primary key for constraint 'test3_did_ref'.

ALTER TABLE test2
SWITCH PARTITION 1
TO Lefttest2;

Data ordering in a Column

Get day...
I am new to SQL Server. I am building an .Net solution that uses SQL as the Backoffice. I have a Primary key column that uses char as data type. I use the following example as my key values:
1-1-1
1-1-2
1-x-x
1-1-10
The 1-1-10 row is automaticlly sorted after the 1-1-1 row. I do not desire this result. Is there a way to FORCE the sequence of my index (values) model.
Thank you in advance.
Sincerely,
Tony D. Abel
That sort order is correct - "10" follows "1" in char type fields.
If you change it, the single CHAR column you have should be broken into 3
integer columns - and all three be part of the primary key.
You can also create a user defined function to take the CHAR field you have
and break it into three pieces and then build it back together for sorting.
Lets say you break the three parts into @.P1, @.P2 and @.P3
Return the item for sorting as a varchar and build it something like
Right('000'+Cast(@.P1 as Varchar(3)),3)+Right('000'+Cast(@.P2 as
Varchar(3)),3)+Right('000'+Cast(@.P3 as Varchar(3)),3)
That would take 1-1-1 and return 001001001 and 1-1-10 and return 001001010.
They should sort fine in an ORDER by.
Steve
"Tony D. Abel" <tdabel@.sbcglobal.net> wrote in message
news:976B2BDF-9C52-4E96-B376-8495D7B92F37@.microsoft.com...
> Get day...
> I am new to SQL Server. I am building an .Net solution that uses SQL as
the Backoffice. I have a Primary key column that uses char as data type. I
use the following example as my key values:
> 1-1-1
> 1-1-2
> 1-x-x
> 1-1-10
> The 1-1-10 row is automaticlly sorted after the 1-1-1 row. I do not desire
this result. Is there a way to FORCE the sequence of my index (values)
model.
> Thank you in advance.
> Sincerely,
> Tony D. Abel
|||Good day Steve...
Thanks for your reply to my question.
I was hoping that it would be a simple solution to handle in SQL. I will manage the query now on the application side because of the quantity of data that has to be inputted for the SQL backend.
Have a great SQL day.
Sincerely,
Tony

Data ordering in a Column

Get day...
I am new to SQL Server. I am building an .Net solution that uses SQL as the
Backoffice. I have a Primary key column that uses char as data type. I use t
he following example as my key values:
1-1-1
1-1-2
1-x-x
1-1-10
The 1-1-10 row is automaticlly sorted after the 1-1-1 row. I do not desire t
his result. Is there a way to FORCE the sequence of my index (values) model.
Thank you in advance.
Sincerely,
Tony D. AbelThat sort order is correct - "10" follows "1" in char type fields.
If you change it, the single CHAR column you have should be broken into 3
integer columns - and all three be part of the primary key.
You can also create a user defined function to take the CHAR field you have
and break it into three pieces and then build it back together for sorting.
Lets say you break the three parts into @.P1, @.P2 and @.P3
Return the item for sorting as a varchar and build it something like
Right('000'+Cast(@.P1 as Varchar(3)),3)+Right('000'+Cast(@.P2 as
Varchar(3)),3)+Right('000'+Cast(@.P3 as Varchar(3)),3)
That would take 1-1-1 and return 001001001 and 1-1-10 and return 001001010.
They should sort fine in an ORDER by.
Steve
"Tony D. Abel" <tdabel@.sbcglobal.net> wrote in message
news:976B2BDF-9C52-4E96-B376-8495D7B92F37@.microsoft.com...
> Get day...
> I am new to SQL Server. I am building an .Net solution that uses SQL as
the Backoffice. I have a Primary key column that uses char as data type. I
use the following example as my key values:
> 1-1-1
> 1-1-2
> 1-x-x
> 1-1-10
> The 1-1-10 row is automaticlly sorted after the 1-1-1 row. I do not desire
this result. Is there a way to FORCE the sequence of my index (values)
model.
> Thank you in advance.
> Sincerely,
> Tony D. Abel|||Good day Steve...
Thanks for your reply to my question.
I was hoping that it would be a simple solution to handle in SQL. I will man
age the query now on the application side because of the quantity of data th
at has to be inputted for the SQL backend.
Have a great SQL day.
Sincerely,
Tony

Data ordering in a Column

Get day..
I am new to SQL Server. I am building an .Net solution that uses SQL as the Backoffice. I have a Primary key column that uses char as data type. I use the following example as my key values
1-1-
1-1-
1-x-
1-1-1
The 1-1-10 row is automaticlly sorted after the 1-1-1 row. I do not desire this result. Is there a way to FORCE the sequence of my index (values) model
Thank you in advance
Sincerely
Tony D. AbelThat sort order is correct - "10" follows "1" in char type fields.
If you change it, the single CHAR column you have should be broken into 3
integer columns - and all three be part of the primary key.
You can also create a user defined function to take the CHAR field you have
and break it into three pieces and then build it back together for sorting.
Lets say you break the three parts into @.P1, @.P2 and @.P3
Return the item for sorting as a varchar and build it something like
Right('000'+Cast(@.P1 as Varchar(3)),3)+Right('000'+Cast(@.P2 as
Varchar(3)),3)+Right('000'+Cast(@.P3 as Varchar(3)),3)
That would take 1-1-1 and return 001001001 and 1-1-10 and return 001001010.
They should sort fine in an ORDER by.
Steve
"Tony D. Abel" <tdabel@.sbcglobal.net> wrote in message
news:976B2BDF-9C52-4E96-B376-8495D7B92F37@.microsoft.com...
> Get day...
> I am new to SQL Server. I am building an .Net solution that uses SQL as
the Backoffice. I have a Primary key column that uses char as data type. I
use the following example as my key values:
> 1-1-1
> 1-1-2
> 1-x-x
> 1-1-10
> The 1-1-10 row is automaticlly sorted after the 1-1-1 row. I do not desire
this result. Is there a way to FORCE the sequence of my index (values)
model.
> Thank you in advance.
> Sincerely,
> Tony D. Abel

Data Model off a view without a primary key

I am trying to bind the data view off of a view that is a
monster, I dont want to make it an indexed view, is there
anyway I can bind to this view without a primary key?
When I try to currently it gives a primary key required
error.
Can I schema bind my view or something? It is a SQL
Server 2005 database.In the model designer you can open data source view editor and assign
primary key to the view.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Greg" <gfricke@.gmail.com> wrote in message
news:1124216552.895088.277620@.g47g2000cwa.googlegroups.com...
>I am trying to bind the data view off of a view that is a
> monster, I dont want to make it an indexed view, is there
> anyway I can bind to this view without a primary key?
> When I try to currently it gives a primary key required
> error.
> Can I schema bind my view or something? It is a SQL
> Server 2005 database.
>

Data migration using Import/export wizard SQL 2000 to SQL 2005

I am trying to import data from SQL 2000 to SQL 2005

The Data schemas are the same in both (tables, relationships, key and triggers).

Using the wizard I am trying to import data from SQL 2000 to SQL 2005. The issue I face is to manage the Idenity columns. I do not want the identity values from the SQL 2000 DB which may be at say 5000, instead I want to use the identity values from the SQL 2005 database which will problably start at 1

Is there a way to do this, should I use a custom query instead of a direct table dump. If using a custom query do I need to just specify a Select query or does it have to be a specific "INSERT INTO Table ..." type query

If I do import the data directly with Identity "ON" how can I advance the identity column value to current +1 on the SQL 2005 table.

Thanks

javahar

You need to ignore the IDENTITY column in the source table. Use a custom query to specify the remaining columns.|||Could you give me diffrent kinds of query of Data migration using DTS package 2000 to 2005.

Data migration using Import/export wizard SQL 2000 to SQL 2005

I am trying to import data from SQL 2000 to SQL 2005

The Data schemas are the same in both (tables, relationships, key and triggers).

Using the wizard I am trying to import data from SQL 2000 to SQL 2005. The issue I face is to manage the Idenity columns. I do not want the identity values from the SQL 2000 DB which may be at say 5000, instead I want to use the identity values from the SQL 2005 database which will problably start at 1

Is there a way to do this, should I use a custom query instead of a direct table dump. If using a custom query do I need to just specify a Select query or does it have to be a specific "INSERT INTO Table ..." type query

If I do import the data directly with Identity "ON" how can I advance the identity column value to current +1 on the SQL 2005 table.

Thanks

javahar

You need to ignore the IDENTITY column in the source table. Use a custom query to specify the remaining columns.|||Could you give me diffrent kinds of query of Data migration using DTS package 2000 to 2005.

Data Migration

Hi friends,
In my project I have redesigned my database structure. In the existing
structure there is no Primary key and no relationship b/w data.
In the new structure Primary key and the relationship is added.
Now I want to migrate the existing data into the new structure.
There is a chance for duplicate records and also records that does not
satisfy referential integrity.
How to migrate the data? I want to have a copy of the duplicate records and
also the records which does not satisfy referential integrity.
Its a huge database, so i can't query table by table to find the mismatch
records.
How to proceed?
thanks
vanithaHi,
Let me know can you consider the following:
I assume that you have two database one is normalized and other one is not
normialized.
1. Backup the Both Databases.
2. Add err_text column to all tables in non-normolized DB.
3. Write a store procedure with the following logic:
a. Select a record and try inserting to Normolized DBs table ,
if no error occurs delete the same from Non-Normolized table,
otherwise
updated err_text column of respective table in non-normolized db
with the
error
b. perform step (a) for each record in every table in non-normlized DB.
4. After the step3 what ever record exists in table of non-normolized db
either they
are passed Primary or referential integrity.|||Vanitha
There are many approaches to this. some are very trickey too.
1)One thing that you can easily do is to update new tables and compare with
old one find unmatched rows using pimary key.( u can use dynamic query to
supply table names while comparing)
2) You can use sp_MSforeachtble: You have to write something dynamically to
abtain all duplicate rows
3) write a procedure (using cursor perhaps) to take columns from
information.schema.columns where for each table and supply those columns to
find out duplicat rows using group by and count(*) >1 as you usually do.
4) Use a sproc to insert row by row to another table and insert into
errortable whereever there is an error using @.@.error <>. This approach gives
coplete rows for which inserts failed.
but 2 and 3 only gives duplicate rows and does not check other constriants
Hope This will give you a lead. though does not solve
Regards
R.D
"Vanitha" wrote:

> Hi friends,
> In my project I have redesigned my database structure. In the existing
> structure there is no Primary key and no relationship b/w data.
> In the new structure Primary key and the relationship is added.
> Now I want to migrate the existing data into the new structure.
> There is a chance for duplicate records and also records that does not
> satisfy referential integrity.
> How to migrate the data? I want to have a copy of the duplicate records an
d
> also the records which does not satisfy referential integrity.
> Its a huge database, so i can't query table by table to find the mismatch
> records.
> How to proceed?
> thanks
> vanitha
>|||I can't add a column to the existing table.
Can I add a new table into the existing database called "errorTable" and the
n
insert the table_name, column data and the error description.
pls advice on this.
thanks
"SlowLearner" wrote:

> Hi,
> Let me know can you consider the following:
> I assume that you have two database one is normalized and other one is not
> normialized.
> 1. Backup the Both Databases.
> 2. Add err_text column to all tables in non-normolized DB.
> 3. Write a store procedure with the following logic:
> a. Select a record and try inserting to Normolized DBs table ,
> if no error occurs delete the same from Non-Normolized table,
> otherwise
> updated err_text column of respective table in non-normolized db
> with the
> error
> b. perform step (a) for each record in every table in non-normlized DB
.
> 4. After the step3 what ever record exists in table of non-normolized db
> either they
> are passed Primary or referential integrity.
>|||there are millions of records, so I can't select each and every record and
check the errors.
I want to do this in bulk.
pls help me to solve this.
thanks
vanitha
"SlowLearner" wrote:

> Hi,
> Let me know can you consider the following:
> I assume that you have two database one is normalized and other one is not
> normialized.
> 1. Backup the Both Databases.
> 2. Add err_text column to all tables in non-normolized DB.
> 3. Write a store procedure with the following logic:
> a. Select a record and try inserting to Normolized DBs table ,
> if no error occurs delete the same from Non-Normolized table,
> otherwise
> updated err_text column of respective table in non-normolized db
> with the
> error
> b. perform step (a) for each record in every table in non-normlized DB
.
> 4. After the step3 what ever record exists in table of non-normolized db
> either they
> are passed Primary or referential integrity.
>|||why dont you create a new table or temp table with the same syntax and add
all uninserted columns data to new error table
Regards
R.D
"R.D" wrote:
> Vanitha
> There are many approaches to this. some are very trickey too.
> 1)One thing that you can easily do is to update new tables and compare wi
th
> old one find unmatched rows using pimary key.( u can use dynamic query to
> supply table names while comparing)
> 2) You can use sp_MSforeachtble: You have to write something dynamically t
o
> abtain all duplicate rows
> 3) write a procedure (using cursor perhaps) to take columns from
> information.schema.columns where for each table and supply those columns t
o
> find out duplicat rows using group by and count(*) >1 as you usually do.
> 4) Use a sproc to insert row by row to another table and insert into
> errortable whereever there is an error using @.@.error <>. This approach giv
es
> coplete rows for which inserts failed.
> but 2 and 3 only gives duplicate rows and does not check other constriants
> Hope This will give you a lead. though does not solve
> Regards
> R.D
>
> "Vanitha" wrote:
>|||There are n number of records... so i can't chk record by record.
is there any way to do in bulk. and if any error during the operation, the
migration shd not be cancelled, it shd log in another table or something lik
e
that.
thanks
vanitha
"R.D" wrote:
> Vanitha
> There are many approaches to this. some are very trickey too.
> 1)One thing that you can easily do is to update new tables and compare wi
th
> old one find unmatched rows using pimary key.( u can use dynamic query to
> supply table names while comparing)
> 2) You can use sp_MSforeachtble: You have to write something dynamically t
o
> abtain all duplicate rows
> 3) write a procedure (using cursor perhaps) to take columns from
> information.schema.columns where for each table and supply those columns t
o
> find out duplicat rows using group by and count(*) >1 as you usually do.
> 4) Use a sproc to insert row by row to another table and insert into
> errortable whereever there is an error using @.@.error <>. This approach giv
es
> coplete rows for which inserts failed.
> but 2 and 3 only gives duplicate rows and does not check other constriants
> Hope This will give you a lead. though does not solve
> Regards
> R.D
>
> "Vanitha" wrote:
>|||TRY THIS
CAN YOU JUST INSERT INTO NEW TABLE AND COMPARE WITH OLD TABLE SO THAT YOU
KNOW WHAT RECORDS ARE NOT INSERTED
this is like this
1) add indentity column in the old table and new table
2) insert into new table2( normalised)
3) insert rows in an order
4) find out which rows are not available in new table using somethinglike
SELECT * FROM TABLE1 LEFT OUTER JOIN TABLE2 ON TABLE.IDENTITY =
TABLE2.IDENTITY WHERE TABLE2.IDENTITY IS NULL
I think this is simple and possible
Regards
R.D
REGARDS
R.D
"Vanitha" wrote:
> There are n number of records... so i can't chk record by record.
> is there any way to do in bulk. and if any error during the operation, the
> migration shd not be cancelled, it shd log in another table or something l
ike
> that.
> thanks
> vanitha
> "R.D" wrote:
>|||OOPS
IN THE SECOND TABLE IDENTIY COLUMN SHOULD ALSO BE INSERTED EXLICITLY OR MAKE
IT A NUMERIC.
sorry there is caps lock pressed on my key board
R.D
"Vanitha" wrote:
> There are n number of records... so i can't chk record by record.
> is there any way to do in bulk. and if any error during the operation, the
> migration shd not be cancelled, it shd log in another table or something l
ike
> that.
> thanks
> vanitha
> "R.D" wrote:
>|||thanks a lot
if any error exits during migration, then it stops the migration process.
how to continue the migration even if error occurs.
thanks
vanitha
"R.D" wrote:
> OOPS
> IN THE SECOND TABLE IDENTIY COLUMN SHOULD ALSO BE INSERTED EXLICITLY OR MA
KE
> IT A NUMERIC.
> sorry there is caps lock pressed on my key board
> R.D
> "Vanitha" wrote:
>

Data Migration

Hi friends,
I have changed the existing database scripts by adding primary key and
foreign key constraints.
Now i have created the new structure, I want to mograte the existing data
into the new structure. There is a chance for duplicate records and also
records that does not satisfy referential integrity.
How to create a populate data from the existing DB and how to avoid the
errors?
thanks a lot
vanithaYou have to eliminate the duplicate records, do you want to delete and
discard them, or do you want to keep them ? You have to provide further
informatione / DDL to help you.
Hth, jens Suessmeyer.
http://www.sqlserver2005.de
--|||hi vanitha,
--You can use this code to scan duplicate keys
use northwind
select orderid from [order details]
group by orderid
having count(orderid)>1
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Vanitha" wrote:

> Hi friends,
> I have changed the existing database scripts by adding primary key and
> foreign key constraints.
> Now i have created the new structure, I want to mograte the existing data
> into the new structure. There is a chance for duplicate records and also
> records that does not satisfy referential integrity.
> How to create a populate data from the existing DB and how to avoid the
> errors?
> thanks a lot
> vanitha|||How to generate the populate script?
thanks
vanitha
"Jose G. de Jesus Jr MCP, MCDBA" wrote:
> hi vanitha,
> --You can use this code to scan duplicate keys
> use northwind
> select orderid from [order details]
> group by orderid
> having count(orderid)>1
> --
> thanks,
> --
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787
>
> "Vanitha" wrote:
>|||Hi
You may want to check the data first to see if this does apply:
e.g. for duplicates
SELECT A.col1, A.col2, A.col3
FROM MyTableA
GROUP BY A.col1, A.col2, A.col3
HAVING COUNT(*) > 1
For a foreign key try something like:
SELECT A.col1, A.col2, A.col3
FROM MyTableA A
WHERE NOT EXISTS ( SELECT * FROM FKTable F WHERE A.col2 = F.col1 )
If you want to eliminate the duplicates when inserting the data use a
DISTINCT clause
INSERT INTO NewTable ( col1, col2, col3 )
SELECT DISTINCT A.col1, A.col2, A.col3
FROM MyTableA
To eliminate those that do not have FKs
INSERT INTO NewTable ( col1, col2, col3 )
SELECT A.col1, A.col2, A.col3
FROM MyTableA A
WHERE EXISTS ( SELECT * FROM FKTable F WHERE A.col2 = F.col1 )
John
"Vanitha" <Vanitha@.discussions.microsoft.com> wrote in message
news:DA29DC9C-1109-491C-9970-E69EBAAB16AC@.microsoft.com...
> Hi friends,
> I have changed the existing database scripts by adding primary key and
> foreign key constraints.
> Now i have created the new structure, I want to mograte the existing data
> into the new structure. There is a chance for duplicate records and also
> records that does not satisfy referential integrity.
> How to create a populate data from the existing DB and how to avoid the
> errors?
> thanks a lot
> vanitha|||insert into destination(orderid,x,y,z) --> this are the insert hint
select orderid,x,y,z from orderdetails -->inserted must match
where orderid not in
(
select orderid from [order details]
group by orderid
having count(orderid)>1
)
process those that are duplicate and insert it afterwards
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Vanitha" wrote:
> How to generate the populate script?
> thanks
> vanitha
> "Jose G. de Jesus Jr MCP, MCDBA" wrote:
>

data lost

Hi,

I have encountered problem in the merge replication with conflict caused by foreign key constraint. As I did some reseach that it can be avoided by increase the upload and download batch generations.

My question is how can I recover the data which has been lost?

Thanks

You can try the mscontents and the conflict tables to say if you can recover the data there if these table has not been cleaned up yet.

I am more interested in how the data was lost. is there any nonconvergence between publisher and subscriber data ?

are you using Sql2000 or SQL2005 ?

thanks

Yunwen

|||When you get a conflict, the info is stored in conflict tables, which you can see in the Replication Conflict Viewer. Conflicts are kept here until you pick which one wins.|||

Thank you for your response.

The conflict message says:"INSERT statement conflicted with COLUMN FOREIGN KEY constraint" so the record can not be inserted in the child table on the subscriber site.

Then somehow the record is also deleted in the child table on the publisher site.

I may be able to get the rowguid from the msmerge_delete_conflicts table, but no more the detail data like stored in the [conflict_replication name_table name]

It seems the data get lost permanently

|||I am using SQL2000|||

If you are using SQL Server 2000 SP4, then please look at the option of 'compensate_for_errors'. This is a paramtere to sp_addmergearticle/sp_changemergearticle.

More info in this article: http://support.microsoft.com/kb/828637

Data Locking Method

Dear All,
I have a receiving application. The app works like below:
1. The operator must key in the delivery notes of the truck at the
receiving offices
2. The operator scans each items within a truck at the warehouse.
After finish unloading the items of one delivery notes (of truck), I
want the operator at the warehouse can't add or delete items anymore.
So the reconciliation report won't be changed in the future.
Does anyone have any suggestion how to solve this problem?
Thanks
Robert Lie
BEGIN TRANSACTION when the delivery is put on the truck for delivery.
COMMIT TRANSACTION when the deliver arrives at the warehouse and is put on
shelf.
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:emexYRTrFHA.2996@.tk2msftngp13.phx.gbl...
> Dear All,
> I have a receiving application. The app works like below:
> 1. The operator must key in the delivery notes of the truck at the
> receiving offices
> 2. The operator scans each items within a truck at the warehouse.
> After finish unloading the items of one delivery notes (of truck), I want
> the operator at the warehouse can't add or delete items anymore.
> So the reconciliation report won't be changed in the future.
> Does anyone have any suggestion how to solve this problem?
> Thanks
> Robert Lie
|||On Tue, 30 Aug 2005 14:37:52 +0700, Robert Lie
<robert.lie24@.gmail.com> wrote:
>I have a receiving application. The app works like below:
>1. The operator must key in the delivery notes of the truck at the
>receiving offices
>2. The operator scans each items within a truck at the warehouse.
>After finish unloading the items of one delivery notes (of truck), I
>want the operator at the warehouse can't add or delete items anymore.
>So the reconciliation report won't be changed in the future.
>Does anyone have any suggestion how to solve this problem?
Not really a data locking issue, it's more about application design, i
think.
J.

Data Locking Method

Dear All,
I have a receiving application. The app works like below:
1. The operator must key in the delivery notes of the truck at the
receiving offices
2. The operator scans each items within a truck at the warehouse.
After finish unloading the items of one delivery notes (of truck), I
want the operator at the warehouse can't add or delete items anymore.
So the reconciliation report won't be changed in the future.
Does anyone have any suggestion how to solve this problem?
Thanks
Robert LieBEGIN TRANSACTION when the delivery is put on the truck for delivery.
COMMIT TRANSACTION when the deliver arrives at the warehouse and is put on
shelf.
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:emexYRTrFHA.2996@.tk2msftngp13.phx.gbl...
> Dear All,
> I have a receiving application. The app works like below:
> 1. The operator must key in the delivery notes of the truck at the
> receiving offices
> 2. The operator scans each items within a truck at the warehouse.
> After finish unloading the items of one delivery notes (of truck), I want
> the operator at the warehouse can't add or delete items anymore.
> So the reconciliation report won't be changed in the future.
> Does anyone have any suggestion how to solve this problem?
> Thanks
> Robert Lie|||On Tue, 30 Aug 2005 14:37:52 +0700, Robert Lie
<robert.lie24@.gmail.com> wrote:
>I have a receiving application. The app works like below:
>1. The operator must key in the delivery notes of the truck at the
>receiving offices
>2. The operator scans each items within a truck at the warehouse.
>After finish unloading the items of one delivery notes (of truck), I
>want the operator at the warehouse can't add or delete items anymore.
>So the reconciliation report won't be changed in the future.
>Does anyone have any suggestion how to solve this problem?
Not really a data locking issue, it's more about application design, i
think.
J.

Data Locking Method

Dear All,
I have a receiving application. The app works like below:
1. The operator must key in the delivery notes of the truck at the
receiving offices
2. The operator scans each items within a truck at the warehouse.
After finish unloading the items of one delivery notes (of truck), I
want the operator at the warehouse can't add or delete items anymore.
So the reconciliation report won't be changed in the future.
Does anyone have any suggestion how to solve this problem?
Thanks
Robert LieBEGIN TRANSACTION when the delivery is put on the truck for delivery.
COMMIT TRANSACTION when the deliver arrives at the warehouse and is put on
shelf.
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:emexYRTrFHA.2996@.tk2msftngp13.phx.gbl...
> Dear All,
> I have a receiving application. The app works like below:
> 1. The operator must key in the delivery notes of the truck at the
> receiving offices
> 2. The operator scans each items within a truck at the warehouse.
> After finish unloading the items of one delivery notes (of truck), I want
> the operator at the warehouse can't add or delete items anymore.
> So the reconciliation report won't be changed in the future.
> Does anyone have any suggestion how to solve this problem?
> Thanks
> Robert Lie|||On Tue, 30 Aug 2005 14:37:52 +0700, Robert Lie
<robert.lie24@.gmail.com> wrote:
>I have a receiving application. The app works like below:
>1. The operator must key in the delivery notes of the truck at the
>receiving offices
>2. The operator scans each items within a truck at the warehouse.
>After finish unloading the items of one delivery notes (of truck), I
>want the operator at the warehouse can't add or delete items anymore.
>So the reconciliation report won't be changed in the future.
>Does anyone have any suggestion how to solve this problem?
Not really a data locking issue, it's more about application design, i
think.
J.

data in sqlserver is not case sensitive

I am facing problems as the data in sqlserver is not case sensitive. The data in parent key may be capital/lower case and the same data in the child table may be lower/capital case. While migrating the data from sqlserver database to other databases(like oracle) its giving error as the data not found in parent key, though the data found in parent table . This is just because the case sensitive in oracle. But according to my knowledge its better if the sqlserver also supports data case sensitive.

Take a small example
Need a table to store all the alphabets in a table
the table structure is

CREATE TABLE [dbo].[ALPHABET] (
[Alphabet] [varchar] (1) NOT NULL ,
[Description] [varchar] (50) NOT NULL
)
GO

The data is

Alphabet

Description

a

Small a

b

Small b

C

Capital C

A

Capital A

sqlserver wont allow to insert data 'A' and gives error "voilation of primary key", though a & A are different according to this table.

I tried with NVARCHAR datatype also. The same problem here also. Sqlserver atleast should support data case sensitivity for NVARCHAR datatype as this can store different languages. May be in other languages the entire meaning may be differ with case differences. Even in english language some words meaning will differ with case differences. For reference can refer english dictionary

change the collation if the field you want to be case-sencetive into SQL_Latin1_General_CP1_CS_AS

Data import - foreign key question

This is a rather abstract question about data design, but I ask it here
because a) the database is SQL Server, and b) you're such a learned
bunch!

Let's assume the classic relation of Customers and Orders, where an
Order may reference a single Customer. If I was designing such a
relation from scratch, I would create the Customer table with an
Identity column and call it CustomerID. The Order table would contain
a column called CustomerID, a foreign key to the Customer table.

So far, so unexceptional. However, in my current project I have to
work with legacy data that comes from a number of old Access systems
where the data was not normalised. I wish to normalise it.

The main table in this new system contains reports on parts. Each
report may reference a single part. However, the old data which I have
to import allowed the user to type in the part number. This has led to
dirty data (for example, '40-7889-9098' appears, as does '40-7889-
9098') so I will clean this data up. In the application, the part
number will be selected from a drop down list, though the administrator
will have access to a builder to add, amend or delete part numbers.

So, my report table needs to store a reference to a part. When I
import the data into my SQL Report table, I will initially bring across
the part number. I will then populate the Part Numbers table with all
discrete, distinct part numbers from the Report table. My question is
should I then create a PartNumberID column in both tables, and "back
populate" the Report table with the PartNumberID which corresponds with
the matching PartNumber - e.g.

UPDATE
R
SET
R.fldPartNumberID = PN.fldPartNumberID
FROM
tblReports R
INNER JOIN tblPartNumbers RN
ON R.fldPartNumber = RN.fldPartNumber

I could then drop the fldPartNumber from the tblReports table.

My question is - should I bother? Or can I just leave the actual
PartNumber in the Reports table, and leave the tblPartNumbers table
with a single column which is both Primary key and Foreign key?

Sorry if this is poorly expressed - I had a tough weekend!

Edward
--
The reading group's reading group:
http://www.bookgroup.org.ukI'm not sure why you say you would leave the PartNumbers table with a
single column. Would it not be better to have a Parts table, whose
primary key is PartNumber? Presumably you have things like names,
descriptions, flags etc. for each part (and if you don't, then you
probably will at some point), so you would need a Parts table anyway.

And what would the difference be between PartNumberID and PartNumber? I
would guess that PartNumber must be unique (after you clean them up,
that is), so why would you want to invent your own ID?

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<1111406898.871386.17820@.g14g2000cwa.googlegroups.c om>...
> I'm not sure why you say you would leave the PartNumbers table with a
> single column. Would it not be better to have a Parts table, whose
> primary key is PartNumber? Presumably you have things like names,
> descriptions, flags etc. for each part (and if you don't, then you
> probably will at some point), so you would need a Parts table anyway.
> And what would the difference be between PartNumberID and PartNumber? I
> would guess that PartNumber must be unique (after you clean them up,
> that is), so why would you want to invent your own ID?

This is exactly my point. The trouble is that I am so used to
creating tables from scratch:

fldPartNumberID
fldPartNumber
fldDescription
fldCurrent
fldOEMID
etc.

that I find myself itching to add the redundant Primary Key, even
though the PartNumber is itself unique or, if it is not, it is unique
in combination with one or more other fields.

Ah, that's the point. I knew I'd get there in the end. If the part
number is NOT unique, but in combination with, say, fldOEMID, then in
order to store a reference to a row in the parts table the Reports
table would need to have TWO foreign keys on the Parts table. So, my
rule of thumb is:

If the PartNumber is unique, it's the ID. If it isn't, it isn't the
ID.

Thanks for your help in clarifying my thoughts.

Edward|||"Edward" <teddysnips@.hotmail.com> wrote in message
news:25080b60.0503211101.73c404b8@.posting.google.c om...
> "Simon Hayes" <sql@.hayes.ch> wrote in message
> news:<1111406898.871386.17820@.g14g2000cwa.googlegroups.c om>...
>> I'm not sure why you say you would leave the PartNumbers table with a
>> single column. Would it not be better to have a Parts table, whose
>> primary key is PartNumber? Presumably you have things like names,
>> descriptions, flags etc. for each part (and if you don't, then you
>> probably will at some point), so you would need a Parts table anyway.
>>
>> And what would the difference be between PartNumberID and PartNumber? I
>> would guess that PartNumber must be unique (after you clean them up,
>> that is), so why would you want to invent your own ID?
> This is exactly my point. The trouble is that I am so used to
> creating tables from scratch:
> fldPartNumberID
> fldPartNumber
> fldDescription
> fldCurrent
> fldOEMID
> etc.
> that I find myself itching to add the redundant Primary Key, even
> though the PartNumber is itself unique or, if it is not, it is unique
> in combination with one or more other fields.
> Ah, that's the point. I knew I'd get there in the end. If the part
> number is NOT unique, but in combination with, say, fldOEMID, then in
> order to store a reference to a row in the parts table the Reports
> table would need to have TWO foreign keys on the Parts table. So, my
> rule of thumb is:
> If the PartNumber is unique, it's the ID. If it isn't, it isn't the
> ID.
> Thanks for your help in clarifying my thoughts.
> Edward

Not exactly - in the case you describe, you would have a composite primary
key on (PartNumber, OEMID):

create table dbo.Parts (
PartNumber char(12) not null,
OEMID int not null,
...
constraint PK_Parts primary key (PartNumber, OEMID)
)

It would be then referenced by one foreign key made up of two columns:

create table dbo.Reports (
ReportID int not null,
PartNumber char(12) not null,
OEMID int not null,
...
constraint PK_Reports primary key (ReportID),
constraint FK_Reports_Parts foreign key (PartNumber, OEMID) references
dbo.Parts (PartNumber, OEMID)
)

This isn't unusual, and in general, you should always use the table's
natural primary key, even if it's made up of several columns, and only
introduce your own when you have a good reason - there's no rule to say that
a primary key must be a single column.

For example, a certain table's natural key might be made up of 10 columns,
including wide varchar columns - that would probably cause performance
problems at some point. So in a situation like that, it's not unreasonable
to introduce your own artificial key, but you would also put a UNIQUE
constraint on the natural key anyway, to make sure the data is valid.

Simon|||teddysnips@.hotmail.com wrote:
>...
> So far, so unexceptional. However, in my current project I have to
> work with legacy data that comes from a number of old Access systems
> where the data was not normalised. I wish to normalise it.
> The main table in this new system contains reports on parts. Each
> report may reference a single part. However, the old data which I
have
> to import allowed the user to type in the part number. This has led
to
> dirty data (for example, '40-7889-9098' appears, as does '40-7889-
> 9098') so I will clean this data up.

>From my perspective, this is where your biggest potential problem lies,
although hidden. I once had to do a similar project where data from
Access tables had to be translated into a different format that could
be imported into a different system. Almost all this was done in code
since it involved complex decisions based on interrelationships of
existing data. The part I didn't like was that one field had to be put
into different categories that didn't exist in the original. The other
fields had nice concrete rules for the conversion. So I created a form
and suggested that their regular employees choose the category from a
combobox for each record similar to the way your application will
select part numbers. All the data coverted smoothly into the new
system except for one of the categories that had been miscatalogued.
The customer wanted me to reduce my invoice based on the grief caused
by having the wrong category. I pointed out that I had selected none
of the categories. Showing which computer was used for the improper
selections would have been even better. Avoid cleaning data yourself
if possible. If not, keep a backup and get the cleanup rules approved.
'Replace' can also do some pretty unexpected things if you're not
careful. Insist that they have someone else check the "clean" data for
final approval.

James A. Fortune

Data handling in sysindexes table

Hi,
I require your help in understanding the way data is maintained in
sysindexes table, I have two doubts,
1) While creating a foreign key constraint for a table, new entry is made in
this table, after dropping this constraint, the entry added with indid equal
to 2 is not deleted from sysindexes table.
2) When we transfer the data from one file group to another, groupid field
value for
for indid equal to 2 still shows the old file group value.
Please execute the following sql statements to simulate the scenario I am
talking about.
create table Department
(DeptId int not null,
DeptName varchar(100))
go
insert into Department
values(1, 'One')
insert into Department
values(2, 'two')
insert into Department
values(3, 'three')
select * from sysindexes
where id = object_id('Department')
-- you will see one entry with indid equal to 0
alter table Department
add constraint PK_DeptId PRIMARY KEY (DeptId)
go
select * from sysindexes
where id = object_id('Department')
-- you will see one entry with indid equal to 1
create table Employee
(EmpId int not null,
EmpName varchar(100),
DeptId int not null)
go
select * from sysindexes
where id = object_id('Employee')
insert into employee
values(1, 'Emp1', 1)
insert into employee
values(2, 'Emp2', 2)
insert into employee
values(3, 'Emp3', 3)
-- you will see one entry with indid equal to 0
alter table Employee
add constraint PK_EmpId PRIMARY KEY (EmpId)
go
alter table Employee
add constraint FK_Emp_DeptId FOREIGN KEY (DeptId)
REFERENCES Department(DeptId)
go
select * from sysindexes
where id = object_id('Employee')
-- you will see two entries with indid values 1 and 2
alter table Employee
drop constraint FK_Emp_DeptId
go
DBCC dbreindex ('Employee', '', 50)
DBCC dbreindex ('Department', '', 50)
select * from sysindexes
where id = object_id('Employee')
-- still you will see those two entries, and indid 2 is not removed from
sysindexes table.
To change the file group, I follow the steps given below, after completing
these steps
groupid value with '2' in sysindexs table for employee object is not getting
updated to
the new file group id value.
1) We drop the employee table's clustered index and recreate clustered index
on the new file group, after this group id field in sysindexes table is
changed
to the new file group in employee table for the row where indid is 1.
2) We drop the foreign key constraint in employee table.
3) We drop the department table's clustered index and recreate clustered
index
on the new file group, after this group id field in sysindexes table is
changed
to the new file group in department table.
4) Foreign key constraint is created again on the employee table by
referring department
table.
5) Execute this statement, select * from sysindexes where id =
object_id('Employee')
6) Groupid field in sysindexes table for employee table against indid value
2 still
shows the old file group id.
This behaviour in sysindexes table is bit confusing, please help me out,
thank you.
Regards,
DevaI think the changes will get reflected when the Server feels that the
statisctics are out of date.
Try
EXEC sp_updatestats
before you check for the change. Its just a guess.
Let me know if this worked.|||Hi,
Data in sysindexes table is not updated even after running
EXEC sp_updatestats
statement, thank you.
Regards,
Deva|||Can you try
DBCC UPDATEUSAGE ('<DBNAME>','<TABLENAME>')
--
"Deva" wrote:

> Hi,
> Data in sysindexes table is not updated even after running
> EXEC sp_updatestats
> statement, thank you.
> Regards,
> Deva
>|||Hi,
Bad luck, even updateusage command does not refresh the data in sysindexes
table, thank you.
Regards,
Deva
"Omnibuzz" wrote:
> Can you try
> DBCC UPDATEUSAGE ('<DBNAME>','<TABLENAME>')
> --
>
>
> "Deva" wrote:
>|||Deva
sp_helpindex 'Employee'
sp_helpconstraint 'Employee'
Yep, it seems to be fixed in SQL Server 2005
select * from sys.indexes
where object_id= object_id('Employee')
Shows only one row
"Deva" <Deva@.discussions.microsoft.com> wrote in message
news:E25567E2-F79B-4516-AD31-6CDC58567F32@.microsoft.com...
> Hi,
> Bad luck, even updateusage command does not refresh the data in sysindexes
> table, thank you.
> Regards,
> Deva
>
> "Omnibuzz" wrote:
>|||1) This is most likely statistics created. What is the name of that "index"?
2) Since statistics doesn't store any pages, the file group is irrelevant. M
ost probably Ms decided
not to change this (dummy) value in case you move the physical table, since
the value should really
be NULL in the first place.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Deva" <Deva@.discussions.microsoft.com> wrote in message
news:E422EDEF-5DA1-4135-97EF-DCA8637E7B63@.microsoft.com...
> Hi,
> I require your help in understanding the way data is maintained in
> sysindexes table, I have two doubts,
> 1) While creating a foreign key constraint for a table, new entry is made
in
> this table, after dropping this constraint, the entry added with indid equ
al
> to 2 is not deleted from sysindexes table.
> 2) When we transfer the data from one file group to another, groupid field
> value for
> for indid equal to 2 still shows the old file group value.
> Please execute the following sql statements to simulate the scenario I am
> talking about.
> create table Department
> (DeptId int not null,
> DeptName varchar(100))
> go
> insert into Department
> values(1, 'One')
> insert into Department
> values(2, 'two')
> insert into Department
> values(3, 'three')
> select * from sysindexes
> where id = object_id('Department')
> -- you will see one entry with indid equal to 0
> alter table Department
> add constraint PK_DeptId PRIMARY KEY (DeptId)
> go
> select * from sysindexes
> where id = object_id('Department')
> -- you will see one entry with indid equal to 1
> create table Employee
> (EmpId int not null,
> EmpName varchar(100),
> DeptId int not null)
> go
> select * from sysindexes
> where id = object_id('Employee')
> insert into employee
> values(1, 'Emp1', 1)
> insert into employee
> values(2, 'Emp2', 2)
> insert into employee
> values(3, 'Emp3', 3)
> -- you will see one entry with indid equal to 0
> alter table Employee
> add constraint PK_EmpId PRIMARY KEY (EmpId)
> go
> alter table Employee
> add constraint FK_Emp_DeptId FOREIGN KEY (DeptId)
> REFERENCES Department(DeptId)
> go
> select * from sysindexes
> where id = object_id('Employee')
> -- you will see two entries with indid values 1 and 2
> alter table Employee
> drop constraint FK_Emp_DeptId
> go
> DBCC dbreindex ('Employee', '', 50)
> DBCC dbreindex ('Department', '', 50)
> select * from sysindexes
> where id = object_id('Employee')
> -- still you will see those two entries, and indid 2 is not removed from
> sysindexes table.
> To change the file group, I follow the steps given below, after completing
> these steps
> groupid value with '2' in sysindexs table for employee object is not getti
ng
> updated to
> the new file group id value.
> 1) We drop the employee table's clustered index and recreate clustered ind
ex
> on the new file group, after this group id field in sysindexes table is
> changed
> to the new file group in employee table for the row where indid is 1.
> 2) We drop the foreign key constraint in employee table.
> 3) We drop the department table's clustered index and recreate clustered
> index
> on the new file group, after this group id field in sysindexes table is
> changed
> to the new file group in department table.
> 4) Foreign key constraint is created again on the employee table by
> referring department
> table.
> 5) Execute this statement, select * from sysindexes where id =
> object_id('Employee')
> 6) Groupid field in sysindexes table for employee table against indid valu
e
> 2 still
> shows the old file group id.
> This behaviour in sysindexes table is bit confusing, please help me out,
> thank you.
> Regards,
> Deva|||On Tue, 9 May 2006 22:26:01 -0700, Deva wrote:

>Hi,
>I require your help in understanding the way data is maintained in
>sysindexes table, I have two doubts,
>1) While creating a foreign key constraint for a table, new entry is made i
n
>this table, after dropping this constraint, the entry added with indid equa
l
>to 2 is not deleted from sysindexes table.
>2) When we transfer the data from one file group to another, groupid field
>value for
>for indid equal to 2 still shows the old file group value.
(snip)
Hi Deva,
The extra row in sysindexes that you see is not for an index, but for
column statistics. If you look at the name column in the output from
SELECT * FROM sysindexes WHERE ...
you'll see that the name start with "_WA_Sys_". For experienced SQL
Server user, this is a clear indication that this is not a real index,
but an entry for column statistics.
I don't think it's documented anywhere, but a more reliable way to
seperate statistics entries from real indexes in sysindexes is to use
the status column:
SELECT *
FROM sysindexes
WHERE id = OBJECT_ID('Employee')
AND status & 64 = 0
Or, even better, don't query the system tables at all. Use the supported
stored procedures instead:
EXEC sp_helpindex 'Employee'
Hugo Kornelis, SQL Server MVP|||Hugo,

> AND status & 64 = 0
How about using INDEXPROPERTY() instead? ;-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:9ga362h8at3nk05gmhtj2hnb5hqfq0sop4@.
4ax.com...
> On Tue, 9 May 2006 22:26:01 -0700, Deva wrote:
>
> (snip)
> Hi Deva,
> The extra row in sysindexes that you see is not for an index, but for
> column statistics. If you look at the name column in the output from
> SELECT * FROM sysindexes WHERE ...
> you'll see that the name start with "_WA_Sys_". For experienced SQL
> Server user, this is a clear indication that this is not a real index,
> but an entry for column statistics.
> I don't think it's documented anywhere, but a more reliable way to
> seperate statistics entries from real indexes in sysindexes is to use
> the status column:
> SELECT *
> FROM sysindexes
> WHERE id = OBJECT_ID('Employee')
> AND status & 64 = 0
> Or, even better, don't query the system tables at all. Use the supported
> stored procedures instead:
> EXEC sp_helpindex 'Employee'
> --
> Hugo Kornelis, SQL Server MVP|||On Wed, 10 May 2006 18:58:09 +0200, Tibor Karaszi wrote:

>Hugo,
>
>How about using INDEXPROPERTY() instead? ;-)
Hi Tibor,
Of course! Much better. (sound of hand slapping forehead)
That's what I get for giving up on BOL in frustration and simply
checking sp_helptext sp_helpindex...
Hugo Kornelis, SQL Server MVP