XL2010: External Data Sort Order is lost

[lalbatros]

Hello,

I am retrieving data from an SQL Server view to an "External Data tabale" in xl2010.
This view is sorted on the server.
Nevertheless, when the data are retrieved in Excel, the sort order is lost.
(the order is changed when the view sort order is changed on the server)
(only a few records are moved, and always in the same deterministic way)

I guess there might be some setting for data table that allows preserving the original sort order.
Unfortunately, I could not find where or how to keep the sort order.

Would you know how to preverve the data sort order?

Thanks

Michel

 

[Sektor]

View can't be sorted.
For instance:

CREATE View ViewCantBeSorted
AS
    SELECT T1.Col1, T1.Col2 FROM T1 ORDER BY T1.Col1

View won't be returned sorted.

 

[lalbatros]

Thanks Sektor for this good suggestion.
That could be indeed a good reason.

I am using SQL Server 2005.
I checked my views using the "Microsoft SQL Server Management Studio".
With this tool, the records are properly sorted when viewed in the "Results Pane".

In addition, sorting the records in the view produces a result that is almost totally sorted in the Excel data table.
However, some records are not properly placed.
It looks like a bug, but my guess is that some xl settings regarding data tables is involved.
I can't find out which one.

I would be interrested by any other way around.
What would be the best way to get my data table sorted according to my wish?

Thanks,

Michel

 

[RoryA]

Change the extract to use a SQL statement that selects all records from the view and adds an ORDER BY statement.

 

[Sektor]

Although underlying query result in view can't be sorted, you can sort it externally.

CREATE VIEW ViewCantBeSorted
AS
    SELECT T1.Col1, T1.Col2 FROM T1 ORDER BY T1.Col1;

-- This query is sorted
SELECT * FROM ViewCantBeSorted ORDER BY Col1;

Data can be returned sorted by view if you have clustered key. This way data is stored sorted by PRIMARY KEY. Note, however, that if you have composite key, then you'll have sorted data by Col1 column specified in PRIMARY KEY (Col1, Col2), for example.

 

[lalbatros]

Thanks rorya and Sektor.

It was quite a surprise for me that SQL views are not sorted, even though they would contain an ORDER BY clause. I am using Access more often than SQL server!

I tried the suggestion by rorya and it works.
Strange enough, the data collection takes now 3 seconds instead 5.5 seconds.
I get the right sort order and a speed bonus as well!

Thanks,

Michel