same script but different results between SQL 7 and SQL 2000

[NickN]

This is really stumping me. For some reason, the same script is giving different results between the same databases - identical databases, one installed on SQL 7 and the other on SQL 2000. Has there been some changes in SQL 2000 that would change my results? Please help!

THANK YOU!!!

 

[Guest_imported]

Sometimes the order of the process can change what you get in return (how the output looks). It may be that all you need is an 'Order By' statement to make things look right.

Craig A.

 

[NickN]

Why did I not need an order by clause with SQL 7 and then I will need one with SQL 2000? Is there something I over looked upon installation or maybe a change. I do believe my script is processing the records based upon order.

 

[fluteplr]

Dumb luck. By definition in sql row order is indeterminate unless specified.

 

[tlbroadbent]

Do you do a Group By within the SQL code? What is the compatibility level of the database? Did you change the compatibility level during the upgrade?

(NOTE: Use Exec sp_dbcmptlevel @dbname = dbname to determine the compatibility level.)

Under compatibility level 6.5 then the Group By statement ordered the result set. Under compatibility mode 7.0 and greater, the Group By no longer order the results and you must use an explicit Order By.

SQL 7 and SQL 2000 continue Microsoft's effort to bring SQL Server into ANSI compliance. Row order is indeterminate according to the ANSI standard. Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm

 

[NickN]

Terry,

I checked the compatibility of my databases. The compatibility of the model, master and msdb on SQL 7.0 is 70 and on my sql 2000 server it is 80 for all. However, the database that I am currently using to process records was originally created on 6.5 thus it has a compatibility of 65. This database was then (somehow - I wasn't around then) upgraded to 7.0 although the compatibility level is still 65. What I did is moved this database to SQL2000 on WIN2000. I did this by backing up the database and restoring it on the new server using SQL2000. However, the compatibility is still 65. Does this matter?

And yes, there were many many group by clauses in this specific procedure. I have tried adding an order by clause but it doesn't seem to give me the results of the old 7.0 box.

 

[tlbroadbent]

Are the result differences only the order of the set or do you see additional differences? Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm

 

[NickN]

Yes, the order is different. The order remains the same until a certain procedure is run (in all about 20 procedures process this data). After this point the order is different for certain fields but not all.

As well, some of the dates are occasionally incorrect in only 2 fields. These dates should match the older database's (SQL7.0) dates.

 

[tlbroadbent]

If the database is set to level 65 in SQL 7 and SQL 2000, the Group By statements should return ordered sets. There may be some other issue involved in the results you see. Check SQL BOL for more info about differences in compatibility levels. A good place to start is the page about sp_dbcmptlevel. You can also find it online at...

http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_da-di_5c8c.asp

I'll do some more research to see if I can find additional reasons for the results you get. Perhaps you could send me some of the SQL code where the differences appear. Maybe, I'll be able to see something in the code.

tlbroadbent@hotmail.com Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm