Changes to order by in sql 2005

Hi, can anyone tell me why the following fails in sql 2005 with the error,
invalid column name 'title2'. This works in all other versions including 200
5
Express.
Select title as title1, title as title2 from title order by [title].title2
I can fix the query to run under 2005 by removing the field aliases, but I
can't find anything to explain why it failed. There are over 1000 queries in
my application, and although this is the only reported failure so far, if
there is a fundamental change regarding aliases or order by clauses, I need
to know so I can fix all affected queries.
ThanksPaul (Paul@.discussions.microsoft.com) writes:
> Hi, can anyone tell me why the following fails in sql 2005 with the
> error, invalid column name 'title2'. This works in all other versions
> including 2005 Express.
> Select title as title1, title as title2 from title order by [title].title2
If fails, because it's incorrect SQL. There is no title2 in title.
It was accepted in SQL 2000, yes. That was a bug.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Specifying a table alias in ORDER BY doesn't make sense, logically. From a l
ogical standpoint the
ORDER BY is performed as one of the last steps of the query (FROM, WHERE, GR
OUP BY, HAVING, SELECT
<col1, col2>, ORDER BY, TOP). Since ORDER BY is after the SELECT, we don't "
see" the base tables any
more, so it doesn't make sense to refer to them. In fact, at this stage, the
re exist only one table,
the table to be returned. But even SQL Server 2005 has some strangeness left
behind, even though it
is stricter than 2000:
--OK in 2000 and 2005
SELECT au_lname AS lname FROM authors AS a ORDER BY a.au_lname
--Fails in 2005, OK in 2000
SELECT au_lname AS lname FROM authors AS a ORDER BY a.lname
--Fails in 2000 and 2005
SELECT au_lname AS lname FROM authors AS a ORDER BY authors.au_lname
--Fails in 2005, OK in 2000
SELECT au_lname AS lname FROM authors AS a ORDER BY authors.lname
Anyhow, search for ORDER BY in below BOL URL (make sure you have the updates
BOL):
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/65eaafa1-9e06-4264-b547-
cbee8013c995.htm
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:FD1F5D8D-F11B-432C-A081-B75036B890A2@.microsoft.com...
> Hi, can anyone tell me why the following fails in sql 2005 with the error,
> invalid column name 'title2'. This works in all other versions including 2
005
> Express.
> Select title as title1, title as title2 from title order by [title].title2
> I can fix the query to run under 2005 by removing the field aliases, but I
> can't find anything to explain why it failed. There are over 1000 queries
in
> my application, and although this is the only reported failure so far, if
> there is a fundamental change regarding aliases or order by clauses, I nee
d
> to know so I can fix all affected queries.
> Thanks
>|||Paul wrote:
> Hi, can anyone tell me why the following fails in sql 2005 with the error,
> invalid column name 'title2'. This works in all other versions including 2
005
> Express.
> Select title as title1, title as title2 from title order by [title].title2
> I can fix the query to run under 2005 by removing the field aliases, but I
> can't find anything to explain why it failed. There are over 1000 queries
in
> my application, and although this is the only reported failure so far, if
> there is a fundamental change regarding aliases or order by clauses, I nee
d
> to know so I can fix all affected queries.
> Thanks
You need to read this section in Books Online:
http://msdn2.microsoft.com/en-us/library/ms143532(SQL.90).aspx
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks for the replies, at least I know what to check for in my other querie
s
now.
Thanks
"Paul" wrote:

> Hi, can anyone tell me why the following fails in sql 2005 with the error,
> invalid column name 'title2'. This works in all other versions including 2
005
> Express.
> Select title as title1, title as title2 from title order by [title].title2
> I can fix the query to run under 2005 by removing the field aliases, but I
> can't find anything to explain why it failed. There are over 1000 queries
in
> my application, and although this is the only reported failure so far, if
> there is a fundamental change regarding aliases or order by clauses, I nee
d
> to know so I can fix all affected queries.
> Thanks
>

Changed Administrator Password

Hi There!
I have changed the Administrator password on my Windows 2003 SBS server, and
now each time I reboot the server, the SQL Server Service fails to start.
I have to change the password back to the previous one, manually start the
service, and then change the password back to what I want it to be.
Does anyone know how to get round this?
TIA
Gareth Sharp
Hi Gareth
If the SQL Services are running on a specified account then you will need to
change the password for the service account too.
In case the SQL Services are running on the Local System account then there
should be no problem in the service starting up.
Thanks
Amer M J
MCP
"Gareth Sharp" wrote:

