Database

24-May-08 23:54

Wrong tool. An sql query will return you the data, not format it for consumption.

You need to manage the result set in your presentation layer rather than attempt to make the sql query do the job.

Never underestimate the power of human stupidity
RAH

Whats wrong in sql2005

AlexeiXX323-May-08 20:01

23-May-08 20:01

Hi, thanks for looking

I am upgrading my app from 2000 to 2005
Some SPs that im using dont return the rows in the expected sort anymore

I have SPs calling views, the sorting is applied in the view and the sp just filters the view
When i was using sql2000 the sorting was working right (as set in the view), but now in sql2005 rows are not returned in the correct sort
Do i have to specify an order by clause in each SP Or is there any setting that i must set??

Thanks in advance

Alexei Rodriguez

Mycroft Holmes25-May-08 0:00

25-May-08 0:00

When adding the order by clause to the view you will get the following error:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.<br />

So I would figure all your sps will require an order by in their select. BTW I would not use top 100% just to get the order by into the view.

Never underestimate the power of human stupidity
RAH

AlexeiXX325-May-08 12:13

25-May-08 12:13

Thanks for your answer

What a huge change
I wonder what other surprises ill find on sql2005 and later in sql2008 Frown | :-(

I cant beleive that even visual studio server explorer added a wrong workaround, something that as i just read was never supposed to be used inside a view

Alexei Rodriguez

AlexeiXX326-May-08 8:43

26-May-08 8:43

I guess i just found the biggest problem with sql2005 Frown | :-(

When generating the scripts, with doesnt care about dependencies and when executing them, you know what happens Frown | :-(

Do you know any way to generate the scripts in the correct order?

Thnans in advance

Alexei Rodriguez

Mycroft Holmes26-May-08 12:27

26-May-08 12:27

I use sql compare (empty database) it does a good job. I have never tried between different versions.

Why not split it logically to table/views/procs then only the procs can be out of sequence.

Never underestimate the power of human stupidity
RAH

AlexeiXX326-May-08 12:43

26-May-08 12:43

Thanks for the reply

How im i supposed to use that?? where is that option?

Alexei Rodriguez

Mycroft Holmes26-May-08 13:54