Post reply

Linked SQL table giving incorrect data

  • December 4, 2008 at 3:10 pm

    #75382

    After setting up a new view on a large table in SQL2000, I was unhappy with performance so I ran the SQL2000 Index tuning wizard and took it's recommendation which was to create a clustered index on the table. Query performance then was great from Ent.Mgr. or Query Analyzer.

    Our Planning Mgr. wanted to use the data so he linked in the view to Access2007, but when he ran queries the data was not the same as it was in SQL. We seemed to get the same number of records for a subset of data but, for each group of data that matched the 3 field key on the clustered index we received records that for the most part were all exactly the same for all other fields. The underlying data has many differences in these other fields but this wasn't reflected in the query.

    After dropping the clustered index the data can be retrieved properly but the performance sucks!

    My SQL 2000 is fully patched, as is the Office2007, and the OS's on both the server and desktop.

    Can anyone tell me why I'm getting this behaviour? I want to put the index back but it is useless if the user's can't get the proper data.

    If you don't stand for something, you'll fall for anything!,

    Don Urquhart

  • December 9, 2008 at 3:07 pm

    #909980

    Odd that adding an index would cause duplicate records to appear.

    Sure that the query is built correctly?

  • December 10, 2008 at 9:03 am

    #910454

    Yes the query is good. I have tried rebuilding it, building it on a different machine and database, relinked the table, and deleted it and recreated it all from scratch.

    As soon as I drop the clustered index I get the correct results from the query.

    If you don't stand for something, you'll fall for anything!,

    Don Urquhart

  • December 10, 2008 at 4:13 pm

    #910736

    Could you post some sample data and the query to recreate the problem?

  • December 11, 2008 at 12:55 pm

    #911297

    Hi Jo,

    I am attaching a small sample of the data (100 of 10 million+).

    I'm not sure what query you are requesting but in Access it is just a simple query to pull in all fields, using the * field reference with a sort by eBisStoreNumber and SalesDate. Didn't keep the query unfortunately

    The Access link is to a SQL view. The query for it is:

    Create VIEW dbo.#Mytable_View

    AS

    SELECT TOP 100 PERCENT det.*

    FROM dbo.#myTable det INNER JOIN

    (SELECT DISTINCT eBisStoreNumber, SalesDate, [Check]

    FROM #MyTable

    WHERE promodesc = 'STAFF') StCh ON det.eBISStoreNumber = StCh.eBisStoreNumber AND det.SalesDate = StCh.SalesDate AND

    det.[CHECK] = StCh.[Check]

    Hope that is what you were looking for.

    Thanks for looking into this.

    If you don't stand for something, you'll fall for anything!,

    Don Urquhart

  • December 11, 2008 at 2:52 pm

    #911358

    Thanks for providing the sample data.

    I didn't find anything odd in query analyzer (still the same 13 records)

    Yet some notes:

    *The view uses TOP 100 PERCENT

    This can be removed as sorting shouldn't be in views, rather specified by the client.

    *The view uses temporary tables (#), one leaving out dbo.

    Are those created by access 2007?

  • December 11, 2008 at 3:11 pm

    #911366

    Thanks,

    I will definitely remove the TOP 100.

    The reference to the temp. table I modified to match the uploaded data table name. The real table is in the actual view, with the missing dbo. reference. FYI: The view is intended to pull all detail for those [CHECK] 'S that match checks with the promo desc. = 'STAFF'

    This is Access 2007.

    The view worked just fine in QA when I had the clustered index or if I didn't. It was only in Access that the problem occurred.

    If you don't stand for something, you'll fall for anything!,

    Don Urquhart

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply