Linked SQL table giving incorrect data

Question

Post reply

Linked SQL table giving incorrect data

Don Urquhart

SSCrazy

Points: 2423
More actions
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

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

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

Jo Pattyn SSC-Dedicated Points: 32347 More actions · Answer 1

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

Sure that the query is built correctly?

Don Urquhart SSCrazy Points: 2423 More actions · Answer 2

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

Jo Pattyn SSC-Dedicated Points: 32347 More actions · Answer 3

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

Don Urquhart SSCrazy Points: 2423 More actions · Answer 4

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

Jo Pattyn SSC-Dedicated Points: 32347 More actions · Answer 5

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?

Don Urquhart SSCrazy Points: 2423 More actions · Answer 6

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