What is a $pseudocolumn?

• Chad Crawford

  SSChampion

  Points: 11610

  More actions

  April 3, 2008 at 10:24 am

  #180531

  I captured a query similar to the following in a profiler replay trace. When replaying, it failed (as I'm sure it did when it was originally run) with this error:

  SELECT ItemValue

  FROM Item

  WHERE ItemID = $psu

  Msg 126, Level 15, State 1, Line 2

  Invalid pseudocolumn "$psu".

  I believe the client app was supposed to parse $psu out and replace it with an integer value and this is a client coding error. I don't believe the programmer intended the $psu to ever make it to the database. But since it did, I would have expected it to fail with an "Invalid column name" error, which it didn't, so I'm thinking the $ must be a special character of some kind. We'll fix the client code so that the error will go away, but can anyone explain how the $ is used and how it relates to pseudocolumns?

  Thanks,

  Chad

• antonio.collins

  SSCrazy Eights

  Points: 9600

  More actions

  April 3, 2008 at 10:32 am

  #797942

  SQLCMD supports $xxx variables that are somewhat like macros and can exist across batches. With it you can write statements like:

  UPDATE $table SET ...

  or

  SELECT ..., $col, ... FROM ...

  Perhaps your trace has the unexpanded version of the script SQL.

  http://msdn2.microsoft.com/en-us/library/ms188714.aspx

• Chad Crawford

  SSChampion

  Points: 11610

  More actions

  April 3, 2008 at 12:08 pm

  #798001

  Thanks Antonio - that does explain why SQL Server thought it was something that might have been valid.

  Chad

• antonio.collins

  SSCrazy Eights

  Points: 9600

  More actions

  April 3, 2008 at 12:39 pm

  #798021

  don't take my response as the gospel truth. it's just a guess/possibility. :unsure:

• Chad Crawford

  SSChampion

  Points: 11610

  More actions

  April 3, 2008 at 9:40 pm

  #798194

  It's all good. I'm almost certain the error was a parsing mistake in the client code, I just didn't know why MSSQL didn't throw an invalid column error instead of the one it did. I figured there was something new to learn and your link gave a valid reason why SQL Server would look at $psu as something other than a column name in this context. Thank you!

  Chad

• Andrew Novick

  SSC Enthusiast

  Points: 104

  More actions

  November 10, 2011 at 10:56 am

  #1405935

  Invalid Pseudocolumn refers to using a variable with the $ that is not one of the recognized pseudocolumns such as $rowguid, $identity or $partition.

  And yes, it looks like a coding error in the client application.