locked
duplicate inserts into sql RRS feed

  • Question

  • User531388329 posted

    I have spent too many hours trying to track down the cause of duplicate inserts from my logparser query.

    Here is the query:

    SELECT 'NETROCA02' as WMSName, 'truthradio' as PPName,
    QUANTIZE(TO_TIMESTAMP(date, time), 3600) as hours,
    sum(REPLACE_IF_NULL(to_int(sc-bytes),0)) as bytesSent,
    0 as maxConnects, 0 as maxDuration,
    0 as avgDuration 
    from c:\windows\system32\logfiles\wms\truthradio\wms_*.log  to statTotals
    where
    to_timestamp(date,time) >= to_timestamp('2004-01-01 00:00:01','yyyy-MM-dd hh:mm:ss') 
    and
    to_timestamp(date,time) <= QUANTIZE(to_timestamp('2004-02-16 15:59:59','yyyy-MM-dd hh:mm:ss'), 3600)
    and c-status='200' group by hours

    Here is a sample output to sql

    NETROCA02 truthradio 2/15/2004 4:00:00 AM 82609359 0 0 0

    I empty the sql table, run this query once from the command line and it writes rows for all the data, grouped by hour, between the two dates...but then it writes them again exactly the same. I have not ran the query twice and I can't see anything in my query that would explain it. (no there are no triggers on the sql table)

    Any ideas?  This is the last issue for this part of my solution and I spent the entire day and evening yesterday trying to sort this out. I'm getting a little desperate.

    thanks

    dave

     

     

     

    Tuesday, August 21, 2007 2:20 AM

All replies

  • User531388329 posted

    Some additional information:

    LogParser 2.1 reports 74 output rows but the total number of SQL rows are 68.

    34 proper and 34 duplicates

    ??? I'm completely flumuxed ???

     

     

    Tuesday, February 17, 2004 8:37 AM
  • User531388329 posted

    Seems to only happen with SQL has output, changing only to -o:csv but nothing else produces a CSV text file that not only does not have duplicates but has correct count of rows (74).

    CSV file contains rows from 2004-02-13 08:00 to 2004-02-16 14:00 with no duplication

    SQL output contains rows from 2004-02-15 16:00 to 2004-02-16 14:00 TWICE

    That seems to indicate that the query is well formed but that there is a problem with the sql output method.

    I'll read up on the ODBC Bulk Add system and see if I can find any clues.

    Dave

     

    Tuesday, February 17, 2004 9:15 AM
  • User531388329 posted

    See Q10022 in the KB section.

    Known problem, fixed in 2.2. Sorry about that

    ------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm

    Tuesday, February 17, 2004 9:33 AM
  • User531388329 posted

    Thank you that is exactly what I was experiencing. One note about the KB article though is that it says behavior is only from CSV to SQL when I am WC3 to SQL and getting the same result.

    I would love to use the posted registry edit workaround until 2.2 comes out so that I could finish development, however I don't have any logParser entries in the registry at all.  I am simply copying logparser.dll to target machines and regsvr32'ng it. 

    I guess I'll try just adding that one key and see what happens

    Dave

     

     

    Tuesday, February 17, 2004 9:53 AM
  • User531388329 posted

    No dice, I added hkcu\software\microsoft\logparser\SQlOutPutMultithreaded dword 0

    but I still get the same behavior, I tried re registering the logparser.dll but still didn't take. I don't need to reboot do I?

    If it was simply the duplicates I would run a delete dupes job after each read but the missing rows would still be a problem.

    I guess I could also try outputing my grouped rows to CSv which is working then send from that CSV to SQL and see if that works.

    Umm any word on 2.2 release date

    I'd hate to spend a bunch of time on an ugly work around and then have 2.2 released the next day, Ya ya I'm sure you can't commit to anything.

     

     

     

     

    Tuesday, February 17, 2004 10:03 AM
  • User531388329 posted

    Whoops my bad typo in the key name...should have been "log parser" not "logparser"

    I made this change but the fix seems to have it's own issue, now I only get the first 40 rows LOL

    <sigh> I guess I'm waiting for 2.2 and I'll have to push back the live date for this project.

    it's a great tool, just my bad luck to hit this bug in a big way.

     

     

    Tuesday, February 17, 2004 10:25 AM
  • User531388329 posted

    OK I worked around this problem in two ways, first I perform all my aggragate queries to CSV on the local machine first, then send this combined file to sql. I also check to see if it's less then 100 rows and if so I add a dummy csv file to the query with the same layout so that I am ALWAYS sending at least 101 rows. Not pretty but should hold till version 2.2

    Thanks for your help

    Tuesday, February 17, 2004 9:32 PM