> Hi There!
> I have changed the Administrator password on my Windows 2003 SBS server, and
> now each time I reboot the server, the SQL Server Service fails to start.
> I have to change the password back to the previous one, manually start the
> service, and then change the password back to what I want it to be.
> Does anyone know how to get round this?
> TIA
> Gareth Sharp
>
>
|||Hello,
Go to the Administrative Tools/Services and find the MSSQLSERVER service.
Click for it's properties and check if the user that starts the service is
Administrator. Change the password for the new password there.
Eduardo Sicouret
American Call Center
"Gareth Sharp" <gs@.paxsoft.co.uk> escribi en el mensaje
news:doghmm$deo$1$8300dec7@.news.demon.co.uk...
> Hi There!
> I have changed the Administrator password on my Windows 2003 SBS server,
> and
> now each time I reboot the server, the SQL Server Service fails to start.
> I have to change the password back to the previous one, manually start the
> service, and then change the password back to what I want it to be.
> Does anyone know how to get round this?
> TIA
> Gareth Sharp
>
|||Hi Eduardo
Thanks for the help - I have made the change, and shall check it next time
the server is rebooted.
Does it matter if it is set to Local System account or Administrator? Could
I avoid the problem in the future by changing the option to Local System
account?
Thanks In Advance
Gareth
"Eduardo Sicouret" <esicouret@.americancallcenter.com> wrote in message
news:OavZ218BGHA.1676@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Hello,
> Go to the Administrative Tools/Services and find the MSSQLSERVER service.
> Click for it's properties and check if the user that starts the service is
> Administrator. Change the password for the new password there.
> Eduardo Sicouret
> American Call Center
> "Gareth Sharp" <gs@.paxsoft.co.uk> escribi en el mensaje
> news:doghmm$deo$1$8300dec7@.news.demon.co.uk...
start.[vbcol=seagreen]
the
>

Changed Administrator Password

Hi There!
I have changed the Administrator password on my Windows 2003 SBS server, and
now each time I reboot the server, the SQL Server Service fails to start.
I have to change the password back to the previous one, manually start the
service, and then change the password back to what I want it to be.
Does anyone know how to get round this?
TIA
Gareth SharpHi Gareth
If the SQL Services are running on a specified account then you will need to
change the password for the service account too.
In case the SQL Services are running on the Local System account then there
should be no problem in the service starting up.
Thanks
Amer M J
MCP
"Gareth Sharp" wrote:

> Hi There!
> I have changed the Administrator password on my Windows 2003 SBS server, a
nd
> now each time I reboot the server, the SQL Server Service fails to start.
> I have to change the password back to the previous one, manually start the
> service, and then change the password back to what I want it to be.
> Does anyone know how to get round this?
> TIA
> Gareth Sharp
>
>|||Hello,
Go to the Administrative Tools/Services and find the MSSQLSERVER service.
Click for it's properties and check if the user that starts the service is
Administrator. Change the password for the new password there.
Eduardo Sicouret
American Call Center
"Gareth Sharp" <gs@.paxsoft.co.uk> escribi en el mensaje
news:doghmm$deo$1$8300dec7@.news.demon.co.uk...
> Hi There!
> I have changed the Administrator password on my Windows 2003 SBS server,
> and
> now each time I reboot the server, the SQL Server Service fails to start.
> I have to change the password back to the previous one, manually start the
> service, and then change the password back to what I want it to be.
> Does anyone know how to get round this?
> TIA
> Gareth Sharp
>|||Hi Eduardo
Thanks for the help - I have made the change, and shall check it next time
the server is rebooted.
Does it matter if it is set to Local System account or Administrator? Could
I avoid the problem in the future by changing the option to Local System
account?
Thanks In Advance
Gareth
"Eduardo Sicouret" <esicouret@.americancallcenter.com> wrote in message
news:OavZ218BGHA.1676@.TK2MSFTNGP09.phx.gbl...
> Hello,
> Go to the Administrative Tools/Services and find the MSSQLSERVER service.
> Click for it's properties and check if the user that starts the service is
> Administrator. Change the password for the new password there.
> Eduardo Sicouret
> American Call Center
> "Gareth Sharp" <gs@.paxsoft.co.uk> escribi en el mensaje
> news:doghmm$deo$1$8300dec7@.news.demon.co.uk...
start.[vbcol=seagreen]
the[vbcol=seagreen]
>