Solved

Stored Procedure/View order change

Posted on 2008-10-29
8
247 Views
Last Modified: 2012-05-05
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
Comment
Question by:mydo76
8 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22836113
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
 

Author Comment

by:mydo76
ID: 22836700
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22837136
>>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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22837139
I should have also added that unless you are using an ORDER BY clause the order of the data is not guaranteed.
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 5

Expert Comment

by:ursangel
ID: 22841529
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
 

Author Comment

by:mydo76
ID: 22842641
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
 

Accepted Solution

by:
mydo76 earned 0 total points
ID: 22842668
I should also add that I put a clustered index on the appropiate field! Thanks for all the suggestions!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22846525
>>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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Dynamics crm 2011 8 46
MS SQL Bulk load data error 5 34
SQL Split character from numbers 3 18
shrink datafile Sql server 4 18
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now