Click here to Skip to main content
15,867,834 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more: , +
Hi

From past one week I have been facing with a strange issue.I did lot more analysis and I come up with few findings.But those were went on top of my head.So I am posting here for suggestions / help.

We are migrating from 2000 to 2012 sql server database.In one of the procedure ,we have seen there was a logic which was purely written on order based.

Logic is : 1. Storing the data into temp table using order by id clause.

SQL
SELECT  id,col2,col3
	INTO	#tmp_equations
	FROM	table1 EQ WITH (NOLOCK)
			INNER JOIN table2  ELU WITH (NOLOCK) ON EQ.EQ_Rate = ELU.[ID] 
	ORDER BY col3,ID	


2.A cursor created on this table but unfortunately there is no order by in cursor declaration.
SQL
DECLARE frame CURSOR FOR
	SELECT	[ID] 
	FROM	#tmp_equations 


3.based on the cursor data,logic is performing to insert the data into master table with some business logic. The logic is handled in such way that previous id will be source for current row..

Now we have deployed the same SP in 2012.But we see different result set in step 3.
While analysing we observe that whatever the data is stored in step 1 temp table ,the same order is cursor taking for step 3.

But in step1, the stored result is different order in 2000 and 2012 like

C#
	in 2000, id's will be stored like 1,2,3,144,145,230....1000(random order)
in 2012 ,id's will be stored like  1000,999,850,849,....10,99(random order)


We have highly restriction that we cannot change the SP in 2000 by adding order by id in cursor which impacts business.So could any one share some suggestions/ideas to handle this in sql 2012.

Thanks in Advance
Posted
Comments
Tomas Takac 26-Nov-15 5:48am    
I don't understand what's the problem. If you are migrating why you don't add the order by? Migration usually requires changes. Without order by the order of the result is not defined, could be anything. That it was working in 2000 is a side effect, luck if you wish. So this is akin fixing a bug really.
pratap420 26-Nov-15 5:51am    
If I add order by in cursor,the result set will be different in 2012.But business team still referring 2000 db only.And they expecting same data in 2012.
Tomas Takac 26-Nov-15 5:58am    
My understanding is that it works in 2000 because the data is ordered correctly i.e. the cursor fetches the rows in the same order as they were inserted in the temp table. In that case ORDER BY col3,ID should solve the problem. Otherwise if the order is truly random I see no way how to replicate the same in 2012.
pratap420 26-Nov-15 6:06am    
Order by col3,id is using in 2012 also.But storage order in temp table different with respect to 2000.
Tomas Takac 26-Nov-15 7:40am    
What I'm saying is you must use order by in the cursor otherwise the order is undefined. It doesn't matter that you used order by to insert the rows into the temp table.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900