I have a replicated database and hundreds of records keep "disappearing" from
one table in particular. The table is a child table storing the answers to a
number of questions. Also;
1. The tables have referential integrity enforced, with cascade delete.
2. The relationships are enforced for replication.
3. There are no stored procedures which delete records from the offending
table.
4. There is no way of manually deleting records directly from the offending
table using the front-end application.
5. The parent tables have exactly the same records in as previously.
6. There are no replication conflicts.
Any ideas?
I am keeping transaction logs for 3 weeks. Is there any way that I can find
out from the transaction logs when the records were deleted? Is there any way
I can find out why the records would have been deleted?
Thanks,
Nigel Taylor
http://www.tinit.co.uk/
Nigel,
you don't say if this is on the publisher or on the subscriber, but I'm
guessing the latter. It could be that some updates are replicated as a
delete-insert pair for transactional replication in ceertain cases (see
http://support.microsoft.com/default...NoWebContent=1).
Perhaps this is your case?
Also I'm not clear why you're using cascading constraints for the subscriber
which is normally treated as RO - or maybe I have misunderstood?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi,
It's an ftp merge replication. I'm not sure where the records are
disappearing from first but the publisher is definately causing the other
subscribers (of which there are about 12) to lose the records too.
The relationships (i.e. cascade delete and enforce for replication) were
automatically created when the subscription was first created from the
publisher.
With the delete-insert pair the records would be recreated wouldn't they?
Thanks,
Nigel Taylor
http://www.tinit.co.uk/
|||In fact, when I use your spBrowseMergeChanges procedure on the offending
table, there are a load more records marked for deletion and without a
replacement!
Nigel Taylor
http://www.tinit.co.uk/
|||Nigel,
as you're using merge, and assuming you have all the related tables being
replicated, I'd modify all relationships to be not enforced for replication.
The delete-insert pairs are a problem for transactional and not merge, but
with merge there are other reasons the FK constraints are not applied (see
http://www.replicationanswers.com/Me...derArticle.asp). This
will simplify the setup somewhat and hopefully allow us to see if this is
the cause of the problems.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Okay, thanks Paul. I've removed the 'enforce relationship for replication'
flag and removed the cascade deletes on the offending tables. Hopefully it'll
work and I can get back to doing something else.
Cheers,
Nigel Taylor
http://www.tinit.co.uk/
Showing posts with label storing. Show all posts
Showing posts with label storing. Show all posts
Wednesday, March 21, 2012
Friday, February 24, 2012
Help withrounding money to 2 decimal places?
I have a table with a money field that had previously been running calculation and storing the data into the database's money field. Since this field supports 4 decimal places, it was storing 4 decimal places worth of data. I have since cleaned up my insert routine to round everything up to two decimal places and it only inserts the rounded values. I now have to go back and update the old data with the two decimal place rule. How would I go about doing this?
OLD--------NEW
15.1456 ================ 15.15
4.1328 ================== 4.13
5.16 =================== 5.16How about using this function ... http://thedailywtf.com/archive/2004/10/25/2882.aspx|||Nevermind, this was extremely easy ... perhaps I should have read the BOL first!
Update tblName
Set fldName = round(fldName, 2)|||How about using this function ... http://thedailywtf.com/archive/2004/10/25/2882.aspxThis isn't help. Granted, the question has an elementary solution; you could have simply stated such and gotten your point across. Thanks for the condescending insight to your personality :mad:|||Nevermind, this was extremely easy ... perhaps I should have read the BOL first!
Update tblName
Set fldName = round(fldName, 2)Just as an FYI, Transact-SQL always rounds values ending in 5 away from zero. The statistically correct answer is to round the result to the even value (so some go up and some go down). This isn't a huge deal, unless you are doing statistically significant numbers of operations on values that end in 5. As an example:Original -1.5 -0.5 0.5 1.5 2.5 3.5
T-SQL -2.0 -1.0 1.0 2.0 3.0 4.0
Correct -2.0 0.0 0.0 2.0 2.0 4.0-PatP|||Just as an FYI, Transact-SQL always rounds values ending in 5 away from zero. The statistically correct answer is to round the result to the even value (so some go up and some go down). This isn't a huge deal, unless you are doing statistically significant numbers of operations on values that end in 5. As an example:Original -1.5 -0.5 0.5 1.5 2.5 3.5
T-SQL -2.0 -1.0 1.0 2.0 3.0 4.0
Correct -2.0 0.0 0.0 2.0 2.0 4.0-PatPI'm using SQL Server 2000, which requires that when using the Round() function that you use the length argument. Not sure about previous SQL implimentations.
Syntax
ROUND ( numeric_expression , length [ , function ] )
So, Round(1.5) will give an error because it requires 2 or 3 arguments. However, Round(1.5, 2) will give 1.5 whereas Round(1.5, 0) will return 2.0|||This is probably making a mountain out of a mole-hill, so don't get too wired up in it unless the end result is significant to your database.
Using Transact-SQL, Round(2.5, 0) produces a result of 3. Mathematically (especially significant in statistics), it ought to produce a result of 2 because a value that ends exactly at 5 is supposed to round so that the result is an even digit (a 2 instead of a 3 in this case).
In the case of money (which is what I presume you are working with), any value with exactly a half cent should round to an even number of cents, never to an odd number of cents. VB has handled this correctly for some time (since at least VB 5.0), but Transact-SQL still rounds away from zero which is what the underlying C library does.
This isn't a big deal in most cases. It often makes no difference in the end result since debits and credits are often evenly enough distributed to make the net result balance after the rounding. It does require fiduciary disclosure if it is used for some kinds of financial data.
-PatP|||This is probably making a mountain out of a mole-hill, so don't get too wired up in it unless the end result is significant to your database.
Using Transact-SQL, Round(2.5, 0) produces a result of 3. Mathematically (especially significant in statistics), it ought to produce a result of 2 because a value that ends exactly at 5 is supposed to round so that the result is an even digit (a 2 instead of a 3 in this case).
In the case of money (which is what I presume you are working with), any value with exactly a half cent should round to an even number of cents, never to an odd number of cents. VB has handled this correctly for some time (since at least VB 5.0), but Transact-SQL still rounds away from zero which is what the underlying C library does.
This isn't a big deal in most cases. It often makes no difference in the end result since debits and credits are often evenly enough distributed to make the net result balance after the rounding. It does require fiduciary disclosure if it is used for some kinds of financial data.
-PatPCorrect, I am using a money field and thus I need two decimal places and thus used the length argument of 2. Thanks for all the insight though ... much appreciated!
OLD--------NEW
15.1456 ================ 15.15
4.1328 ================== 4.13
5.16 =================== 5.16How about using this function ... http://thedailywtf.com/archive/2004/10/25/2882.aspx|||Nevermind, this was extremely easy ... perhaps I should have read the BOL first!
Update tblName
Set fldName = round(fldName, 2)|||How about using this function ... http://thedailywtf.com/archive/2004/10/25/2882.aspxThis isn't help. Granted, the question has an elementary solution; you could have simply stated such and gotten your point across. Thanks for the condescending insight to your personality :mad:|||Nevermind, this was extremely easy ... perhaps I should have read the BOL first!
Update tblName
Set fldName = round(fldName, 2)Just as an FYI, Transact-SQL always rounds values ending in 5 away from zero. The statistically correct answer is to round the result to the even value (so some go up and some go down). This isn't a huge deal, unless you are doing statistically significant numbers of operations on values that end in 5. As an example:Original -1.5 -0.5 0.5 1.5 2.5 3.5
T-SQL -2.0 -1.0 1.0 2.0 3.0 4.0
Correct -2.0 0.0 0.0 2.0 2.0 4.0-PatP|||Just as an FYI, Transact-SQL always rounds values ending in 5 away from zero. The statistically correct answer is to round the result to the even value (so some go up and some go down). This isn't a huge deal, unless you are doing statistically significant numbers of operations on values that end in 5. As an example:Original -1.5 -0.5 0.5 1.5 2.5 3.5
T-SQL -2.0 -1.0 1.0 2.0 3.0 4.0
Correct -2.0 0.0 0.0 2.0 2.0 4.0-PatPI'm using SQL Server 2000, which requires that when using the Round() function that you use the length argument. Not sure about previous SQL implimentations.
Syntax
ROUND ( numeric_expression , length [ , function ] )
So, Round(1.5) will give an error because it requires 2 or 3 arguments. However, Round(1.5, 2) will give 1.5 whereas Round(1.5, 0) will return 2.0|||This is probably making a mountain out of a mole-hill, so don't get too wired up in it unless the end result is significant to your database.
Using Transact-SQL, Round(2.5, 0) produces a result of 3. Mathematically (especially significant in statistics), it ought to produce a result of 2 because a value that ends exactly at 5 is supposed to round so that the result is an even digit (a 2 instead of a 3 in this case).
In the case of money (which is what I presume you are working with), any value with exactly a half cent should round to an even number of cents, never to an odd number of cents. VB has handled this correctly for some time (since at least VB 5.0), but Transact-SQL still rounds away from zero which is what the underlying C library does.
This isn't a big deal in most cases. It often makes no difference in the end result since debits and credits are often evenly enough distributed to make the net result balance after the rounding. It does require fiduciary disclosure if it is used for some kinds of financial data.
-PatP|||This is probably making a mountain out of a mole-hill, so don't get too wired up in it unless the end result is significant to your database.
Using Transact-SQL, Round(2.5, 0) produces a result of 3. Mathematically (especially significant in statistics), it ought to produce a result of 2 because a value that ends exactly at 5 is supposed to round so that the result is an even digit (a 2 instead of a 3 in this case).
In the case of money (which is what I presume you are working with), any value with exactly a half cent should round to an even number of cents, never to an odd number of cents. VB has handled this correctly for some time (since at least VB 5.0), but Transact-SQL still rounds away from zero which is what the underlying C library does.
This isn't a big deal in most cases. It often makes no difference in the end result since debits and credits are often evenly enough distributed to make the net result balance after the rounding. It does require fiduciary disclosure if it is used for some kinds of financial data.
-PatPCorrect, I am using a money field and thus I need two decimal places and thus used the length argument of 2. Thanks for all the insight though ... much appreciated!
Subscribe to:
Posts (Atom)