September 11, 2008 at 4:35 am
1. Are you sure that the duplicate data is present in the database and not only visible on the reports?
2.If its present in the database you need to identify the primary key which, when created will not allow duplicate data to be entered. You also need to check your validation of your VB application which inserts data into the database to find how why it inserts duplicates.
April 4, 2010 at 11:03 am
Jeff Moden (11/23/2007)
Yep... you're right... database sure can't do any of that And, I'm sure that the VB program does it with much more speed...
Everyone does realize that my statement above was absolutely dripping with sarcasm, correct?
I came very late to this party, but it seemed obvious to me.
I would be supicious about that bulletproof VB - bet it doesn't start off by reading everything from the table into store so that it can check that it's not inserting something that's already there. This seems to me like a complete giveaway:-
Ramoncito Medina (11/23/2007)hrThe thing is all of the extracted files that are inserted into the database have no duplicates, except for the one file that is generated last Nov 5, where our database server was reported stopped responding.
it seems pretty clear that duplicates of already present rows were not checked for when the job ran again after the outage (and that the job uses multiple transactions for the insert - probably using default ADO behaviour).
Also the question about the non-surrogate key was answered accidentally I think by the comment
There are no joins in my expressions, there is a unique index(ID).
which I take to mean that there is a surrogate key and no unique constraint except on that.
Tom
April 4, 2010 at 4:21 pm
Ramoncito Medina (11/20/2007)
What would be the probable cause of the duplication of the records?
Poor design - there is no PK in affected table or PK was poorly defined otherwise no chance of getting duplicate records.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 4, 2010 at 4:27 pm
Ramoncito Medina (11/22/2007)
I enforce the "no duplicates" via my vb program and it is working.It's not joined, the data are directly inserted in the table from a text file via my vb program.
I'm wondering what miraculous chain of events allows vb program to enforce "no duplicates" when vb program is not looking at the target table... is it silent knowledge?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 7, 2010 at 2:50 am
Ramonicto
You provide very lite information about the texfiles and how your VB program works.
But if you like to use VB use it.
I use VB6 and now VB2008.net and ADO.net to import textfiles.
This is a very short description.
The textfiles are imported with bulk insert to a staging table form which I use substring formulas to
extrakt the different fields in the textfile to a temporary table. From that table you can check if rows already exists in the "main" table and avoid to import them.
I have a SQL Server 2008 64 bit and the procedure is very fast and stable and
is scheduled to run every day.
/Gosta
April 7, 2010 at 6:22 am
Gosta Munktell (4/7/2010)
From that table you can check if rows already exists in the "main" table and avoid to import them/Gosta
ahhh... you check against target table, that's cheating Gosta! 😀
I like Ramoncito's magic process the more :laugh:
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 7, 2010 at 9:30 am
Pablo
ahhh... you check against target table, that's cheating Gosta!
I like Ramoncito's magic process the more
I would also like to see the magic process:-)
but I am no witch doctor.:crazy:
/Gosta
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply