Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

INSERTED DUPLICATE RECORDS Expand / Collapse
Author
Message
Posted Thursday, November 22, 2007 7:38 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 28, 2013 3:26 PM
Points: 114, Visits: 477
I wasn't sure if assumption C was correct and that you had checked the database itself and there are no duplicates there but I went back and added a second possibility there if that wasn't the case. The program may only be looking for duplicates within the single file OR if you don't truncate/destroy the table before the import, the report may be including data from more than one batch, somehow.
Post #425106
Posted Thursday, November 22, 2007 8:05 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:14 PM
Points: 35,215, Visits: 31,665
The vb program will split the strings and remove unnecessary values such as 0's, and converts strings to datetime.


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... :D

I enforce the "no duplicates" via my vb program and it is working


Ummm... so you checked the table for duplicates, eh? And the table has a Primary Key, or at least a unique index to double check for you, huh?

Dave is correct... either you have duplicates or the report code is wrong. You need to check both.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #425110
Posted Thursday, November 22, 2007 8:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 15, 2008 6:45 PM
Points: 6, Visits: 12
The 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.
Post #425115
Posted Thursday, November 22, 2007 9:01 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 28, 2013 3:26 PM
Points: 114, Visits: 477
Does your program check the database itself for rows that may have been inserted previously?
If it "can't happen", I am guessing not.

That being said, what triggers the VB code to be executed? It may help us to know.

Is it constantly running and checking for the appearance of a file to trigger the import? If so, what causes the file to appear in the target folder? How often does the program check and when does the file get deleted or moved? Could it have been sent to you a second time? Does the program produce logs you can check? Could it happen that you have/had two instances of your VB program in memory? Does the program produce logs you can check? Oh right... asked that.
Post #425120
Posted Friday, November 23, 2007 4:22 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:14 PM
Points: 35,215, Visits: 31,665
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?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #425369
Posted Thursday, September 11, 2008 4:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, May 23, 2009 10:16 PM
Points: 6, Visits: 16
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.

Post #567589
Posted Sunday, April 4, 2010 11:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:11 AM
Points: 7,687, Visits: 9,406
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
Post #896434
Posted Sunday, April 4, 2010 4:21 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
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.
Post #896499
Posted Sunday, April 4, 2010 4:27 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
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.
Post #896501
Posted Wednesday, April 7, 2010 2:50 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 2:26 AM
Points: 204, Visits: 1,336
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
Post #898294
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse