Prev: How to PIVOT this?
Next: Database Publishing
From: Dan Holmes on 2 Apr 2010 10:43
I know what caused it and i can fix it but i am curious why there is an error message for this failure. Does anyone know?

It is easy to reproduce

SELECT $v
FROM sys.columns

sql2005 - SSMS syntax highligher even changes the color to blue.
From: Jeroen Mostert on 2 Apr 2010 13:11
On 2010-04-02 16:43, Dan Holmes wrote:
> I know what caused it and i can fix it but i am curious why there is an
> error message for this failure. Does anyone know?
>
> It is easy to reproduce
>
> SELECT $v
> FROM sys.columns
>
A column name may not start with $ unless escaped, but $ is a legal start of
a pseudocolumn name -- those are symbolic aliases for actual columns. In
particular, $IDENTITY is an alias for to the identity column of the table
(if it has any) and $ROWGUID is an alias for the column with the ROWGUIDCOL
property (if there is one). These are mainly used by scripts (and internally
automated stuff like replication) to obviate the need for system table
grovelling and dynamic query building.

--
J.
From: Dan Guzman on 2 Apr 2010 13:23
> I know what caused it and i can fix it but i am curious why there is an
> error message for this failure. Does anyone know?
>
> It is easy to reproduce
>
> SELECT $v
> FROM sys.columns

Pseudo columns like $IDENTITY and $ROWGUID begin with a $. The error
message differentiates between a mistyped pseudo-column name and an invalid
column name that is not a pseudo-column. This behavior is apparently
different than what you expected ("Invalid column name '$v'").

Interestingly, you will get an invalid column name error if you specify a
valid pseudo column name but it's not valid in the current context:

SELECT $IDENTITY
FROM sys.columns;

> sql2005 - SSMS syntax highligher even changes the color to blue.

No blue highlighting for me with SQL 2005 SSMS. I presume this depends on
the client tool SP level.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/



From: Dan Holmes on 2 Apr 2010 14:14
On 4/2/2010 1:23 PM, Dan Guzman wrote:
>
>> sql2005 - SSMS syntax highligher even changes the color to blue.
>
> No blue highlighting for me with SQL 2005 SSMS. I presume this depends
> on the client tool SP level.
>
the engine is 2005 but SSMS is 2008. Perhaps that is the difference.
 | 
Pages: 1
Prev: How to PIVOT this?
Next: Database Publishing