CODEWITHSUNDEEP
sqlsql-server-2008
Jake
Jake

Reputation: 11430

Phantom column name in MS SQL Table, what is it?

I am trying to write an MS SQL statement to fetch a row.

SELECT otherfields, phantom_col FROM mytable WHERE id=5

The above SQL fails with error:

Msg 207 Level 16 Stage 1, Line XX
invalid column name 'phantom_col'.

I loaded SQL Management Studio 2008 R2 and connected to the said database and table and did a "select top 1000 rows" to get the auto generated SQL. It shows:

SELECT TOP 1000 [otherfields], [phantom_col] FROM [mydatabase].[dbo].[mytable]

I then deleted the part that reads [mydatabase] and immediately SQL Management Studio tells me [phantom_col] is invalid.

What special kind of column is that phantom_col? Strictly speaking, if I omit the lengthy [].[] notation, is my SQL syntax still correct?

EDIT: I looked finally looked closely enough and realised there is an error message. edited as above.

Upvotes: 0

Views: 421

Answers (1)

dArc
dArc

Reputation: 342

Your syntax is correct. The error, because you haven't connect to the myDatabse

you can use this too

USE mydatabase
GO
SELECT TOP 1000 phantom_col 
FROM myTable

When a SQL Server login connects to SQL Server, the login is automatically connected to its default database and acquires the security context of a database user. If no database user has been created for the SQL Server login, the login connects as guest. If the database user does not have CONNECT permission on the database, the USE statement will fail. If no default database has been assigned to the login, its default database will be set to master.

the quote is taken from here

Upvotes: 1

Related Questions