ODBC load, exec(SELECT TOP) is introducting randomness

[Bruce McDougall]

Hi all

I work in a data lab that prepares data in Stata that is pulled from SSMS using ODBC load.

I am using

ODBC load, exec(SELECT (TOP 5000) varlist FROM etc etc)

The issue I have is that I get a marginally different set of 5000 obs each time. This is really surprisingly and troubling, as I want to check my work by comparing old end results to new. I can't if randomness is introduced. Even stranger, for some tables in SSMS this seems to happen, but not others. I checked to see if perhaps some of them had internal sorting in SSMS and others not, but this doesn't seem to be the cause of the problem.

Unfortunately I can't provide example code as the data sits in SSMS.

Does anyone have any ideas?

Regards,
Bruce

[Leonardo Guizzetti]

I’m no expert with SQL, but I don’t think this is a Stata issue. With SQL (generic), I don’t think there is any expectation for data to be returned or accessed with any specific order, unless enforced some how (such as a selection on a range or sorting result sets).

To use this example for Transact SQL, it specifically mentions the results will be returned in an undefined order unless an ORDER BY clause is included in the query.

[Bruce McDougall]

Hi Leonardo

Thanks for your answer. That is interesting and still I find it a bit odd. Most of my colleagues are under the impression TOP always gives the same top x, being whatever x rows is on the top of the heap. That seems simpler than drawing a random x rows. According to the link, at least in the context of Transact SQL, we may be wrong about this. We are using ODBC load to query a SSMS database that is in .mdf+.ldf format.

I remain confused as for some tables I do get the same top x rows each time.

All the best,
Bruce

[Leonardo Guizzetti]

Sorry I can’t help further. I recommend diving into the documento for your specific flavour of SQL and see what you can learn.

[Cameron Wellard]

Hi Bruce,
Not sure whether you have resolved this. If you have I'd be interested to hear what the problem was. I've also had an issue getting random results from an ODBC call to a sequel server database (I've posted about it under 'Problem importing dates using and ODBC call to SQL server', I don't know how to link the thread). Essentially I find that values of dates are sometimes returned reliably, and sometimes the values are missing. This seems to occur at random. Have you found the cause of your issue?

C.