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 Integrity

Hi,

I have a table of products and a table of price bands. e.g. ( Qty: 1-5 = $2, 6+ = $1 ) The price bands are related to products via a foreign key.

To get a product's data, a stored procedure does something like this....

SELECT * FROM products WHERE productID = @.prodID
If isPriceBands
SELECT * FROM priceBands WHERE productID = @.prodID

The question is, what if somebody updates the price bands of a product during a retrieval of the product's data?

My concern is that the select query could have retrieved 2 out of 3 price bands at the point of the update. Lets say the update has deleted all the original price bands and has replaced them with 3 new ones. Then, wouldn' the select query return 5 price bands?

Obviously this would be incorrect.

What is there to stop a modification of an entity spread over several different tables and rows intruding on a select query?

Is the only way to rule this out to lock the entire database for each read?

Cheers,

I.One possible solution is to use serializable transactions. This makes a shared multiuser resource like a db act as if one person is accessing it at a time. Basically you are putting exclusive locks on the records so that no one can change them.|||Thanks for your reply.

I don't see how a serializable transaction would work unless every query was of that kind. This has got to be out of the question for a simple retrieval of a product.

If only the procedures which updated or deleted parts of a product were to be serializable, I still don't think that would stop them from interupting a retrieval of a product's data thus making the final result set potentially inconsistent with itself.

What I think would be good is if you could put a lock on a stored procedure, x, such that a given stored procedure, y, couldn't run until all current x's had completed.
WT.

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