Have you ever had SQL Server 2008 return a differe

问题:

I am not looking for help with the actual code of my stored procedure (yet anyway), but a client sent me a copy of his SQL Server 2000 database stating that a particular proc was returning incorrect results; I restored the database to my server (SQL Server 2008R2) and ran the proc and it produced the correct results. Keep in mind there is no front-end that could be causing the difference - in both the cases I simply execute the proc thru enterprise manager and management studio.

I am wondering if there are any known differences that might contribute/cause this problem? This particular proc uses a lot of conditional logic, default parameters and even has a cursor to do some looping logic, but any suggestions on possible areas to look at would be helpful.

Edit: to clarify, the number of rows and columns returned are the same, but for a particular column (for certain rows depending on value of a data field), the number is exactly double what it should be. 2008 gives the 'correct' answer. '2000' gives the wrong answer, i.e. the double amount.

回答1:

There are so many things that could cause this problem including (but not limited to) collation and system settings. If we saw the SP we might be able to spot the culprit. In addition to changes in deployments there are differences between versions see

http://msdn.microsoft.com/en-us/library/ms143179(v=sql.100).aspx

and

http://msdn.microsoft.com/en-us/library/ms143179.aspx

回答2:

Views with order by won't be ordered in SQl Server 2008, see here: Create a sorted view in SQL Server 2005 and SQL Server 2008

When you say incorrect results, what do you mean? The Language of the SQl Server installation can also be factor if you pass in dates as strings and you are not using ISO/safe formats

Could also be the ANSI settings from the connection coming in at your client's DB

perhaps they have ANSI_NULLS OFF instead of ON