Column name in SQL Server fully qualified name

Question

I read in SQL Server documentation that I can specify column names in FROM clause of a simple SELECT query. But when I try to run this query:

select * from my_db.dbo.test_table.test;

I get the following error:

Msg 7202, Level 11, State 2, Line 1 Could not find server 'my_db' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

I know this is a T-SQL page, and I'm trying to run .

Why does this happen?

I'm using SQL Server version 2017 via Microsoft SQL Server Management Studio version 2017 as well.

Where did you read you can specify *column names* in the *from* clause? — HoneyBadger, Feb 08 '18 at 15:43
Sql query follows the default `Servername.Dbname.schema.table `, as you have given `.test ` in the end, first line (`my_db`) is considered as server name, if you want to select specific column write `select test from my_db.dbo.test_table` — Ven, Feb 08 '18 at 15:46
@BHouse yes, I know about specifying column names that way, but SQL Server documented something else. — Lotusmeristem, Feb 08 '18 at 15:52
It doesn't say that in the link. It only says how you can refer to an object, nowhere does it say you can refer to a column in the from clause. — HoneyBadger, Feb 08 '18 at 15:52

score 2 · Answer 1 · answered Feb 08 '18 at 15:46

2

You cannot specify columns in the FROM clause, but you can specify columns in the SELECT clause.

select test from my_db.dbo.test_table

answered Feb 08 '18 at 15:46

Vidmantas Blazevicius

4,652
2
11
30

score 2 · Accepted Answer · answered Feb 08 '18 at 17:12

try rewriting from this

select * from my_db.dbo.test_table.test;

to this

select test_table.test,* from my_db.dbo.test_table;

the way it was written with that many Periods it assume you are trying to fully qualify the table so in what you had tried to the server is treating it as follows

my db = linked server (a server other than the Server you are working on)
dbo = Schema (which is correct)
test_table = Table (Also correct)
test = just plain erroror

the fields you want to show should directly follow the Keyword Select so if you only wanted 2 fields you could write

select test,test2 from my_db.dbo.test_table;

or simpler if you only have the one server

select test,test2 from dbo.test_table;

or if you only have the defailt Schema dbo (Database Base Owner)

select test,test2 from test_table;

I hope thaelps

score 0 · Answer 3 · answered Feb 08 '18 at 15:45

Please run sp_addlinkedserver and sp_addlinkedsrvlogin SPs:

EXEC sp_addlinkedserver @server='MyLinkedServer'
EXEC sp_addlinkedsrvlogin 'MyLinkedServer', 'false', NULL, 'MyUserName', 'MyPassword'

After that, you can try to run: select * from my_db.dbo.test_table

Ref: https://blog.sqlauthority.com/2014/08/26/sql-server-fix-error-7202-could-not-find-server-in-sys-servers-verify-that-the-correct-server-name-was-specified/

Also, even though it is SQL Server 2014 related maybe helps, please see: Could not find server 'server name' in sys.servers. SQL Server 2014

Column name in SQL Server fully qualified name

3 Answers3