Re: Missing Rows in Destination Table

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

I've add a second step which looks is the same as the first expect it only
returns row's not already in the dest table and it returns 0 row's have tried
it in QA both local and on my PC and get 0 row's. I've also run the orignal
Query on the server and it returns the number of row's DTS transfers run it
else where the missing rows are there, I've check via the GUI and these row's
should be included. I've also ran the Query and got it to reun just this one
row, think I'll run it again but on the server this time and see what
happens.

"andrewharris" wrote:

> Don't believe I can do it directly but might be able to put in a second dts
> step which combine's again selects from the source db but only returns row's
> which aren't in the dest table. i'll give it a go and see what happens.
>
> "Allan Mitchell" wrote:
>
> > OK sounds interesting if the rows are not special.
> >
> > Can you isolate those rows in the select and move only them?
> >
> >
> >
> > --
> >
> > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> > www.SQLDTS.com - The site for all your DTS needs.
> > www.SQLIS.com - SQL Server 2005 Integration Services.
> > www.Konesans.com
> >
> >
> > "andrewharris" <andrewharris@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:B23F7742-3435-45F3-A31B-E4AE95901BCF@xxxxxxxxxxxxxxxx
> > > No worries.
> > >
> > > As I'm not during any transformation just moving the data I couild re-write
> > > it as a select. But it will be be and ugly.
> > >
> > > Yeah in QA you can see the missing row's are they appear to be no differnet
> > > to row's before and after.
> > >
> > > I've deceide to bite the bullet and raise it with MS, I'll post back if I
> > > get an answer to the problem from them, thnaks for the help.
> > >
> > > Andrew
> > >
> > >
> > >
> > > "Allan Mitchell" wrote:
> > >
> > >> Sorry I got carried away with the source being a text file for some reason.
> > >>
> > >> Forget DTS. Can you do this using a plain old fashioned INSERT?
> > >>
> > >> When you issue the SELECT in the Source using QA can you see the rows you want then?
> > >>
> > >>
> > >> --
> > >>
> > >> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> > >> www.SQLDTS.com - The site for all your DTS needs.
> > >> www.SQLIS.com - SQL Server 2005 Integration Services.
> > >> www.Konesans.com
> > >>
> > >>
> > >> "andrewharris" <andrewharris@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > >> news:786B75A7-98B7-41CD-A4E5-8CB8869665BC@xxxxxxxxxxxxxxxx
> > >> > Not very easliy (have to do a manual check)
> > >> > Not sure how I would use a hex editor to check the contents of a SQL
> > >> > Database or where to start looking.
> > >> >
> > >> > I've not tried to enter them manually, but I supect there wouldn't be a
> > >> > problem.
> > >> >
> > >> > I don't have the luxery of being able to remove the offending rows as the
> > >> > source system is our main MIS package.
> > >> >
> > >> > "Allan Mitchell" wrote:
> > >> >
> > >> >> Can you identify the rows?
> > >> >> Can you look at them in a hex editor and see if they are terminated correctly?
> > >> >>
> > >> >> What happens if you tried to enter that row manually?
> > >> >>
> > >> >> What about taking the offending rows out, put them into their own text files?
> > >> >> Still fail to insert?
> > >> >> What about the main file? All Successful?
> > >> >>
> > >> >> --
> > >> >>
> > >> >> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> > >> >> www.SQLDTS.com - The site for all your DTS needs.
> > >> >> www.SQLIS.com - SQL Server 2005 Integration Services.
> > >> >> www.Konesans.com
> > >> >>
> > >> >>
> > >> >> "andrewharris" <andrewharris@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > >> >> news:CEB0F452-9442-4711-AC1E-760D8B0130CE@xxxxxxxxxxxxxxxx
> > >> >> > There's nothing special about the row's, i.e. it will take 2 out of 3 lines
> > >> >> > from a sales order. The indexes are diferent and so are some of the data
> > >> >> > type's i.e. replaced chars with varchar's but the rest are the same. It ius
> > >> >> > set to fail on the first error, but dts isn't giving any errors, which is
> > >> >> > what is confusing.
> > >> >> >
> > >> >> > Andrew
> > >> >> >
> > >> >> > "Allan Mitchell" wrote:
> > >> >> >
> > >> >> >> Is there anyhing special about the rows missing.
> > >> >> >> Are both table EXACTLY the same(indexes, datatypes, constraints etc etc)
> > >> >> >>
> > >> >> >> Set the package to fail on first error
> > >> >> >>
> > >> >> >> --
> > >> >> >>
> > >> >> >> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> > >> >> >> www.SQLDTS.com - The site for all your DTS needs.
> > >> >> >> www.SQLIS.com - SQL Server 2005 Integration Services.
> > >> >> >> www.Konesans.com
> > >> >> >>
> > >> >> >>
> > >> >> >> "andrewharris" <andrewharris@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > >> >> >> news:8E1B239D-5B86-4B09-A92E-01E23A0A6614@xxxxxxxxxxxxxxxx
> > >> >> >> > Sorry forgot to add both source and destination tables are MS Sql Servers
> > >> >> >> > Running 2K with SP3a
> > >> >> >> >
> > >> >> >> > "andrewharris" wrote:
> > >> >> >> >
> > >> >> >> >> I have a SQL Server 2k DTS Package which is missing/ignoring rows when they
> > >> >> >> >> are transferred between tables. The source is a Query covering 15 tables and
> > >> >> >> >> the destination is a single table. When I run the Query in Query Analyzer I
> > >> >> >> >> get 87000 rows return yet when I run it as part of a DTS task the destination
> > >> >> >> >> table only has 81700 or so rows.
> > >> >> >>
> > >> >> >>
> > >> >> >>
> > >> >>
> > >> >>
> > >> >>
> > >>
> > >>
> > >>
> >
> >
> >
.

Relevant Pages

  • Re: SQL Server Database Synchronization
    ... The dest db is an exact copy/replica of the source, no updates will be done on the dest db, the dest db should be updated on a daily basis. ... Server A in a Hospital with ISDN connection, server B at ISP, server A is updated on a daily basis by users, server B is readonly for external users, server B needs all data from Server A. ... > Wayne Snyder, MCDBA, SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: A question on output of flip flop operator (part 2)
    ... > Am i going the correct way by doing the incomplete code below? ... Below's a quick example which also builds a little data structure you may ...
    (comp.lang.perl.misc)
  • Re: Hosting Public DNS
    ... server which will handle all the records for the domain. ... Dest TCP & UDP Port 53, Dest YOUR DNS server address ... source port all FROM Internet ...
    (microsoft.public.windows.server.dns)
  • A question on output of flip flop operator (part 2)
    ... and then using Net::FTP module to transfer files from local directory ... Am i going the correct way by doing the incomplete code below? ...
    (comp.lang.perl.misc)