Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 257
  • Last Modified:

Stored Procedure/View order change

I have a stored procedure that creates a pivot table with data from a view in SQL Sever 2000. Neither the sp or the view has changed in months. However for some reason the order in which the data is being returned from the stored procedure has just changed - this is causing huge problems as the output is copied straight to an excel spreadsheet and now its all messed up - the data is the same it is just the order of the data that has changed
Any idea what factors could influence the an order change in a sp/ view where the input data is exactly the same. Indexes is perhaps on thing that I need to look at - can u think of anything else??
0
mydo76
Asked:
mydo76
1 Solution
 
Patrick MatthewsCommented:
On what columns are your data sorted?  What are those columns' data types?  What is different about
the output now vs the output in the past?
0
 
mydo76Author Commented:
one input parameter of the stored procedure is a list of months e.g. '200700', '200801',
'200802', '200803', '200804', '200805' and creates a pivot table where the input yyyymm list are the column names and fiills them with data. eg
'200700', '200801', '200802', '200803', '200804', '200805',
123          111          100          5000       3000        1245.1 (I have to check the type)

this is out to an excel spreadsheet - the return data from the sp should be and was in the fact in this order
'200700', '200801', '200802', '200803', '200804', '200805',
123          111          100          5000       3000        1245.1

but its coming out now as --

'200802' '200805',   '200700', '', '200803', '200804', '200801'
100          1245.1     123               5000       3000        111

ie the data is correct its just the order that is incorrect.
I need to check the colums the data is sorted on etc tomorrow
0
 
Anthony PerkinsCommented:
>>Any idea what factors could influence the an order change in a sp/ view where the input data is exactly the same. <<
The lack of an appropriate ORDER BY clause would explain it.  However without seeing the actual stored procedure, there is no tellin.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
Anthony PerkinsCommented:
I should have also added that unless you are using an ORDER BY clause the order of the data is not guaranteed.
0
 
ursangelCommented:
Yes its True that without proper order by clause, you cannot have a guarantee on the order in which the result is retrieved. SQL server has its on internal method of retrieving the records from the database. May be all these time, the order was what you ver expected, but since it has changed now, make it use ORDER BY Clause.
0
 
mydo76Author Commented:
I fixed this myself !! It was a problem with a temporary table - when I inserted the data to a permanent table it rectified the problem!
0
 
mydo76Author Commented:
I should also add that I put a clustered index on the appropiate field! Thanks for all the suggestions!
0
 
Anthony PerkinsCommented:
>>I should also add that I put a clustered index on the appropiate field!<<
All you have done is mask the problem.  Until you add an ORDER BY clause you have no guarantee that the results will be returned in the same order. Period.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now